MySQL多实例启动

一台服务器安装MySQL,用于测试。同时会用于NextCloud和Wordpress的数据库。NextCloud和Wordpress要经常备份,而且以后可能会迁移。所以隔离不同情景的数据,用多实例启动。便于管理,和提高性能。

首先初始化要用到的数据库:

mysqld --initialize-insecure --datadir=/home/mysql/3307/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3308/data --user=mysql;
mysqld --initialize-insecure --datadir=/home/mysql/3309/data --user=mysql;

MySQL自带了mysqld_multi工具运行多个实例。以下是我的配置文件:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
#password = 

# nextcloud
[mysqld3307]
user		= mysql
pid-file	= /home/mysql/3307/mysqld.pid
socket		= /home/mysql/3307/mysqld.sock
port		= 3307
datadir		= /home/mysql/3307/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000

# wordpress
[mysqld3308]
user		= mysql
pid-file	= /home/mysql/3308/mysqld.pid
socket		= /home/mysql/3308/mysqld.sock
port		= 3308
datadir		= /home/mysql/3308/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000
innodb_io_capacity = 4000

[mysqld3309]
user		= mysql
pid-file	= /home/mysql/3309/mysqld.pid
socket		= /home/mysql/3309/mysqld.sock
port		= 3309
datadir		= /home/mysql/3309/data
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
expire_logs_days	= 10
max_binlog_size   = 100M
innodb_buffer_pool_size = 1G
innodb_io_capacity = 4000

配置多实例,每个实例命名为mysqld*,这里我三个实例配置分别对应[mysql3307],[mysql3308],[mysql3309]。

启动:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql start

如果需要启动指定的实例,可以在start后面跟上参数,使用实例名,如:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql start 3307

或,启动指定多个实例:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql start 3307,3308

查看启动状态:

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql report

关闭(也可以关闭指定实例,和start用法一样):

mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld_multi.cnf --user=mysql stop

我在执行stop命令时,实例未关闭。网上遇到该问题的人有改mysqld_multi.cnf脚本的,我试过,加上参数-s后直接报错。有说是因为权限问题,需要授权:grant shutdown on . to ‘username’@’localhost’ identified by ‘password’。我用的root用户启动,试过用mysqladmin去执行关闭,是可以成功的。

mysqladmin -h127.0.0.1 -P3309 -uroot shutdown

留下评论

电子邮件地址不会被公开。