配置多实例的MySQL数据库

一、准备多个目录

[root@localhost data]# mkdir /data/3307/data -p 
[root@localhost data]# mkdir /data/3308/data -p 
[root@localhost data]# mkdir /data/3309/data -p 

二、编写每个实例的配置文件（my.cnf）

3307

[root@localhost /]# vim /data/3307/my.cnf 
#服务器端配置
[mysqld]
#用户
user=mysql   
#软件安装目录                
basedir=/app/mysql  
#数据路径
datadir=/data/3307/data 
#socket文件位置
socket=/data/3307/mysql3307.sock
#服务器id号
server_id=7
#端口号
port=3307
#错误日志位置
log_error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
#客户端配置
[mysql]
#socket文件位置
socket=/data/3307/mysql3307.sock

3308

[root@localhost ~]# vim /data/3308/my.cnf 
#服务器端配置
[mysqld]
#用户
user=mysql   
#软件安装目录                
basedir=/app/mysql  
#数据路径
datadir=/data/3308/data 
#socket文件位置
socket=/data/3308/mysql3308.sock
#服务器id号
server_id=8
#端口号
port=3308
#错误日志位置
log_error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
#客户端配置
[mysql]
#socket文件位置
socket=/data/3308/mysql3308.sock

3309

[root@localhost ~]# vim /data/3309/my.cnf  
#服务器端配置
[mysqld]
#用户
user=mysql   
#软件安装目录                
basedir=/app/mysql  
#数据路径
datadir=/data/3309/data 
#socket文件位置
socket=/data/3309/mysql3309.sock
#服务器id号
server_id=9
#端口号
port=3309
#错误日志位置
log_error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
#客户端配置
[mysql]
#socket文件位置
socket=/data/3309/mysql3309.sock

三、初始化三套实例

3307

mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data 

3308

[root@localhost ~]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3308/data 

3309

[root@localhost mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3309/data  

四、修改systemd启动文件

[root@localhost ~]# cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3307.service 
[root@localhost ~]# cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3308.service  
[root@localhost ~]# cp /etc/systemd/system/mysqld.service  /etc/systemd/system/mysqld3309.service  

3307

[root@localhost ~]# vim /etc/systemd/system/mysqld3307.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000

3308

[root@localhost ~]# vim /etc/systemd/system/mysqld3308.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000

3309

[root@localhost ~]# vim /etc/systemd/system/mysqld3309.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://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=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
 

五、授权

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

六、启动测试

[root@localhost ~]# systemctl start mysqld3307.service
[root@localhost ~]# systemctl start mysqld3308.service
[root@localhost ~]# systemctl start mysqld3309.service

[root@localhost ~]# systemctl status mysqld3307.service
● mysqld3307.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld3307.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-01-19 05:51:41 CST; 41s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 11086 (mysqld)
    Tasks: 27 (limit: 23825)
   Memory: 177.6M
   CGroup: /system.slice/mysqld3307.service
           └─11086 /app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

Jan 19 05:51:41 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# systemctl status mysqld3308.service
● mysqld3308.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld3308.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-01-19 05:51:41 CST; 54s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 11090 (mysqld)
    Tasks: 27 (limit: 23825)
   Memory: 178.2M
   CGroup: /system.slice/mysqld3308.service
           └─11090 /app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

Jan 19 05:51:41 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost ~]# systemctl status mysqld3309.service
● mysqld3309.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld3309.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-01-19 05:51:43 CST; 56s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 11095 (mysqld)
    Tasks: 27 (limit: 23825)
   Memory: 178.6M
   CGroup: /system.slice/mysqld3309.service
           └─11095 /app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

Jan 19 05:51:43 localhost.localdomain systemd[1]: Started MySQL Server.

七、登陆

[root@localhost ~]# mysql -S /data/3307/mysql3307.sock 
[root@localhost ~]# mysql -S /data/3307/mysql3308.sock
 [root@localhost ~]# mysql -S /data/3307/mysql3309.sock
 或者
 [root@localhost ~]# mysql -uroot -p -P3307 -h127.0.0.1  
 [root@localhost ~]# mysql -uroot -p -P3308 -h127.0.0.1
 [root@localhost ~]# mysql -uroot -p -P3309 -h127.0.0.1    

八、修改密码

[root@localhost ~]# mysqladmin -uroot -p password -S /data/3307/mysql3307.sock 
[root@localhost ~]# mysqladmin -uroot -p password -S /data/3308/mysql3308.sock 
[root@localhost ~]# mysqladmin -uroot -p password -S /data/3309/mysql3309.sock