MySQL系列：（四）MySQL多实例管理

1、准备多个目录

[root@localhost ~]# mkdir -p /data/db_{1,2,3}/data

2、准备配置文件

# 修改第一个实例的配置文件
[root@localhost ~]# cat > /data/db_1/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/db_1/data
socket=/data/db_1/data/mysql.sock
port=3307
log-error=/data/db_1/data/localhost.localdomain.err
user=mysql
local_infile=OFF
server_id=1

[mysql]
socket=/data/db_1/data/mysql.sock
EOF

# 修改第二个实例的配置文件
[root@localhost ~]# cat > /data/db_2/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/db_2/data
socket=/data/db_2/data/mysql.sock
port=3308
log-error=/data/db_2/data/localhost.localdomain.err
user=mysql
local_infile=OFF
server_id=2

[mysql]
socket=/data/db_2/data/mysql.sock
EOF

# 修改第三个实例的配置文件
[root@localhost ~]# cat > /data/db_3/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/db_3/data
socket=/data/db_3/data/mysql.sock
port=3309
log-error=/data/db_3/data/localhost.localdomain.err
user=mysql
local_infile=OFF
server_id=3

[mysql]
socket=/data/db_3/data/mysql.sock
EOF

3、初始化三套数据

[root@localhost ~]# mv /etc/my.cnf /etc/my.cnf.bak
[root@localhost ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/db_1/data --basedir=/application/mysql
[root@localhost ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/db_2/data --basedir=/application/mysql
[root@localhost ~]# mysqld --initialize-insecure  --user=mysql --datadir=/data/db_3/data --basedir=/application/mysql

4、systemd管理多实例

[root@localhost ~]# cd /etc/systemd/system
[root@localhost system]# cp mysqld.service db_1.service
[root@localhost system]# cp mysqld.service db_2.service
[root@localhost system]# cp mysqld.service db_3.service

# 修改第一个实例的启动参数
[root@localhost ~]# vim db_1.service
## 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/db_1/my.cnf

# 修改第二个实例的启动参数
[root@localhost ~]# vim mysqld_2.service
## 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/db_2/my.cnf

# 修改第三个实例的启动参数
[root@localhost ~]# vim mysqld_3.service
## 修改为:
ExecStart=/application/mysql/bin/mysqld  --defaults-file=/data/db_3/my.cnf

5、修改授权

[root@localhost ~]# chown -R mysql:mysql /data/*

6、启动实例

[root@localhost ~]# systemctl start db_1.service
[root@localhost ~]# systemctl start db_2.service
[root@localhost ~]# systemctl start db_3.service

7、验证多实例

[root@localhost ~]# netstat -lnp|grep db_

执行返回结果：
unix  2      [ ACC ]     STREAM     LISTENING     35595    5136/mysqld          /data/db_1/data/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     35861    5224/mysqld          /data/db_3/data/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     35714    5180/mysqld          /data/db_2/data/mysql.sock