一.什么是多实例

1）多套后台进程+线程+内存结构
2）多个配置文件
a.多个端口
b.多个socket文件
c.多个日志文件
d.多个server_id
3）多套数据

二.多实例部署

说明：数据库安装请参考链接文档：https://www.jianshu.com/p/22ccea85428e

2.1创建配置文件存放目录

[root@db02 ~]# mkdir /data/{13307,13308,13309} -p

2.2准备不同的配置文件

#编辑13307配置文件
[root@db02 ~]# vim /data/13307/my.cnf
[mysqld]
port = 13307
log-bin = mysql-bin
server_id = 7
datadir = /data/13307/data
basedir = /application/mysql
socket = /data/13307/data/mysql.sock
log_error = /data/13307/data/error.log
pid_file = /data/13307/data/mysql.pid
[client]
socket = /data/13307/data/mysql.sock
#编辑13308配置文件
[root@db02 ~]# vim /data/13308/my.cnf
[mysqld]
port = 13308
log-bin = mysql-bin
server_id = 8
datadir = /data/13308/data
basedir = /application/mysql
socket = /data/13308/data/mysql.sock
log_error = /data/13308/data/error.log
pid_file = /data/13308/data/mysql.pid
[client]
socket = /data/13308/data/mysql.sock
#编辑13309配置文件
[root@db02 ~]# vim /data/13309/my.cnf
[mysqld]
port = 13309
log-bin = mysql-bin
server_id = 9
datadir = /data/13309/data
basedir = /application/mysql
socket = /data/13309/data/mysql.sock
log_error = /data/13309/data/error.log
pid_file = /data/13309/data/mysql.pid
[client]
socket = /data/13309/data/mysql.sock

2.3初始化出不同的datadir

#切换到初始化目录
[root@db02 ~]# cd /application/mysql/scripts/
#初始化13307数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13307/my.cnf --basedir=/application/mysql --datadir=/data/13307/data

Installing MySQL system tables...2019-11-27 19:54:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-27 19:54:11 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2019-11-27 19:54:11 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 26472 ...
OK

Filling help tables...2019-11-27 19:54:14 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-27 19:54:14 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2019-11-27 19:54:14 0 [Note] /application/mysql/bin/mysqld (mysqld 5.6.40-log) starting as process 26494 ...
OK

出现两个ok 这样才证明初始化成功。
#初始化13308数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13308/my.cnf --basedir=/application/mysql --datadir=/data/13308/data
#初始化13309数据
[root@db02 /application/mysql/scripts]# ./mysql_install_db --user=mysql --defaults-file=/data/13309/my.cnf --basedir=/application/mysql --datadir=/data/13309/data

2.5修改目录权限

[root@db02 /application/mysql/scripts]# chown -R mysql.mysql /data/1330*

2.6启动服务

#启动13307
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13307/my.cnf &
#启动13308
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13308/my.cnf &
#启动13309
[root@db02 /application/mysql/scripts]# mysqld_safe --defaults-file=/data/13309/my.cnf &
#查看端口确认端口是否已经启动
[root@db02 /application/mysql/scripts]# ss -lntp|grep 133
LISTEN     0      80          :::13307                   :::*                   users:(("mysqld",pid=26890,fd=11))
LISTEN     0      80          :::13308                   :::*                   users:(("mysqld",pid=27068,fd=11))
LISTEN     0      80          :::13309                   :::*                   users:(("mysqld",pid=27235,fd=11))
#关闭实例
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13307/data/mysql.sock shutdown
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13308/data/mysql.sock shutdown
[root@db02 /application/mysql/scripts]# mysqladmin -S /data/13309/data/mysql.sock shutdown

2.7本地连接实例

#连接13307
#指定socket连接
root@db02 /application/mysql/scripts]#  mysql -S /data/13307/data/mysql.sock
#连接13308
[root@db02 /application/mysql/scripts]#  mysql -S /data/13308/data/mysql.sock
#连接13309
[root@db02 /application/mysql/scripts]#  mysql -S /data/13309/data/mysql.sock

2.8编写连接脚本

[root@db02 ~]# vim /usr/local/bin/mysql13307
mysql -S /data/13307/data/mysql.sock
[root@db02 ~]# vim /usr/local/bin/mysql13308
mysql -S /data/13308/data/mysql.sock
[root@db02 ~]# vim /usr/local/bin/mysql13309
mysql -S /data/13309/data/mysql.sock
#授予可执行权限
[root@db02 ~]# chmod +x /usr/local/bin/mysql1330*

测试连接

[root@db02 ~]# mysql13307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

9.编写启动脚本

[root@db02 ~]# vim /usr/lib/systemd/system/mysql13307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13307/my.cnf

[root@db02 ~]# vim /usr/lib/systemd/system/mysql13308.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13308/my.cnf

[root@db02 ~]# vim /usr/lib/systemd/system/mysql13309.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/13309/my.cnf

测试
说明：默认只能使用一种方式进行实例管理，这里需要按照之前的方式先关闭实例，之后测试。

#关闭实例
[root@db02 ~]# mysqladmin -S /data/13307/data/mysql.sock shutdown
191127 21:55:11 mysqld_safe mysqld from pid file /data/13307/data/mysql.pid ended
[3]+  Done                    mysqld_safe --defaults-file=/data/13307/my.cnf  (wd: /application/mysql/scripts)
(wd now: ~)
[root@db02 ~]# mysqladmin -S /data/13308/data/mysql.sock shutdown
191127 21:55:19 mysqld_safe mysqld from pid file /data/13308/data/mysql.pid ended
[2]+  Done                    mysqld_safe --defaults-file=/data/13308/my.cnf  (wd: /application/mysql/scripts)
(wd now: ~)
[root@db02 ~]# mysqladmin -S /data/13309/data/mysql.sock shutdown
191127 21:55:26 mysqld_safe mysqld from pid file /data/13309/data/mysql.pid ended
[1]+  Done                    mysqld_safe --defaults-file=/data/13309/my.cnf  (wd: /application/mysql/scripts)
(wd now: ~)
#启动实例
[root@db02 ~]# systemctl start mysql13307.service
[root@db02 ~]# systemctl start mysql13308.service
[root@db02 ~]# systemctl start mysql13309.service
#查看端口是否处于监听状态
[root@db02 ~]# ss -lntp|grep 133
LISTEN     0      80          :::13307                   :::*                   users:(("mysqld",pid=12421,fd=11))
LISTEN     0      80          :::13308                   :::*                   users:(("mysqld",pid=12449,fd=11))
LISTEN     0      80          :::13309                   :::*                   users:(("mysqld",pid=12477,fd=11))

至此多实例部署完成。