資料庫 怎麼配置多例項
為什麼資料庫要配置多例項呢?
主要的原因是因為資料庫是單程序多執行緒的服務,為了最大可能的利用資源,所以配置多例項
具體的配置方法如下:
我以配置資料庫版本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=SQL/">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,並建立日誌檔案,否則無法啟動。希望對大家有所幫助,筆芯!!!