1. 基礎架構介紹
2. 準備環(huán)境
2.1 環(huán)境準備：
兩臺虛擬機 db01 db02
每臺創(chuàng)建四個mysql實例：3307 3308 3309 3310
2.2 刪除歷史環(huán)境：
===================================================================================
執(zhí)行過程中要看好標記，看好＃號后標記得主機名稱；這里的我的主機環(huán)境：db01（eth0：10.0.0.51 eth1: 172.16.1.51） db02（eth0：10.0.0.52 eth1:172.16.1.52）?
===================================================================================
pkill mysqld
rm -rf /data/330*
mv /etc/my.cnf /etc/my.cnf.bak
2.3 創(chuàng)建相關目錄初始化數(shù)據(jù)
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure? --user=mysql --datadir=/data/3307/data --basedir=/data/app/mysql
mysqld --initialize-insecure? --user=mysql --datadir=/data/3308/data --basedir=/data/app/mysql
mysqld --initialize-insecure? --user=mysql --datadir=/data/3309/data --basedir=/data/app/mysql
mysqld --initialize-insecure? --user=mysql --datadir=/data/3310/data --basedir=/data/app/mysql
2.4 準備配置文件和啟動腳本
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/data/app/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
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/data/app/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
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
========db02===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/data/app/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=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/data/app/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=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/etc/systemd/system/mysqld3307.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[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=/data/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
2.5 修改權限，啟動多實例
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
2.7 分片規(guī)劃（不用執(zhí)行，結構介文字說明）
shard1：（節(jié)點①）
? ? Master：10.0.0.51:3307
? ? slave1：10.0.0.51:3309
? ? Standby Master：10.0.0.52:3307
? ? slave2：10.0.0.52:3309
shard2：（節(jié)點②）
? ? Master：10.0.0.52:3308
? ? slave1：10.0.0.52:3310
? ? Standby Master：10.0.0.51:3308
? ? slave2：10.0.0.51:3310
2.8 開始配置主從環(huán)境
# shard1
##? 10.0.0.51:3307 <-----> 10.0.0.52:3307
# db02
mysql? -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql? -S /data/3307/mysql.sock -e "grant all? on *.* to root@'10.0.0.%' identified by '123'? with grant option;"
# db01
mysql? -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3307/mysql.sock -e "start slave;"
mysql? -S /data/3307/mysql.sock -e "show slave status\G"
# db02
mysql? -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3307/mysql.sock -e "start slave;"
mysql? -S /data/3307/mysql.sock -e "show slave status\G"
## 10.0.0.51:3309 ------> 10.0.0.51:3307
# db01
mysql? -S /data/3309/mysql.sock? -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3309/mysql.sock? -e "start slave;"
mysql? -S /data/3309/mysql.sock? -e "show slave status\G"
## 10.0.0.52:3309 ------> 10.0.0.52:3307
# db02
mysql? -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3309/mysql.sock -e "start slave;"
mysql? -S /data/3309/mysql.sock -e "show slave status\G"
====================================================================
# shard2
## 10.0.0.52:3308 <-----> 10.0.0.51:3308
# db01
mysql? -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql? -S /data/3308/mysql.sock -e "grant all? on *.* to root@'10.0.0.%' identified by '123'? with grant option;"
# db02
mysql? -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3308/mysql.sock -e "start slave;"
mysql? -S /data/3308/mysql.sock -e "show slave status\G"
# db01
mysql? -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3308/mysql.sock -e "start slave;"
mysql? -S /data/3308/mysql.sock -e "show slave status\G"
## 10.0.0.52:3310 -----> 10.0.0.52:3308
# db02
mysql? -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3310/mysql.sock -e "start slave;"
mysql? -S /data/3310/mysql.sock -e "show slave status\G"
##10.0.0.51:3310 -----> 10.0.0.51:3308
# db01
mysql? -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql? -S /data/3310/mysql.sock -e "start slave;"
mysql? -S /data/3310/mysql.sock -e "show slave status\G"
2.9 檢測主從狀態(tài)
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Running
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Running
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Running
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Running
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
注：如果中間出現(xiàn)錯誤，在每個節(jié)點進行執(zhí)行以下命令，從2.8從頭執(zhí)行
mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3308/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3310/mysql.sock -e "stop slave; reset slave all;"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
最后結果是：下圖樣子：
