MySQL使用mysqld_multi进行单机多实例配置

本文以MySQL5.6.43和5.7.25为测试样本

1.下载安装包

MySQL5.6.43的下载链接：

https://downloads.mysql.com/archives/get/file/mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz

MySQL5.7.25的下载链接：

https://downloads.mysql.com/archives/get/file/mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

2.解压并安装MySQL

5.6

tar zxvf mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-5.6.43-linux-glibc2.12-x86_64 mysql

5.7 

tar zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-5.7.25-linux-glibc2.12-x86_64 mysql

3.配置my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = my_password
log        = /var/log/mysqld_multi.log

[mysqld]
#这里写公共参数，如果下面的标签中有，这里的参数将被覆盖
#注意有些关于innodb的配置，例如innodb_data_file_path，要配置相同，否则可能会出错

[mysqld1]
port                 = 3306
socket               = /data/mysql3306/mysql.sock
pid_file             = /data/mysql3306/mysql.pid
basedir              = /usr/local/mysql
datadir              = /data/mysql3306

# LOGGING
long_query_time         = 5
slow_query_log          = 1
log_error               = /data/mysql3306/error.log
slow_query_log_file     = /data/mysql3306/slow_query.log
log_warnings            = 1

# INNODB
innodb_data_home_dir      = /data/mysql3306
innodb_buffer_pool_size   = 1024M
innodb_log_file_size      = 1G
innodb_data_file_path     = ibdata1:12M:autoextend:max:1024M

# REPLICATION
server-id               = 2303306
log-bin                 = /data/mysql3306/mysql-binlog

[mysqld2]
socket     = /data/mysql3307/mysql.sock
port       = 3307
pid-file   = /usr/local/mysql/data2/hostname.pid2
datadir    = /usr/local/mysql/data2
language   = /usr/local/mysql/share/mysql/english

[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /usr/local/mysql/data3/hostname.pid3
datadir    = /usr/local/mysql/data3
language   = /usr/local/mysql/share/mysql/swedish

4.修改mysqld_multi文件

# which mysqld_multi
/usr/bin/mysqld_multi
# vi /usr/bin/mysqld_multi

执行一下修改操作 

my $com= join ' ', 'my_print_defaults ', @defaults_options, $group; 
替换为： 
my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

因为-s代表从配置文件中获取密码，否则无法停止mysql，并且密码为********，错误日志为:

/usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

另外，5.6版本的MySQL，需要执行以下命令拷贝脚本：

cp /usr/local/mysql/scripts/mysql_install_db /usr/local/mysql/bin/

5.使用mysqld_multi控制MySQL

/usr/local/mysql/bin/mysql_install_db report

/usr/local/mysql/bin/mysql_install_db start {num}  #如果数据库没有初始化，这一步将自动初始化数据库

/usr/local/mysql/bin/mysql_install_db stop {num}  #记得先设置数据库密码，并跟my.cnf一致