编程知识 cdmana.com

MySQL standardized installation manual under Linux

Server requirements :

 Host memory , Storage needs to meet the database requirements , The file system requirements are as follows :
/tran/mysql/	mysql Software installation directory ,10G
/tran/my$port/	 Data storage directory (port Is the port number ), Evaluate capacity based on the size of the data , Its directory structure is :
 The root directory places the configuration file my.cnf,socket file .
data Catalog as my.cnf Medium datadir Catalog ,pid-file Put it here, too .
log Directory placement binlog,error log,slow log,relay log(slave).

/tran/app/	DBA working directory ,20G
/tran/myback	 Backup directory ( long-range NAS disc ), The capacity is larger than that of the database 3.5 times ( Keep a week's backup ,2 be completely ready +6 Additional reserve , Delete after successful backup 7 Days ago , So there could be at most 3 be completely ready +6 Additional reserve )

10.250.112.99 The related file systems on are as follows :
/dev/mapper/centos-tran_mysql    10G   33M   10G   1% /tran/mysql
/dev/mapper/centos-tran_app      20G   33M   20G   1% /tran/app
/dev/mapper/centos-tran_my3306  600G   33M  600G   1% /tran/my3306
/dev/mapper/centos-tran_myback  2.4T   33M  2.4T   1% /tran/myback

install Mysql

 Create new users and groups and modify directory permissions :
groupadd -g 500 mysql
useradd -g 500 -u 500 -d /home/mysql -m mysql
 Modify the relevant file system owner by mysql
chown mysql:mysql /tran/mysql /tran/app /tran/my3306 /tran/myback
 Let go of mysql Limit the number of files that users can open ( otherwise max_connections,table_open_cache Parameters will not take effect ):
vi /etc/security/limits.conf
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft nofile 65535
mysql hard nofile 65535
 Download a software ( It's all in the back mysql The user action ):
su - mysql
cd /tran/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-el7-x86_64.tar.gz
tar -zxvf mysql-5.7.30-el7-x86_64.tar.gz
mv mysql-5.7.30-el7-x86_64 mysql-5.7.30
 Create new data directory and log directory , Temporary directory :
mkdir -p /tran/my3306/data
mkdir -p /tran/my3306/log
mkdir -p /tran/my3306/tmp
 Set up mysql user path Path and simplified login method :
vi .bash_profile
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/tran/mysql/mysql-5.7.30/bin/
export PS1='[\u@\h `pwd`]$ '
alias m='mysql --login-path=root'
alias md='cd /tran/my3306/data'
alias ml='cd /tran/my3306/log/'

Initialize database :

Take the attachment below as the template , Generate configuration files /tran/my3306/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server=utf8mb4
socket=/tran/my3306/mysql3306.sock

# Set the copy process from the database to not start with the start of the database 
#skip-slave-start
# Copy parameters from library 
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# The TCP/IP Port the MySQL Server will listen on
port=3306

datadir=/tran/my3306/data
log-error=/tran/my3306/log/error.log
pid-file=/tran/my3306/data/mysql.pid
tmpdir=/tran/my3306/tmp
# Import and export file storage directory 
secure_file_priv='/tran/app/'
server_id=66

log-bin=/tran/my3306/log/binlog
binlog_cache_size = 10M
binlog_format=mixed
max_binlog_size=300M
expire_logs_days=7
enforce-gtid-consistency=on
gtid_mode=on
relay_log=/tran/my3306/log/mysql-relay-bin
log_slave_updates=1
# If it is a standby database, open read-only 
#read_only=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

transaction_isolation=READ-COMMITTED
# Save the table name in lowercase , Case insensitive when used 
lower_case_table_names = 1
# Not super Users can create functions 
log_bin_trust_function_creators=on

slow_query_log=1
long_query_time=1
slow_query_log_file=/tran/my3306/log/slow.log
slow_launch_time=5

innodb_buffer_pool_size=48G
# When the database is large, it needs to be adjusted , Otherwise, the backup will fail 
#innodb_log_files_in_group=5
innodb_log_file_size=48M
max_connections=3000
table_open_cache=3000
 Initialize database (root User execution , The last line is the database user root The temporary password for , It needs to be modified after login ):
/tran/mysql/mysql-5.7.30/bin/mysqld --initialize --user=mysql --basedir=/tran/mysql/ --datadir=/tran/my3306/data --explicit_defaults_for_timestamp
[root@Fit2cloud data]# /tran/mysql/mysql-5.7.30/bin/mysqld --initialize --user=mysql --basedir=/tran/mysql/ --datadir=/tran/my3306/data --explicit_defaults_for_timestamp
2020-05-15T07:04:35.478562Z 0 [ERROR] Can't find error-message file '/tran/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2020-05-15T07:04:35.853976Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-15T07:04:35.919044Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-15T07:04:35.981098Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 55ea3712-967a-11ea-aa75-0050569cae61.
2020-05-15T07:04:35.983004Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-15T07:04:35.983984Z 1 [Note] A temporary password is generated for root@localhost: edA_Cb740sps
 This command also generates configuration files /etc/my.cnf, We don't need to , It can be deleted :rm /etc/my.cnf

Start database :

mysql user :
 start-up mysql
mysqld_safe --defaults-file=/tran/my3306/my.cnf &
 land mysql
mysql -uroot -pedA_Cb740sps --socket=/tran/my3306/mysql3306.sock
 modify root password :
SET PASSWORD = PASSWORD('trAn2h7P');
flush privileges;
exit

verification

 Log in to the database with your new password :
mysql -uroot -ptrAn2h7P --socket=/tran/my3306/mysql3306.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

 see datadir structure :
[mysql@Fit2cloud my3306]$ pwd
/tran/my3306
[mysql@Fit2cloud my3306]$ tree
├── data
│   ├── auto.cnf
│   ├── ca-key.pem
│   ├── ca.pem
│   ├── client-cert.pem
│   ├── client-key.pem
│   ├── ib_buffer_pool
│   ├── ibdata1
│   ├── ib_logfile0
│   ├── ib_logfile1
│   ├── ibtmp1
│   ├── mysql
│   │   ├── columns_priv.frm
......
│   │   └── user.MYI
│   ├── mysql.pid
│   ├── performance_schema
│   │   ├── accounts.frm
......
│   │   └── variables_by_thread.frm
│   ├── private_key.pem
│   ├── public_key.pem
│   ├── server-cert.pem
│   ├── server-key.pem
│   └── sys
│       ├── db.opt
......
│       └── x@0024waits_global_by_latency.frm
├── log
│   ├── binlog.000001
│   ├── binlog.index
│   ├── error.log
│   └── slow.log
├── my.cnf
├── mysql3306.sock
└── mysql3306.sock.lock
└── tmp

Set up local password free login

 For the convenience of maintenance in the future , Set up local password free login , In this way, there is no need to write the user name and password in the maintenance script , The method is as follows :
 Carry out orders :
mysql_config_editor set -G root -S /tran/my3306/mysql3306.sock -u root -p
 verification :
[mysql@edw-tools-master ~]$ mysql_config_editor print --all
[root]
user = root
password = *****
socket = /tran/my3306/mysql3306.sock
 Then you can log in like this ( Because of the setting above alias, It can be used directly “m” Sign in ):
mysql --login-path=root
 Equate to :
mysql -uroot -ptrAn2h7P --socket=/tran/my3306/mysql3306.sock

To configure mysql Boot up

root User execution :
chmod +x /etc/rc.d/rc.local

vi /etc/rc.d/rc.local
su - mysql -c "mysqld_safe --defaults-file=/tran/my3306/my.cnf &"

 

版权声明
本文为[lyle_ Luo]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224105400244I.html

Scroll to Top