小技巧：MySQL多实例管理

也就是一台服务器运行多个mysql服务
1、准备多个用于存放不同实例的数据目录

$ mkdir -p /usr/local/mysql5.7/data/330{7,8,9}/data

2、编辑my.conf配置文件

$ cat >/usr/local/mysql5.7/data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql5.7/bin/mysql
datadir=/usr/local/mysql5.7/data/3307/data
socket=/usr/local/mysql5.7/data/3307/mysql.sock
log_error=/usr/local/mysql5.7/data/3307/mysql.log
port=3307
server_id=7
log_bin=/usr/local/mysql5.7/data/3307/mysql-bin
EOF

$ cat > /usr/local/mysql5.7/data/3308/my.cnf  <<EOF
[mysqld]
basedir=/usr/local/mysql5.7/bin/mysql
datadir=/usr/local/mysql5.7/data/3308/data
socket=/usr/local/mysql5.7/data/3308/mysql.sock
log_error=/usr/local/mysql5.7/data/3308/mysql.log
port=3308
server_id=8
log_bin=/usr/local/mysql5.7/data/3308/mysql-bin
EOF

$ cat >/usr/local/mysql5.7/data/3309/my.cnf  <<EOF
[mysqld]
basedir=/usr/local/mysql5.7/bin/mysql
datadir=/usr/local/mysql5.7/data/3309/data
socket=/usr/local/mysql5.7/data/3309/mysql.sock
log_error=/usr/local/mysql5.7/data/3309/mysql.log
port=3309
server_id=9
log_bin=/usr/local/mysql5.7/data/3309/mysql-bin
EOF

3、初始化这三个mysql实例

$ mv /etc/my.cnf /etc/my.cnf.bak
$ mysqld --initialize-insecure  --user=mysql --datadir=/usr/local/mysql5.7/data/3307/data --basedir=/usr/local/mysql5.7/bin/mysql
$ mysqld --initialize-insecure  --user=mysql --datadir=/usr/local/mysql5.7/data/3308/data --basedir=/usr/local/mysql5.7/bin/mysql
$ mysqld --initialize-insecure  --user=mysql --datadir=/usr/local/mysql5.7/data/3309/data --basedir=/usr/local/mysql5.7/bin/mysql

4、使用systemd管理实例

$ cd /etc/systemd/system
$ cp mysqld.service mysqld3307.service
$ cp mysqld.service mysqld3308.service
$ cp mysqld.service mysqld3309.service

$ vim mysqld3307.service
ExecStart=/usr/local/mysql5.7/bin/mysqld  --defaults-file=/usr/local/mysql5.7/data/3307/my.cnf
$ vim mysqld3308.service
ExecStart=/usr/local/mysql5.7/bin/mysqld  --defaults-file=/usr/local/mysql5.7/data/3308/my.cnf
$ vim mysqld3309.service
ExecStart=/usr/local/mysql5.7/mysqld  --defaults-file=/usr/local/mysql5.7/data/3309/my.cnf

5、授权

$ chown -R mysql.mysql /usr/local/mysql5.7/data/*

6、启动

$ systemctl start mysqld3307.service
$ systemctl start mysqld3308.service
$ systemctl start mysqld3309.service

7、验证

$ netstat -lnp|grep 330
$ netstat -tulnp
$ mysql -S /usr/local/mysql5.7/data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+

$ mysql -S /usr/local/mysql5.7/data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+

$ mysql -S /usr/local/mysql5.7/data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

学习来自：郭老师博客，老男孩深标DBA课程 第二章