MySQL多实例部署

1.环境准备

首先需要安装好mysql数据库，可以使用二进制安装、编译安装、yum的方法。

本文以二进制安装的mysql为例在一台服务器上搭建mysql多实例。如果采用yum安装的mysql，搭建多实例的过程会存在一些细微差距。

二进制安装mysql见这篇文章：https://www.wanhebin.com/database/mysql/614.html

2.搭建多实例

2.1 创建多实例的目录

mkdir -p /data/mysql/{3307,3308,3309}

2.2 配置多实例的配置文件

• 多实例配置文件

[root@db01 ~]# vim /data/mysql/3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/tmp/mysql.sock
port=3307
log-error=/data/mysql/3307/log/mysql.err
log-bin=/data/mysql/3307/log/mysql-bin
server_id=7

[root@db01 ~]# vim /data/mysql/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/tmp/mysql.sock
port=3308
log-error=/data/mysql/3308/log/mysql.err
log-bin=/data/mysql/3308/log/mysql-bin
server_id=8

[root@db01 ~]# vim /data/mysql/3309/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/tmp/mysql.sock
port=3309
log-error=/data/mysql/3309/log/mysql.err
log-bin=/data/mysql/3309/log/mysql-bin
server_id=9

• 创建日志和socket文件目录

mkdir -p /data/mysql/330{7,8,9}/{tmp,log}

2.3 授权目录

chown -R mysql:mysql /data/mysql

2.4 初始化多个实例数据库

• 初始化实例

/usr/local/mysql/scripts/mysql_install_db --defaults-file=/data/mysql/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data

/usr/local/mysql/scripts/mysql_install_db --defaults-file=/data/mysql/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308/data

/usr/local/mysql/scripts/mysql_install_db --defaults-file=/data/mysql/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3309/data

• 查看初始化后的目录

  每个实例的目录都要查看，当出现以下结构时，说明数据库初始化成功了

[root@db01 ~]# tree -L 3 /data/mysql
/data/mysql
├── 3307
│   ├── data
│   │   ├── ibdata1
│   │   ├── ib_logfile0
│   │   ├── ib_logfile1
│   │   ├── mysql
│   │   ├── performance_schema
│   │   └── test
│   ├── log
│   │   ├── mysql-bin.000001
│   │   ├── mysql-bin.000002
│   │   ├── mysql-bin.index
│   │   └── mysql.err
│   ├── my.cnf
│   └── tmp

3.启动多实例

3.1 使用安全启动脚本启动mysql

mysqld_safe --defaults-file=/data/mysql/3307/my.cnf &

mysqld_safe --defaults-file=/data/mysql/3308/my.cnf &

mysqld_safe --defaults-file=/data/mysql/3309/my.cnf &

3.2 验证是否启动

[root@db01 ~]# netstat -tlnp | grep mysqld
tcp6       0      0 :::3307              :::*                 LISTEN      18035/mysqld        
tcp6       0      0 :::3308              :::*                 LISTEN      18208/mysqld        
tcp6       0      0 :::3309              :::*                 LISTEN      18380/mysqld  

3.3 连接登录每个实例

• 连接实例

在连接这些实例的时候，需要制定每个实例的socket文件

mysql -S /data/mysql/3307/tmp/mysql.sock

mysql -S /data/mysql/3308/tmp/mysql.sock

mysql -S /data/mysql/3309/tmp/mysql.sock

• 确认登录的是否为指定实例

[root@db01 ~]# mysql -S /data/mysql/3307/tmp/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+
[root@db01 ~]# mysql -S /data/mysql/3308/tmp/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+
[root@db01 ~]# mysql -S /data/mysql/3309/tmp/mysql.sock -e 'show variables like "server_id";'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+

3.4 配置多实例的登录密码

mysqladmin -uroot password '123' -S /data/mysql/3307/tmp/mysql.sock

mysqladmin -uroot password '123' -S /data/mysql/3308/tmp/mysql.sock

mysqladmin -uroot password '123' -S /data/mysql/3309/tmp/mysql.sock

3.5 配置快速连接命令

• 添加命令

echo "mysql -uroot -p123 -S /data/mysql/3307/tmp/mysql.sock" > /usr/bin/mysql3307

echo "mysql -uroot -p123 -S /data/mysql/3308/tmp/mysql.sock" > /usr/bin/mysql3308

echo "mysql -uroot -p123 -S /data/mysql/3309/tmp/mysql.sock" > /usr/bin/mysql3309

• 给文件加执行权限

chmod +x /usr/bin/mysql330*

• 快速连接

直接使用命令mysql3307即可连接到3307这个数据库实例

4.使用systemd管理每个mysql实例

4.1 编写mysql多实例的unit文件
给每个实例编写一个unit文件，使用systemd来管理每个实例的启动、关闭等

• 配置3307实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3307.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=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
LimitNOFILE = 5000

• 配置3308实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3308.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=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
LimitNOFILE = 5000

• 配置3309实例

[root@db01 ~]# vim /usr/lib/systemd/system/mysqld3309.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=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
LimitNOFILE = 5000

4.2 重载系统的unit文件

systemctl daemon-reload

4.3 启动mysql多个实例

systemctl start mysql3307.service mysql3308.service mysql3309.service