1 前言

笔者的生产环境需要部署一套单机多实例的MySQL服务，故有此文分享。

2 系统环境

IP Address = 10.168.0.90
OS = CentOS 6.8 x86_64
Host Name = mysql_bin.cmdschool.org

3 配置前的准备

根据以下文档安装部署MySQL的二进制包，
https://www.cmdschool.org/archives/2196

4 配置数据库实例

4.1 停止单实例数据库

/etc/init.d/mysqld stop
chkconfig --del mysqld

4.2 部署配置文件

cp /etc/my.cnf /etc/my.cnf.default
vim /etc/my.cnf

加入如下配置：

# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = multi_admin
password   = shutpassword

[mysqld3306]
socket     = /var/lib/mysql/mysql3306.sock
port       = 3306
pid-file   = /var/run/mysqld/mysqld3306.pid
log-error  = /var/log/mysqld3306.log
datadir    = /data/3306

[mysqld3307]
socket     = /var/lib/mysql/mysql3307.sock
port       = 3307
pid-file   = /var/run/mysqld/mysqld3307.pid
log-error  = /var/log/mysqld3307.log
datadir    = /data/3307

4.3 部署配置文件

mkdir -p /data/3306
mysql_install_db --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/3306/
mkdir -p /data/3307
mysql_install_db --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/3307/

4.4 手动测试启动

4.4.1 执行启动命令

mysqld_multi --defaults-file=/etc/my.cnf start 3306
mysqld_multi --defaults-file=/etc/my.cnf start 3307

4.4.2 确认启动的进程

ps -ef | grep mysql | grep -v grep

可见如下进程则正常：

root       1923      1  0 22:22 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --socket=/var/lib/mysql/mysql3306.sock --port=3306 --pid-file=/var/run/mysqld/mysqld3306.pid --log-error=/var/log/mysqld3306.log --datadir=/data/3306
mysql      2061   1923  1 22:22 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld3306.log --pid-file=/var/run/mysqld/mysqld3306.pid --socket=/var/lib/mysql/mysql3306.sock --port=3306
root       2088      1  0 22:22 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --socket=/var/lib/mysql/mysql3307.sock --port=3307 --pid-file=/var/run/mysqld/mysqld3307.pid --log-error=/var/log/mysqld3307.log --datadir=/data/3307
mysql      2226   2088  1 22:22 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld3307.log --pid-file=/var/run/mysqld/mysqld3307.pid --socket=/var/lib/mysql/mysql3307.sock --port=3307

4.4.2 确认监听的端口

netstat -anpt | grep mysql

可见如下端口则正常：

tcp        0      0 :::3306                     :::*                        LISTEN      2061/mysqld
tcp        0      0 :::3307                     :::*                        LISTEN      2226/mysqld

4.5 初始化密码

mysqladmin -uroot --socket /var/lib/mysql/mysql3306.sock password 'mypassword'
mysqladmin -uroot --socket /var/lib/mysql/mysql3307.sock password 'mypassword'

4.6 修改mysqld_multi代码

vim /usr/local/mysql/bin/mysqld_multi

找到如下行：

my $com= join ' ', 'my_print_defaults', @defaults_options, $group;

修改为：

my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

4.7 配置关闭数据库账号

4.7.1 配置3306端口

mysql -uroot -pmypassword -S /var/lib/mysql/mysql3306.sock
grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'shutpassword';
flush privileges;
quit;

4.7.2 配置3307端口

mysql -uroot -pmypassword -S /var/lib/mysql/mysql3307.sock
grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'shutpassword';
flush privileges;
quit;

4.8 手动测试关闭

mysqld_multi --defaults-file=/etc/my.cnf stop 3306
mysqld_multi --defaults-file=/etc/my.cnf stop 3307

5 部署服务控制

5.1 部署控制脚本

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld

5.2 测试控制脚本

/etc/init.d/mysqld start
/etc/init.d/mysqld report
/etc/init.d/mysqld stop
/etc/init.d/mysqld report
/etc/init.d/mysqld restart
/etc/init.d/mysqld report

5.3 配置服务自启动

chkconfig mysqld on

参阅文档
===========

MySQL的二进制安装
————–
https://www.cmdschool.org/archives/2196

MySQL multi：
————-
https://dev.mysql.com/doc/refman/5.6/en/mysqld-multi.html

MySQL multi 关库错误修复：
————-
https://bugs.mysql.com/bug.php?id=77946