mysqld_multi配置

当我们一台机器启动了多个mysql实例的时候，管理起来简直就是灾难。官方提供的mysqld_multi帮我们完美解决这个问题。
mysqld_multi用于管理多个mysqld进程，这些进程侦听不同Unix套接字文件和TCP / IP端口上的连接。它可以启动或停止服务器，或报告其当前状态。

配置文件

mysqld_multi其实是将多个实例的配置文件整合到了一起。每个实例都会有对应的选项组编号,GNR(option group number)。通过指定编号的方式来操作不同的实例
那我们先来看下配置文件长什么样子吧

mysqld_multi --example

输出如下

[mysqld_multi]
mysqld     = /usr/local/Cellar/mysql.6/5.6.41/bin/mysqld_safe
mysqladmin = /usr/local/Cellar/mysql.6/5.6.41/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /usr/local/var/mysql2/hostname.pid2
datadir    = /usr/local/var/mysql2
language   = /usr/local/Cellar/mysql.6/5.6.41/share/mysql/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/var/mysql3/hostname.pid3
datadir    = /usr/local/var/mysql3
language   = /usr/local/Cellar/mysql.6/5.6.41/share/mysql/mysql/swedish
user       = unix_user2

这是样例配置文件，对于[mysqld2]选项组来说，GNR是2,后续对实例进行管理的时候都是用该数字作为唯一标识符。个人建议使用端口号作为组号，方便管理。
[mysqld_multi]
mysqld: 要使用的mysqld二进制文件。请注意，您还可以指定mysqld_safe作为此选项的值,本文不做讨论。
mysqldadmin: mysqldadmin的文件地址,通过该程序控制实例的启停
user:调用mysqladmin时要使用的MySQL帐户的用户名。
password：调用mysqladmin时使用的MySQL帐户的密码

[mysqld2]
socket: socket文件地址，不同实例要区分开
port: 端口号，不同实例要区分开
datadir：数据目录地址，用来存放各个实例的数据
user: 启动用户

按照上述要求我的配置文件如下

[mysqld_multi]i
mysqld     = /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld
mysqladmin = /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqladmin
user       = multi_admin
password   = multipass

[mysqld1]
socket     = /tmp/mysql.sock
port       = 3306
#pid-file   = /usr/local/var/mysql/XYBJM01617.local.pid
datadir    = /usr/local/var/mysql
#lc-messages-dir   = /usr/local/Cellar/mysql@5.6/5.6.41/share/mysql/english
#language = /usr/local/Cellar/mysql@5.6/5.6.41/share/mysql/english
user       = csw
#explicit_defaults_for_timestamp = true
#secure_file_priv =

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
#pid-file   = /usr/local/var/mysql/XYBJM01617.local.pid2
datadir    = /usr/local/var/mysql2
#lc-messages-dir   = /usr/local/Cellar/mysql@5.6/5.6.41/share/mysql/english
user       = csw
#explicit_defaults_for_timestamp = true
#secure_file_priv =

[mysqld3]
socket     = /tmp/mysql.sock3
port       = 3308
#pid-file   = /usr/local/var/mysql/XYBJM01617.local.pid3
datadir    = /usr/local/var/mysql3
#lc-messages-dir   = /usr/local/Cellar/mysql@5.6/5.6.41/share/mysql/english
user       = csw
#explicit_defaults_for_timestamp = true
#secure_file_priv =

管理服务

我本地的组1和组2已经配好用户权限，无法复现问题。我们启动组3试一下，这是一个全新的组。

命令格式

mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]

只启动17

mysqld_multi start 17

停止实例组8，10-13

mysqld_multi stop 8,10-13

当不指定 GNR的时候，将操作全部的实例组。这里需要格外注意，除非你真的想关掉生产环境的全部mysql实例。

启动服务

按照上面的命令，我们来只启动组3试一下

mysqld_multi start 3

还是很顺利的

...
Version: '5.6.41'  socket: '/tmp/mysql.sock3'  port: 3308  Homebrew

看到上述信息，说明实例已经正常启动。

but，事情总不会遂人意，相信很多人会看到报错。错误原因是我们的前置工作没做到位。前面说了msyqld_multi是方便管理多个mysql实例，那么前提就是多个mysql实例是可以正常启动的。所以请参考 同时启动多个mysql实例>>

---

查看状态

通过report我们可以查看当前各个实例的启停状态

mysqld_multi report

会看到我们的组3已经启动啦

WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: 四  5 21 18:44:14 2020
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 running
MySQL server from group: mysqld4 is not running

关闭实例

那我想关掉这个实例呢

mysqld_multi stop 3

终于还是报错了

WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: 四  5 21 18:47:02 2020

Stopping MySQL servers
XYBJM01617:var csw$ Warning: Using a password on the command line interface can be insecure.
/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'multi_admin'@'localhost' (using password: YES)'

大概意思就是用户multi_admin无权限。
前面有提到过，[mysqld_multi]配置项里的user是用来关闭实例的用户，那前提自然是，所有的实例必须有mysqld_multi这个用户，并且该用户必须有shutdown的权限。
知道问题所在，那我们就来配一下用户吧。
我们先关掉实例

XYBJM01617:~ csw$ ps aux | grep mysql
csw              74594   0.0  0.0  4301512   1116 s010  S+   11:53上午   0:00.03 mysql -u root -S /tmp/mysql.sock2 -p
csw              79467   0.0  0.0  4268020    788 s014  R+    6:51下午   0:00.01 grep mysql
csw              79040   0.0  0.6  4917216 100976   ??  S     6:18下午   0:00.78 /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --socket=/tmp/mysql.sock3 --port=3308 --datadir=/usr/local/var/mysql3 --user=csw

可以看到进程id是79040

kill -9 79040

杀死它！！！
然后启动它，不过要换一种方式

/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --skip-grant-tables --socket=/tmp/mysql.sock3 --port=3308 --datadir=/usr/local/var/mysql3

关键在–skip-grant-tables，这个参数启动mysql，我们可以跳过权限校验直接登录实例。

#登录数据库
mysql -S /tmp/mysql.sock3

查看当前用户表的信息

mysql> select host,user,password from user;
+------------------+------+----------+
| host             | user | password |
+------------------+------+----------+
| localhost        | root |          |
| xybjm01617.local | root |          |
| 127.0.0.1        | root |          |
| ::1              | root |          |
| localhost        |      |          |
| xybjm01617.local |      |          |
+------------------+------+----------+
6 rows in set (0.00 sec)

更新密码字段，不允许出现密码为空的数据

mysql> update user set password='csw123';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

删除掉user字段为空的数据

mysql> delete from user where user='';
Query OK, 2 rows affected (0.01 sec)

我们试着新增用户，会发现没有权限

mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

重新启动，这次不用跳过权限验证

/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld  --socket=/tmp/mysql.sock3 --port=3308 --datadir=/usr/local/var/mysql3

通过root用户登录

XYBJM01617:~ csw$ mysql -h 127.0.0.1 -u root -P 3308 -p
Enter password:

新增用户

mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';
Query OK, 0 rows affected (0.00 sec)

再试一下，就可以正常关闭实例了

小插曲

如果关闭实例的时候还是报错

Warning: Using a password on the command line interface can be insecure.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
mysqld_multi log file version 2.16; run: Wed Dec 14 15:31:32 2016

更改下脚本即可

#先找到脚本在哪里
XYBJM01617:~ csw$ which mysqld_multi
/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld_multi

#再修改下脚本
XYBJM01617:~ csw$ vim /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld_multi

my $com= join ' ', 'my_print_defaults ', @defaults_options, $group; 
替换为： 
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

保存在重新试下就OK了