CentOS 7.5 uses MySQL multi mode to install multiple instances of MySQL 5.7.28

Because the MySQL 5.7.28 multiple instances installed using the source code will have problems when importing the database, the method of using mysql_multi to manage multiple instances will be studied again. After the verification of the test environment, the problem of MySQL 5.7.28 multiple instances has not been reported in all aspects of use, especially in backup and restore, and many pits have been stepped on. Here I will share my deployment process. If where is it If there is any problem, please correct and guide me a lot. Thank you!!
For mysql installation and deployment basic environment and configuration, please refer to the article: CentOS 7.5 source code installation mysql 5.7.28 multiple instances
https://blog.51cto.com/8355320/2463218
This article starts from chapter 2.7 to install multiple instances of mysql. The specific deployment process is as follows:
2.7 installing multiple instances of mysql
2.7.1. Create the software installation directory (please modify the deployment path according to the actual situation)

[root@~]#  mkdir -pv /data/mysql/{3306,3307}
[root@~]#  mkdir -v /data/mysql/3306/{logs,data,binlog}
[root@~]#  mkdir -v /data/mysql/3307/{logs,data,binlog}

2.7.2. Download MySQL installation package

[root@~]#  cd /opt
[root@~]#  wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]#  tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@~]#  mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
[root@~]#  chown -R mysql:mysql /usr/local/mysql
[root@~]#  chown -R mysql:mysql /data

2.7.3. MySQL parameter configuration
Configure my.cnf parameter file
(1) Please configure the values of server id = 3306 and server id = 3307 according to the actual configuration. Note that the configured ID value cannot conflict with the values configured by other instances in the LAN;
(2) max_connections=1000 configure the maximum number of connections of MySQL database according to the actual needs, and optimize or add other parameters according to the actual needs;
(3) The entire contents of the configuration file are as follows

[root@~]#  vim /etc/my.cnf
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /data/mysql/mysqld_multi.log 
#user=root
#pass=

[mysql]
prompt="\u@jsshapp \R:\m:\s [\d]> "
no-auto-rehash

[mysqld3306]
user                                = mysql
port                                = 3306
symbolic-links                      = 0
#basedir                             = /usr/
datadir                             = /data/mysql/3306/data
socket                              = /data/mysql/3306/mysql3306.sock
pid-file                            = /data/mysql/3306/mysqld3306.pid
server_id                           = 3306
character_set_server                = utf8
max_connections                     = 1000
skip_name_resolve                   = 1
open_files_limit                    = 65536
thread_cache_size                   = 64
table_open_cache                    = 4096
table_definition_cache              = 1024
table_open_cache_instances          = 64
max_prepared_stmt_count             = 1048576
explicit_defaults_for_timestamp     = true
log_timestamps                      = system

binlog_format                       = row
log_bin                           = /data/mysql/3306/binlog/mysql-bin
binlog_rows_query_log_events        = on
expire_logs_days                    = 7
binlog_cache_size                   = 4M
max_binlog_cache_size               = 2G
max_binlog_size                     = 1G
sync_binlog                         = 1
log_bin_trust_function_creators     = 1

slow_query_log                      = on
slow_query_log_file                 = /data/mysql/3306/data/slow.log
log-error                           = /data/mysql/3306/logs/error.log
log_queries_not_using_indexes      = on
long_query_time                     = 1.000000

gtid_mode                           = on
enforce_gtid_consistency            = on

default_storage_engine              = innodb
default_tmp_storage_engine          = innodb
innodb_data_file_path               = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path          = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename         = ib_buffer_pool
innodb_log_files_in_group           = 3
innodb_log_file_size                = 512M
innodb_online_alter_log_max_size    = 1024M
innodb_open_files                   = 4096
innodb_page_size                    = 16k
innodb_thread_concurrency           = 0
innodb_read_io_threads              = 4
innodb_write_io_threads             = 4
innodb_purge_threads                = 4
innodb_page_cleaners                = 4
innodb_print_all_deadlocks          = on
innodb_lock_wait_timeout            = 20
innodb_spin_wait_delay              = 128
innodb_autoinc_lock_mode            = 2
innodb_io_capacity                  = 200
innodb_io_capacity_max              = 2000
#innodb_flush_neighbors             = 
innodb_log_buffer_size              = 8M
innodb_flush_log_at_timeout         = 1
innodb_flush_log_at_trx_commit      = 2
innodb_buffer_pool_size             = 1024M
innodb_buffer_pool_instances        = 4
autocommit                          = 1
innodb_buffer_pool_dump_pct         = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

[mysqld3307]
user                                = mysql
port                                = 3307
symbolic-links                      = 0
lower_case_table_names              = 1
#basedir                             = /usr/
datadir                             = /data/mysql/3307/data
socket                              = /data/mysql/3307/mysql3307.sock
pid-file                            = /data/mysql/3307/mysqld3307.pid
server_id                           = 3307
character_set_server                = utf8
max_connections                     = 1000
skip_name_resolve                   = 1
open_files_limit                    = 65536
thread_cache_size                   = 64
table_open_cache                    = 4096
table_definition_cache              = 1024
table_open_cache_instances          = 64
max_prepared_stmt_count             = 1048576
explicit_defaults_for_timestamp     = true
log_timestamps                      = system

binlog_format                       = row
log_bin                            = /data/mysql/3307/binlog/mysql-bin
binlog_rows_query_log_events        = on
expire_logs_days                    = 7
binlog_cache_size                   = 4M
max_binlog_cache_size               = 2G
max_binlog_size                     = 1G
sync_binlog                         = 1

slow_query_log                      = on
slow_query_log_file                 = /data/mysql/3307/data/slow.log
log-error                           = /data/mysql/3307/logs/error.log
log_queries_not_using_indexes      = on
long_query_time                     = 1.000000

gtid_mode                           = on
enforce_gtid_consistency            = on

default_storage_engine              = innodb
default_tmp_storage_engine          = innodb
innodb_data_file_path               = ibdata1:12M:autoextend:max:2000M
innodb_temp_data_file_path          = ibtmp1:12M:autoextend:max:2000M
innodb_buffer_pool_filename         = ib_buffer_pool
innodb_log_files_in_group           = 3
innodb_log_file_size                = 512M
innodb_online_alter_log_max_size    = 1024M
innodb_open_files                   = 4096
innodb_page_size                    = 16k
innodb_thread_concurrency           = 0
innodb_read_io_threads              = 4
innodb_write_io_threads             = 4
innodb_purge_threads                = 4
innodb_page_cleaners                = 4
innodb_print_all_deadlocks          = on
innodb_lock_wait_timeout            = 20
innodb_spin_wait_delay              = 128
innodb_autoinc_lock_mode            = 2
innodb_io_capacity                  = 200
innodb_io_capacity_max              = 2000
#innodb_flush_neighbors             = 
innodb_log_buffer_size              = 8M
innodb_flush_log_at_timeout         = 1
innodb_flush_log_at_trx_commit      = 2
innodb_buffer_pool_size             = 1024M
innodb_buffer_pool_instances        = 4
autocommit                          = 1
innodb_buffer_pool_dump_pct         = 25
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

[mysqldump]
quick
max_allowed_packet = 32M

2.7.4. Configure MySQL environment variables

[root@~]#  echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@~]#  tail -1 /etc/profile
[root@~]#  source /etc/profile

2.7.5. Testone instance initialization
Operation instance initialization

[root@~]#  mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>&1
[root@~]#  tail -100f /tmp/3306.log

---Use the tail command to view the initialization log. The following appears, which means that the initialization is complete (where "5 + T + xyw + < T" is the temporary password of root user)
A temporary password is generated for root@localhost: #5+t+xYW+<t?

Generate ssl file

[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data

Start testone instance

[root@~]#  mysqld_multi start 3306
[root@~]#  tail -100f /data/mysql/3306/logs/error.log 

----Use the tail command to view the startup log. If it appears, it means the startup is successful

Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)

Change the root password

[root@~]#  less /tmp/3306.log | grep 'A temporary password'
[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by 'Unified password';
mysql> flush privileges;
mysql> exit;

 verify the unified password of testone root user (the interface normally outputs information ﹣ the schema content indicates normal)

[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:

Modify my.cnf configuration file, add the modified unified password to the configuration file

[root@~]#  sed -i "s@^#user=root@user=root@g" /etc/my.cnf
[root@~]#  sed -i "s@^#pass=@pass = unified password @ g "/ etc / my.cnf
[root@~]#  Cat / etc / my.cnf| grep pass = --- use cat command to check whether the output of pass field of configuration file is consistent

Stop testone instance

[root@~]#  mysqld_multi stop 3306
[root@~]#  netstat -tnlp|grep 3306 -- if the input result is blank, it means that the service is stopped normally

2.7.6. Testwo instance initialization
Operation instance initialization

[root@~]#  mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>&1
[root@~]#  tail -100f /tmp/3307.log

---Use the tail command to view the initialization log. The following appears, that is, initialization is complete (where - PN > t; ye) ay6 = I is the temporary password of root user)
A temporary password is generated for root@localhost: -pn>t;Ye)Ay6=I
Generate ssl file

[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data

Start testwo instance

[root@~]# mysqld_multi start 3307

----Use the tail command to view the startup log. If it appears, it means the startup is successful

[root@~]# tail -100f /data/mysql/3307/logs/error.log
Version: '5.7.28-log'  socket: '/data/mysql/3307/mysql3307.sock'  port: 3307  MySQL Community Server (GPL)

Change the root password

[root@~]#  less /tmp/3307.log|grep 'A temporary password'
[root@~]#  mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> alter user 'root'@'localhost' identified by 'Unified password';
mysql> flush privileges;
mysql> exit;

 verify the unified password of testwo root user (the normal output of information ﹣ schema content on the interface indicates normal)

[root@~]#  mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema
Enter password:

Stop testwo instance

[root@~]#  mysqld_multi stop 3307
[root@~]#  netstat -tnlp|grep 3307 -- if the input result is blank, it means that the service is stopped normally

2.7.7. Mysqld multi instance management command
Start all instances

[root@~]# mysqld_multi start

Stop a single instance

[root@~]#  mysqld_multi stop 3306
[root@~]#  mysqld_multi stop 3307

Start a single instance

[root@~]#  mysqld_multi start 3306
[root@~]#  mysqld_multi start 3307

View the status of all instances (is running)

[root@~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

View the status of a single instance

[root@~]#  mysqld_multi report 3306
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
[root@~]#  mysqld_multi report 3307
Reporting MySQL servers
MySQL server from group: mysqld3307 is running

Stop all instances

[root@~]# mysqld_multi stop

2.7.8. Data import
(1) Upload the database script (test.sql, testwo. SQL) file to be imported (name to be operated according to the actual situation) to the / data / path
(2) Use the command to confirm that the two instances are already running

[root@~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

(3) Enter testone's mysql database to create testone database instance, user name and password and import data

[root@~]#  cd /data/
[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock
Enter password: 
mysql>create database testone default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by 'Password';
mysql> flush privileges;
mysql> exit
[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql

(3) Enter the mysql database of testwo, create the testwo service database instance, user name and password, and import the data

[root@~]#  mysql -uroot -p -S /data/mysql/3307/mysql3307.sock
Enter password:
mysql> create database testtwo default character set utf8 collate utf8_bin;
mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by 'Password';
mysql> flush privileges;
mysql> exit
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql

2.7.9. Firewall configuration
Add open ports according to actual requirements

[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

 reload

[root@~]# firewall-cmd --reload

Tags: MySQL Database socket SQL

Posted on Tue, 14 Jan 2020 21:19:22 -0500 by Anant