数据库 怎么配置多实例
为什么数据库要配置多实例呢？
主要的原因是因为数据库是单进程多线程的服务，为了最大可能的利用资源，所以配置多实例
具体的配置方法如下：
我以配置数据库版本5.7.20的为例，5.6的版本数据库配置基本流程一样，只是在启动的时候略有不同5.6的版本可以利用/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &来进行启动。
第一步：生成多实例的目录
mkdir /data/330{7..9}/data -p
第二步：准备多个配置文件
[root@mysql02 local]# vim /data/3307/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
[root@mysql02 local]# vim /data/3308/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
port=3308
log_bin=/data/3308/mysql-bin
log-error=/data/3308/mysql.log
binlog_format=row
skip-name-resolve
server-id=8
[root@mysql02 local]# vim /data/3309/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
第三步：初始化三套数据：
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data
第四步：创建日志文件，并修改权限：
touch /data/330{7..9}/mysql.log
chown -R mysql.mysql /data/330*
第五步：配置systemctl的启动脚本文件
[root@mysql02 local]# 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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
[root@mysql02 local]# 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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
[root@mysql02 local]# 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=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
第六步：加入开机自启动，并启动mysql多实例的服务
systemctl enable mysql3307.service
systemctl enable mysql3308.service
systemctl enable mysql3309.service
systemctl start mysql3307
systemctl start mysql3308
systemctl start mysql3309
systemctl status mysql3307
systemctl status mysql3308
systemctl status mysql3309
第七步：查看端口
[root@mysql02 local]# ss -luntp|grep 33
tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=25300,fd=15))
tcp LISTEN 0 80 :::3307 :::* users:(("mysqld",pid=30400,fd=22))
tcp LISTEN 0 80 :::3308 :::* users:(("mysqld",pid=30435,fd=22))
tcp LISTEN 0 80 :::3309 :::* users:(("mysqld",pid=30470,fd=22))
第八步：测试
[root@mysql02 local]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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>
配置一定要注意将权限全部修改为mysql，并创建日志文件，否则无法启动。希望对大家有所帮助，笔芯！！！
