使用mysqld_multi管理多个MySQL实例

anmile

1. 修改mysql的配置文件my.cnf配置mysqld_multi的选项

1. [mysqld_multi]
   mysqld          = /usr/local/mysql/bin/mysqld_safe
   mysqladmin      = /usr/local/mysql/bin/mysqladmin
   user            = multi_admin
   password        = multi_passwd

2.修改mysql的配置文件my.cnf 创建mysqld_multi的config-file

[mysqld1]
port            = 3306
socket          = /tmp/mysql1.sock
datadir         = /var/lib/mysql1
user            = mysql

[mysqld2]
port            = 3307
socket          = /tmp/mysql2.sock
datadir         = /var/lib/mysql2
user            = mysql

[mysqld3]
port            = 3308
socket          = /tmp/mysql3.sock
datadir         = /var/lib/mysql3
user            = mysql

 

3.  初始化数据库

 

/usr/local/mysql/scripts/mysql_install_db --datadir=/var/lib/mysql1 --user=mysql
/usr/local/mysql/scripts/mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
/usr/local/mysql/scripts/mysql_install_db --datadir=/var/lib/mysql3 --user=mysql

 

4.  启动多实例

 

mysqld_multi start 1-3

或

mysqld_multi start 1

mysqld_multi start 2

mysqld_multi start 3

 

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

 

1. mysql -uroot -S /tmp/mysql1.sock -e 'grant SHUTDOWN ON *.* TO multi_admin@localhost identified by "multi_passwd";'
2. 
   mysql -uroot -S /tmp/mysql2.sock -e 'grant SHUTDOWN ON *.* TO multi_admin@localhost identified by "multi_passwd";'
3. 
   mysql -uroot -S /tmp/mysql3.sock -e 'grant SHUTDOWN ON *.* TO multi_admin@localhost identified by "multi_passwd";'

 

6. 关闭数据库实例

 

mysqld_multi stop 1-3

或

mysqld_multi stop 1

mysqld_multi stop 2

mysqld_multi stop 3