MyCAT基础架构准备
节点主从规划
箭头指向谁是主库
10.0.0.12:3307 <-----> 10.0.0.13:3307(主主复制)
10.0.0.12:3309 ------> 10.0.0.12:3307(主从复制)
10.0.0.13:3309 ------> 10.0.0.13:3307(主从复制)
10.0.0.13:3308 <-----> 10.0.0.13:3308 (主主复制)
10.0.0.13:3310 -----> 10.0.0.52:3308 (主从复制)
10.0.0.12:3310 -----> 10.0.0.13:3308 (主从复制)
分片规划
shard1：
Master：10.0.0.12:3307
slave1：10.0.0.12:3309
Standby Master：10.0.0.13:3307
slave2：10.0.0.13:3309
shard2：
Master：10.0.0.13:3308
slave1：10.0.0.13:3310
Standby Master：10.0.0.12:3308
slave2：10.0.0.12:3310
开始配置
第一组四节点结构
# 10.0.0.12:3307 <-----> 10.0.0.13:3307
## db03:
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;"
## db02:
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.13', 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"
## db03:
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.12', 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.12:3309 ------> 10.0.0.12:3307
## db02:
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.12', 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.13:3309 ------> 10.0.0.13:3307
## db03:
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.13', 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.13:3308 <-----> 10.0.0.12:3308
## db02:
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;"
## db03:
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.12', 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"
## db02:
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.13', 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.13:3310 -----> 10.0.0.13:3308
## db03:
mysql -S /data/33010/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.13', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/33010/mysql.sock -e "start slave;"
mysql -S /data/33010/mysql.sock -e "show slave status\G"
# 10.0.0.12:3310 -----> 10.0.0.12:3308
## db02:
mysql -S /data/33010/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.12', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/33010/mysql.sock -e "start slave;"
mysql -S /data/33010/mysql.sock -e "show slave status\G"
检测主从状态
[root@db03 ~]# for i in {7..10};do mysql -S /data/330${i}/mysql.sock -e "show slave status\G"|grep Yes;done
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#注：如果中间出现错误，在每个节点进行执行以下命令,从第1.9步重新开始即可
[root@db03 ~]# for i in {7..10};do mysql -S /data/330${i}/mysql.sock -e "stop slave; reset slave all;"|grep Yes;done
