环境介绍
- Mac 系统为最新的 macOS 10.13.6
- MySQL 是使用官网下载安装的
mysql -V : Ver 14.14 Distrib 5.7.23, for macos10.13 (x86_64) using EditLine wrapper - 为什么不用homebrew安装，最后会说原因
- 文中mysql的安装位置为
/usr/local/mysql，如果你的不是，请替换文中的命令路径
准备工作
本次示例使用 mysqld_multi 安装6个 MySQL 实例，端口分别为 3307 ~ 3312。
- 创建各实例的单独数据目录，我的目录在
/usr/local/var，data 目录是存放数据文件的，log 目录是存放慢查询日志文件的，执行如下命令:
mkdir -p mysql_3307/data mysql_3307/log mysql_3308/data mysql_3308/log mysql_3309/data mysql_3309/log mysql_3310/data mysql_3310/log mysql_3311/data mysql_3311/log mysql_3312/data mysql_3312/log
- 初始化data目录
这一步也很重要，如果跳过，mysql启动会报错导致无法启动
cd /usr/local/mysql/bin
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3307/data
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3308/data
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3309/data
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3310/data
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3311/data
./mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/var/mysql_3312/data
- 同理，创建 bin-log 日志文件目录，我的目录在
/usr/local/var/log
mkdir -p mysql_3307 mysql_3308 mysql_3309 mysql_3310 mysql_3311 mysql_3312
- 准备配置文件
/usr/local/etc/my_multi.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = 123456 //注意不要写成password
[mysqld3307]
user = root
server-id = 3307
port = 3307
basedir = /usr/local/mysql
socket = /tmp/mysql_3307.sock
pid-file = /usr/local/var/mysql_3307/mysql.pid
datadir = /usr/local/var/mysql_3307/data
log-bin = /usr/local/var/log/mysql_3307/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3307/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3307/general.log
[mysqld3308]
user = root
server-id = 3308
port = 3308
basedir = /usr/local/mysql
socket = /tmp/mysql_3308.sock
pid-file = /usr/local/var/mysql_3308/mysql.pid
datadir = /usr/local/var/mysql_3308/data
log-bin = /usr/local/var/log/mysql_3308/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3308/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3308/general.log
[mysqld3309]
user = root
server-id = 3309
port = 3309
basedir = /usr/local/mysql
socket = /tmp/mysql_3309.sock
pid-file = /usr/local/var/mysql_3309/mysql.pid
datadir = /usr/local/var/mysql_3309/data
log-bin = /usr/local/var/log/mysql_3309/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3309/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3309/general.log
[mysqld3310]
user = root
server-id = 3310
port = 3310
basedir = /usr/local/mysql
socket = /tmp/mysql_3310.sock
pid-file = /usr/local/var/mysql_3310/mysql.pid
datadir = /usr/local/var/mysql_3310/data
log-bin = /usr/local/var/log/mysql_3310/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3310/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3310/general.log
[mysqld3311]
user = root
server-id = 3311
port = 3311
basedir = /usr/local/mysql
socket = /tmp/mysql_3311.sock
pid-file = /usr/local/var/mysql_3311/mysql.pid
datadir = /usr/local/var/mysql_3311/data
log-bin = /usr/local/var/log/mysql_3311/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3311/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3311/general.log
[mysqld3312]
user = root
server-id = 3312
port = 3312
basedir = /usr/local/mysql
datadir = /usr/local/var/mysql_3312/data
socket = /tmp/mysql_3312.sock
pid-file = /usr/local/var/mysql_3312/mysql.pid
log-bin = /usr/local/var/log/mysql_3312/mysql-bin
binlog_format = mixed
slow_query_log = on
slow_query_log_file = /usr/local/var/log/mysql_3312/slow.log
long_query_time = 1
log-queries-not-using-indexes
log_output = FILE,TABLE
general_log = on
general_log_file = /usr/local/var/log/mysql_3312/general.log
[mysqld]
dmax_connections = 2000
wait_timeout = 10000
validate_password = off
character_set_server=utf8
init_connect='SET NAMES utf8'
#skip-grant-tables
# Only allow connections from localhost
bind-address = 127.0.0.1
启动
执行以下命令启动所有实例：
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start
注意，如果通过其他方式安装过mysql，注意cd到刚才安装的5.7.23版本目录下，使用 ./mysqld_multi 来执行，并且将 /usr/local/etc/my_multi.cnf 中配置的 mysqld 和 mysqladmin 的也设置为此路径下的脚本，以确保没有纰漏出现
此时留意当前命令行日志输出，每个实例会生成一个随机的初始密码，后面第一次登录时需要用到。
可以单独启动某个实例或部分实例，只要在命令后面指定具体的实例id即可，实例id为上面配置文件里标签[mysqld3312] 后面的数字:
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start 3307
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf start 3307,3310-3312
同理，停止所有实例：
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop
停止指定实例：
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop 3307,3310-3312
可以使用 report 命令查看实例运行状态：
mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
MySQL server from group: mysqld3310 is running
MySQL server from group: mysqld3311 is running
MySQL server from group: mysqld3312 is running
登录各实例服务器并修改密码
登录数据库需要指定各实例使用的socket文件，具体文件如上面配置文件所示。使用如下命令登录：
mysql -uroot -S/tmp/mysql_3307.sock -p
输入初始密码即可登录。登录后需要重置密码：
use mysql;
update user set authentication_string=PASSWORD("123456") where User='root';
update user set password_expired='N' where User='root';
quit
如果初始密码没有记住，可以通过以下方式修改初始密码
- 先通过
pa aux | grep mysqld查找到指定的mysql实例，然后kill掉此实例 - 通过以下命令重启此实例，例如实例3312
/bin/sh /usr/local/mysql/bin/mysqld_safe --user=root --server-id=3312 --port=3312 --basedir=/usr/local/mysql --socket=/tmp/mysql_3312.sock --pid-file=/usr/local/var/mysql_3312/mysql.pid --datadir=/usr/local/var/mysql_3312/data --log-bin=/usr/local/var/log/mysql_3312/mysql-bin --binlog_format=mixed --slow_query_log=on --slow_query_log_file=/usr/local/var/log/mysql_3312/slow.log --long_query_time=1 --log-queries-not-using-indexes --log_output=FILE,TABLE --general_log=on --general_log_file=/usr/local/var/log/mysql_3312/general.log --skip-grant-tables --skip-networking &
- 无密码方式登录数据库
mysql -uroot -S/tmp/mysql_3312.sock -p
- 修改root用户密码和密码有效性
use mysql;
update user set authentication_string=PASSWORD("123456") where User='root';
update user set password_expired='N' where User='root';
quit
最后之我遇见的坑
我通过以上方式完成了步骤，但是无论如何都启动不了5个实例，最后通过ps查看进程发现，所有的mysqld实例使用的 datadir 都是一个默认的相同的路径，但是我查看了我的 /usr/local/etc/my_multi.cnf 文件，明明已经各自指定了不同的 datadir
出现这种情况的时候，我的mysql是通过 brew install mysql 安装的最新版本 8.0.12，这个版本不仅没有 mysql_install_db 脚本导致我安装data的时候历经磨难，没想到最后出现了 mysqld_multi.cnf 指定不了 datadir 这种奇怪的问题。
最后不得已，我从官网下载了 5.7.23 版本，安装后一次启动成功，没有出任何问题。
遗留的问题
我无法通过 mysqld_multi --defaults-file=/usr/local/etc/my_multi.cnf stop 来关闭实例，即使我已经正确的配置了 user 和 pass，最后只能通过以下命令关闭：
/usr/local/mysql/bin/mysqladmin -uroot -p123456 -S /tmp/mysql_3307.sock shutdown
