mysql_multi 管理多实例,废话不多说,直接上操作!
1.把以下内容添加到/my.cnf 文件里面
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /opt/mysql/mysqld_multi.log
user = admin
password = admin
[mysqld3306]
port = 3306
#指定本实例相应版本的basedir和datadir
#basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
tmpdir = /data/mysql/mysql_3306/tmp/
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
server-id = 23306
socket = /tmp/mysql3306.sock
[mysqld3307]
port = 3307
#指定本实例相应版本的basedir和datadir
#basedir= /usr/local/mysql
datadir = /data/mysql/mysql_3307/data
tmpdir = /data/mysql/mysql_3307/tmp/
log-bin = /data/mysql/mysql_3307/logs/mysql-bin
server-id = 23307
socket = /tmp/mysql3307.sock
2.创建一个专门启动关闭的mysql用户,只赋予关闭库的权限。
mysql> create user 'admin'@'localhost' identified by 'admin';
mysql> grant shutdown on *.* to 'admin'@'localhost';
3.查看host是不是localhost
root@localhost [(none)]>select host,user from mysql.user;
+-----------+-----------+
| host | user |
+-----------+-----------+
| localhost | admin |
| localhost | mysql.sys |
| localhost | root |
| localhost | zhengwei |
+-----------+-----------+
4 rows in set (0.00 sec)
root@localhost [(none)]>flush privileges;
Query OK, 0 rows affected (0.02 sec)
root@localhost [(none)]>show grants for 'admin'@'localhost';
+----------------------------------------------+
| Grants for admin@localhost |
+----------------------------------------------+
| GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)
root@localhost [(none)]>
4.用新建的账户关闭库
[root@zw-test-db data]# mysqladmin -S /tmp/mysql3306.sock -uadmin -p shutodwn
5.用mysqld_multi启动3306:
[root@zw-test-db bin]# mysqld_multi start 3306
[root@zw-test-db bin]# ps -ef|grep mysql
root 8234 1 0 16:07 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3306 --datadir=/data/mysql/mysql_3306/data --tmpdir=/data/mysql/mysql_3306/tmp/ --log-bin=/data/mysql/mysql_3306/logs/mysql-bin --server-id=23306 --socket=/tmp/mysql3306.sock
mysql 8393 8234 7 16:07 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --tmpdir=/data/mysql/mysql_3306/tmp/ --log-bin=/data/mysql/mysql_3306/logs/mysql-bin --server-id=23306 --log-error=/data/mysql/mysql_3306/data/zw-test-db.err --pid-file=/data/mysql/mysql_3306/data/zw-test-db.pid --socket=/tmp/mysql3306.sock --port=3306
root 8422 29010 0 16:07 pts/0 00:00:00 grep mysql
6.用mysqld_multi启动3307:
[root@zw-test-db bin]# mysqld_multi start 3307
[root@zw-test-db bin]# ps -ef|grep mysql
root 8234 1 0 16:07 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3306 --datadir=/data/mysql/mysql_3306/data --tmpdir=/data/mysql/mysql_3306/tmp/ --log-bin=/data/mysql/mysql_3306/logs/mysql-bin --server-id=23306 --socket=/tmp/mysql3306.sock
mysql 8393 8234 1 16:07 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --tmpdir=/data/mysql/mysql_3306/tmp/ --log-bin=/data/mysql/mysql_3306/logs/mysql-bin --server-id=23306 --log-error=/data/mysql/mysql_3306/data/zw-test-db.err --pid-file=/data/mysql/mysql_3306/data/zw-test-db.pid --socket=/tmp/mysql3306.sock --port=3306
root 8427 1 0 16:07 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --port=3307 --datadir=/data/mysql/mysql_3307/data --tmpdir=/data/mysql/mysql_3307/tmp/ --log-bin=/data/mysql/mysql_3307/logs/mysql-bin --server-id=23307 --socket=/tmp/mysql3307.sock
mysql 8586 8427 9 16:07 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --tmpdir=/data/mysql/mysql_3307/tmp/ --log-bin=/data/mysql/mysql_3307/logs/mysql-bin --server-id=23307 --log-error=/data/mysql/mysql_3307/data/zw-test-db.err --pid-file=/data/mysql/mysql_3307/data/zw-test-db.pid --socket=/tmp/mysql3307.sock --port=3307
root 8616 29010 0 16:07 pts/0 00:00:00 grep mysql
可以看到都启动了
7.打印mysql3307的参数
[root@zw-test-db tmp]# my_print_defaults mysqld_multi mysqld3307
--mysqld=/usr/local/mysql/bin/mysqld_safe
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--log=/opt/mysql/mysqld_multi.log
--user=admin
--password=*****
--port=3307
--datadir=/data/mysql/mysql_3307/data
--tmpdir=/data/mysql/mysql_3307/tmp/
--log-bin=/data/mysql/mysql_3307/logs/mysql-bin
--server-id=23307
--socket=/tmp/mysql3307.sock
把参数文件的内容打印出来了,可以看到密码是保密的
加了一个-s参数就可以看到密码了
[root@zw-test-db tmp]# my_print_defaults -s mysqld_multi mysqld3307
--mysqld=/usr/local/mysql/bin/mysqld_safe
--mysqladmin=/usr/local/mysql/bin/mysqladmin
--log=/opt/mysql/mysqld_multi.log
--user=admin
--password=admin
--port=3307
--datadir=/data/mysql/mysql_3307/data
--tmpdir=/data/mysql/mysql_3307/tmp/
--log-bin=/data/mysql/mysql_3307/logs/mysql-bin
--server-id=23307
--socket=/tmp/mysql3307.sock
也可以修改mysqld_multi文件
常用命令:
mysqld_multi start
mysqld_multi start 1,2
mysqld_multi stop
mysqld_multi stop 1,2