参考文档:https://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html
MySQL的多实例使用 mysqld_multi 来管理, 使用这个命令来启动、停止、导出实例状态。
Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
一、环境配置如下:
[root@localhost ~]# cat /etc/issue
CentOS release 6.8 (Final)
Kernel \r on an \m
[root@localhost ~]# uname -r
2.6.32-642.el6.x86_64
[root@localhost ~]# mysql -V //使用yum安装
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
二、my.cnf
可以使用mysql --help,来查看查找MySQL主配置文件的顺序
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
直接使用/etc目录下的my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = my_password
log = /var/log/mysqld_multi.log
[mysqld3307]
port = 3307
datadir = /var/lib/mysql/3307
socket = /var/lib/mysql/3307/mysql.sock
pid-file = /var/lib/mysql/3307/mysqld.pid
#user = unix_user1 //默认mysql
[mysqld3308]
port = 3308
datadir = /var/lib/mysql/3308
socket = /var/lib/mysql/3308/mysql.sock
pid-file = /var/lib/mysql/3308/mysqld.pid
#user = unix_user1
[mysqld_safe]
log-error=/var/log/mysqld.log
三、MySQL多实例中每个实例必须使用独立数据文件夹,不允许几个实例共用一个数据文件夹
在上面的配置文件中设定了二个实例,其datadir分别为/var/lib/mysql/3307与/var/lib/mysql/3308。
现在分别生成主数据库,如下:
[root@localhost ~]# mysql_install_db --datadir=/var/lib/mysql/3307 --user=mysql
[root@localhost ~]# mysql_install_db --datadir=/var/lib/mysql/3308 --user=mysql
四、启动与停止
启动实例:
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
[root@localhost ~]# mysqld_multi start
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
当然,也可以启动指定的某个实例,
使用mysqld_multi start 3307,这里的3307是在my.cnf文件中[mysqld3307],mysqld后面的一个整数指定的。
停止实例:
停止实例需要在每个实例的主数据库中生成一个mysql用户,
用户名与密码指定在my.cnf中[mysqld_multi]段中的user与password。
现在先生成root用户密码:
[root@localhost ~]# mysqladmin -S /var/lib/mysql/3307/mysql.sock -u root password 'root'
[root@localhost ~]# mysqladmin -S /var/lib/mysql/3308/mysql.sock -u root password 'root'
登陆mysql,生成用户,每个实例都需要生成与my.cnf中匹配的用户与密码
[root@localhost ~]# mysql -u root -P 3307 -h 127.0.0.1 -p
mysql> grant SHUTDOWN on *.* to 'multi_admin'@'localhost' identified by 'my_password';
mysql> flush privileges;
此时就可以使用 mysqld_multi stop 或 mysqld_multi stop [GNR] 来关闭实例。