mysql一主一从的结构，怎么最小影响添加一个从库，变成一...

43212gg

架构故障

1. 一主一从的结构，怎么最小影响添加一个从库，变成一主两从
场景：分别如下2种
(1)由M--〉S1变为M-->S1-->S2
(2)由M--〉S1变为M-->S1&M-->S2

ip 192.168.0.100 3306
ip 192.168.0.100 3307
ip 192.168.0.100 3308(3308为新添加的从库)


(1)由M--〉S1变为M-->S1-->S2
在从库3307上面备份：(主库压力大,不影响业务情况下，在从库3307继续mysqldump)  --使用参数为--master-data=2
mysqldump --single-transaction --master-data=2 -A -S /tmp/mysql3307.sock -p >3307-full-20170109.sql

--master-data=2# show master status;(s1)

more 3307-full-20170109.sql
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=1793;

传统复制：(注意端口为3307)
CHANGE MASTER TO
MASTER_HOST='192.168.0.100',
MASTER_PORT=3307,
MASTER_USER='rep1',
MASTER_PASSWORD='repl4slave',
MASTER_LOG_FILE='mysql-bin.000011',
MASTER_LOG_POS=1793;

GTID复制：(注意端口为3307)
CHANGE MASTER TO
MASTER_HOST='192.168.0.100',
MASTER_PORT=3307,
MASTER_USER='rep1',
MASTER_PASSWORD='repl4slave',
master_auto_position=1;

(2)由M--〉S1变为M-->S1&M-->S2
备份：在3307上面备份
mysqldump --single-transaction --dump-slave=2 -A -S /tmp/mysql3307.sock -p >3307-full-20170109-xxxxx.sql

--dump-slave=2  # show slave status;(m1)表示在s1上面执行得到m1的bin-log的位置

more 3307-full-20170109-xxxxx.sql
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000047', MASTER_LOG_POS=2198;

传统复制：(注意端口为3306)
CHANGE MASTER TO
MASTER_HOST='192.168.0.100',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='repl4slave',
MASTER_LOG_FILE='mysql-bin.000047',
MASTER_LOG_POS=2198;

GTID复制：(注意端口为3306)
CHANGE MASTER TO
MASTER_HOST='192.168.0.100',
MASTER_PORT=3306,
MASTER_USER='rep1',
MASTER_PASSWORD='repl4slave',
master_auto_position=1;