MySQL-5.6.27两种方式部署多实例以及mysqld_multi脚本BUG处理

部署MySQL多实例准备工作
1.1 创建各实例的data目录，以data’N’区分
[root@db1 mysql]# pwd
/data/mysql
[root@db1 mysql]# mkdir {data3306,data3307,data3308,data3309}
[root@db1 mysql]# chown -R mysql:mysql data330*

1.2 初始化各实例
[root@db1 mysql]# /usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql -datadir=/data/mysql/data3306
[root@db1 mysql]# /usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql -datadir=/data/mysql/data3307
[root@db1 mysql]# /usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql -datadir=/data/mysql/data3308
[root@db1 mysql]# /usr/local/mysql/scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql -datadir=/data/mysql/data3309

方式一：所有实例配置文件写入/etc/my.cnf
2.1
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
user=multi_admin
password=multi_password
[mysqld3306]
socket=/tmp/mysql3306.sock
port=3306
pid-file=/data/mysql/data3306/hostname3306.pid
datadir=/data/mysql/data3306
user=mysql
server-id=3306
[mysqld3307]
socket=/tmp/mysql3307.sock
port=3307
pid-file=/data/mysql/data3307/hostname3307.pid
datadir=/data/mysql/data3307
user=mysql
server-id=3307
[mysqld3308]
socket=/tmp/mysql3308.sock
port=3308
pid-file=/data/mysql/data3308/hostname3308.pid
datadir=/data/mysql/data3308
user=mysql
server-id=3308
[mysqld3309]
socket=/tmp/mysql3309.sock
port=3309
pid-file=/data/mysql/data3309/hostname3309.pid
datadir=/data/mysql/data3309
user=mysql
server-id=3309

2.2
通过bin下的工具mysqld_multi管理MySQL的多实例
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi start
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running

2.3
各个实例配置’multi_admin’@’localhost’用户，为mysqld_multi关闭实例
grant shutdown on . to ‘multi_admin’@’localhost’ identified by ‘multi_password’;
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi stop
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is not running
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
MySQL server from group: mysqld3309 is not running

方式二：各实例配置文件独立分开
3.1 复制原/etc/my.cnf，并更改各配置文件对应的datadir、socket、pid、server-id、port等
[root@db3 mysql]# cp /etc/my.cnf /etc/my3306.cnf
[root@db3 mysql]# cp /etc/my.cnf /etc/my3307.cnf
[root@db3 mysql]# cp /etc/my.cnf /etc/my3308.cnf
[root@db3 mysql]# cp /etc/my.cnf /etc/my3309.cnf

3.2 此时无法使用mysqld_multi，使用mysqld_safe和mysqladmin开或关mysqld
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3306.cnf –basedir=/usr/local/mysql –datadir=/datal/mysql/data3306 &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3307.cnf –basedir=/usr/local/mysql –datadir=/data/mysql/data3307 &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3308.cnf –basedir=/usr/local/mysql –datadir=/data/mysql/data3308 &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my3309.cnf –basedir=/usr/local/mysql –datadir=/data/mysql/data3309 &
[root@db3 data3406]# ps -ef |grep -w mysqld |grep -v grep |wc -l
4

3.3关闭各个MySQL实例
/usr/local/mysql/bin/mysqladmin –user=multi_admin –password=multi_password -S /tmp/mysql3306.sock shutdown
/usr/local/mysql/bin/mysqladmin –user=multi_admin –password=multi_password -S /tmp/mysql3307.sock shutdown
/usr/local/mysql/bin/mysqladmin –user=multi_admin –password=multi_password -S /tmp/mysql3308.sock shutdown
/usr/local/mysql/bin/mysqladmin –user=multi_admin –password=multi_password -S /tmp/mysql3309.sock shutdown
[root@db3 data3406]# ps -ef |grep -w mysqld |grep -v grep |wc -l
0

两种方式的特点
4.1所有实例同一配置文件的方式是使用mysqld_multi管理方便，但无法使用Xtrabackupex
4.2所有实例的配置文件独立分开，无法使用mysqld_multi，但可使用Xtrabackupex
4.3在生产中建议配置文件独立分开，至于为什么谁用谁知道

配置过程中的错误
5.1
[root@db1 data3309]# mysqld_multi report
Reporting MySQL servers
No groups to be reported (check your GNRs)
原因是在/etc/my.cnf，误把[mysqld3306]->[mysql3306]

5.2
mysqld_multi脚本report/start没问题，但stop无效
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi stop
[root@db1 data3306]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
解决方案：
参考：https://bugs.mysql.com/bug.php?id=77946

(over)