mysql多实例管理_使用mysqld_multi管理多个MySQL实例

配置过程如下，

1. 创建mysqld_multi的config-file，配置mysqld_multi的选项

[mysqld_multi]

mysqld = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user = multi_admin

password = multipass

log = /var/log/mysqld_multi.log

[mysqld3307]

port = 3307

pid-file = /var/lib/mysql3307/mysql3307.pid

socket = /var/lib/mysql3307/mysql3307.sock

datadir=/var/lib/mysql3307

user=mysql

set-variable=max_connections=27000

[mysqld3308]

port = 3308

pid-file = /var/lib/mysql3308/mysql3308.pid

socket = /var/lib/mysql3308/mysql3308.sock

datadir=/var/lib/mysql3308

user=mysql

set-variable=max_connections=28000

[mysqld3309]

port = 3309

pid-file = /var/lib/mysql3309/mysql3309.pid

socket = /var/lib/mysql3309/mysql3309.sock

datadir=/var/lib/mysql3309

user=mysql

set-variable=max_connections=29000

2.创建相应的数据目录并生成db结构

mkdir /var/lib/mysql3309

chown -R　mysql.mysql /var/lib/mysql3309

mysql_install_db --datadir=/var/lib/mysql3309 --user=mysql

3. 之前在my.cnf设置的user和password是用来关闭数据库实例的，根据配置中的用户名和密码授权该用户shutdown权限

mysql -u root -S /var/lib/mysql3309/mysql3309.sock -p

mysql> grant shutdown on *.* tomulti_admin@'localhost'identified by 'multipass';

4. 对单台数据库的启动和关闭

语法：mysqld_multi [options] {start|stop|report} [GNR[,GNR] …]

如对mysqld1的启动和关闭，可以通过mysqld_multi来操作

启动： mysqd_multi --defaults-file=/etc/mysql/mysqld_multi.cnf start 1

关闭： mysqd_multi --defaults-file=/etc/mysql/mysqld_multi.cnf stop 1

也可以同时启动和关闭多个数据库实例

启动： mysqd_multi --defaults-file=/etc/mysql/mysqld_multi.cnf start 1-3

关闭： mysqd_multi --defaults-file=/etc/mysql/mysqld_multi.cnf stop 1-3