之前也做过多实便的安装,mysql的多实例安装与关闭不过当时的版本是5.6的,由于5.7和5.6mysql的安装方式还是有所不同的,今天就测试一下mysql5.7多实例的安装。
环境说明:
os: rhel6.3 64bit
mysql: 5.7.21
目前已存在一个实例:
root 2564 2538 0 23:31 pts/1 00:00:00 grep mysql
[root@qht131 ~]# ps -ef | grep mysql
root 1533 1 0 23:28 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/u01/mysql --pid-file=/u01/mysql/mysqld.pid
mysql 1880 1533 0 23:28 ? 00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/u01/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/u01/log/mysql/mysql_3306.err --open-files-limit=8192 --pid-file=/u01/mysql/mysqld.pid --socket=/u01/mysql/mysql.sock
root 2566 2538 0 23:31 pts/1 00:00:00 grep mysql
1.修改my.cnf
[root@qht131 mysql]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /u01/log/mysql_multi.log
[mysqld1]
character_set_server= utf8
init_connect= 'SET NAMES utf8'
basedir= /usr/local/mysql
datadir= /u01/mysql
socket = /u01/mysql/mysql.sock
user=mysql #必须用mysql帐户启动mysql,避免用root来启动
port=3306
performance_schema=off
innodb_buffer_pool_size=32M
bind_address=0.0.0.0
skip-name-resolve=0
[mysqld2]
character_set_server= utf8
init_connect= 'SET NAMES utf8'
basedir= /usr/local/mysql
datadir= /u01/mysql2
socket = /u01/mysql2/mysql.sock
user=mysql
port=3307
performance_schema=off
innodb_buffer_pool_size=32M
bind_address=0.0.0.0
skip-name-resolve=0
character_set_server= utf8
init_connect= 'SET NAMES utf8'
basedir= /usr/local/mysql
datadir= /u01/mysql3
socket = /u01/mysql3/mysql.sock
user=mysql
port=3308
performance_schema=off
innodb_buffer_pool_size=32M
bind_address=0.0.0.0
skip-name-resolve=0
2.建立需要的目录:
[root@qht131 mysql]# mkdir -p /u01/mysql2
[root@qht131 mysql]# mkdir -p /u01/log/mysql2
[root@qht131 mysql]# mkdir -p /u01/mysql3
[root@qht131 mysql]# mkdir -p /u01/log/mysql3
[root@qht131 mysql]# chown -R mysql.mysql /u01
3.初始化各实例 以及开启SSL连接:
[root@qht131 mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/u01/mysql2
2018-05-04T22:32:19.567383Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-04T22:32:20.937886Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-05-04T22:32:21.154932Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-04T22:32:21.244128Z 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: 027cc829-4feb-11e8-9103-000c29aa0615.
2018-05-04T22:32:21.246035Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-05-04T22:32:21.246980Z 1 [Note] A temporary password is generated for root@localhost: vd539qdjd#,B
[root@qht131 mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/u01/mysql3
2018-05-04T22:33:34.514887Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-04T22:33:35.948344Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-05-04T22:33:36.150828Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-05-04T22:33:36.217487Z 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: 2f2ccf35-4feb-11e8-9402-000c29aa0615.
2018-05-04T22:33:36.221292Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-05-04T22:33:36.227321Z 1 [Note] A temporary password is generated for root@localhost: RcwAsh&A%9mr
[root@qht131 mysql]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/u01/mysql2
Generating a 2048 bit RSA private key
.............................................................+++
.....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.......................+++
................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
....+++
..........................+++
writing new private key to 'client-key.pem'
-----
[root@qht131 mysql]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/u01/mysql3
Generating a 2048 bit RSA private key
..............................................+++
...........................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...................................................................................................................+++
..+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
..+++
..+++
writing new private key to 'client-key.pem'
-----
4.配置自动启动
[root@qht131 mysql]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi
[root@qht131 mysql]# chmod +x /etc/init.d/mysqld_multi
[root@qht131 mysql]# chkconfig --add mysqld_multi
5.启动所有的实例
[root@qht131 mysql]# /etc/init.d/mysqld_multi start
[root@qht131 mysql]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[root@qht131 mysql]# netstat -lntp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6800/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 6803/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 6806/mysqld
已成功启动!
6.记得先要修改新添加的两个实例的root密码:
[root@qht131 mysql2]# mysql -S /u01/mysql2/mysql.sock -uroot -pvd539qdjd#,B
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@qht131 mysql2]# mysql -S /u01/mysql2/mysql.sock -uroot -pRcwAsh&A%9mr
mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
7.接着试下能否正常关闭
[root@qht131 mysql]# /etc/init.d/mysqld_multi stop
[root@qht131 mysql]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
没有关闭成功,查看一下多实例的Log:
[root@qht131 log]# tail -f mysql_multi.log
2018-05-04T21:52:32.951509Z 10 [Note] Access denied for user 'root'@'localhost' (using password: NO)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
2018-05-04T21:52:32.958002Z 10 [Note] Access denied for user 'root'@'localhost' (using password: NO)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
2018-05-04T21:52:32.965020Z 10 [Note] Access denied for user 'root'@'localhost' (using password: NO)
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
发现mysqladmin连接不到数据库,在/etc/my.cnf中增加user和password试一下:
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /u01/log/mysql_multi.log
user=root #增加的user和password
password=123456
增加了用户名和密码后还是不行,原因是mysqld_multi是通过调用my_print_defaults来读取实例的各项参数的,读取的结果如下:
[root@qht131 mysql2]# my_print_defaults mysqld_multi mysql1
--mysqld=/usr/local/mysql/bin/mysqld
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--log=/u01/log//mysql_multi.log
[root@qht131 mysql2]# vi /etc/my.cnf
[root@qht131 mysql2]# /etc/init.d/mysqld_multi stop
[root@qht131 mysql2]# my_print_defaults mysqld_multi mysql1
--mysqld=/usr/local/mysql/bin/mysqld
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--log=/u01/log/mysql_multi.log
--user=root
--password=*****
由于读取的密码是加密的,自然就登入不了。解决的方法就是修改一下mysqld_multi脚本:
接着再关闭数据库就成功了:
[root@qht131 bin]# /etc/init.d/mysqld_multi stop
[root@qht131 bin]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
参考:https://blog.csdn.net/zhengwei125/article/details/52413835
8.开启和关闭单个实例:
如果启动第一个也就是Port为3306的实例:
[root@qht131 u01]# mysqld_multi start 1
[root@qht131 u01]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
[root@qht131 u01]# ps -ef | grep mysqld
mysql 12945 1 4 17:49 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --character_set_server=utf8 --init_connect=SET NAMES utf8 --basedir=/usr/local/mysql --datadir=/u01/mysql --socket=/u01/mysql/mysql.sock --user=mysql --port=3306 --performance_schema=off --innodb_buffer_pool_size=32M --bind_address=0.0.0.0 --skip-name-resolve=0 --log-error=/u01/log/mysql/mysql_3306.err
root 12975 2781 0 17:49 pts/2 00:00:00 grep mysqld
我的情况GNR分别为1,2,3。所以单个启动时指定1,2,3就可以了。官方文档的说明如下:
Each GNR
value represents an option group number or range of group numbers. The value should be the number at the end of the group name in the option file. For example, the GNR
for a group named [mysqld17]
is 17
.
关闭也是同理:
[root@qht131 u01]# mysqld_multi stop 1
[root@qht131 u01]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
也可以用mysqladmin进行关闭
[root@qht131 u01]# mysqladmin -uroot -p -S /u01/mysql/mysql.sock shutdown
9.连接单实例,通过指定实例的sock文件来连接实例:
[root@qht131 u01]# mysql -uroot -p -S /u01/mysql/mysql.sock
[root@qht131 u01]# mysql -uroot -p -S /u01/mysql2/mysql.sock
[root@qht131 u01]# mysql -uroot -p -S /u01/mysql3/mysql.sock