MySQL Master-Slave 無痛新增 Slave2 指到 Master

MySQL Master-Slave-Slave2 的架構，Slave 2 是作備份用，但是機器老舊需要搬家 換新機，要如何無痛切換？

簡單說就是下述步驟：

1. 要從 Slave 1 新增更下面一組 Slave 2
2. 將 Slave 2 無痛指定到 Master
3. 最後在拔除 Slave 1

MySQL Master-Slave 無痛新增 Slave2 指到 Master

環境

• MasterMachine
• Salve1Machine
• Salve2Machine
• 資料放在 /home/mysql/data57
• 備份放在 /home/mysql/backup

MasterMachine 將 MySQL Master 設定到 Slave2Machine

1. ssh MasterMachine
2. scp -r Slave1Machine:backup . # 從 Slave 1 先取得備份的資料庫
3. cd /home/mysql
4. mv backup data57
5. cat xtrabackup_binlog_info
   mysql-bin.001001 1041186441
6. docker ps # 找出 docker name (mysql-docker)
7. docker exec -it mysql-docker bash
8. mysql -u root -p
9. mysql> STOP slave;
10. mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.001001',
    MASTER_LOG_POS=1041186441;
11. mysql> START slave;

同步後，要設定到 MasterMachine Master

1. ssh slave 1
2. slave 1 mysql> stop slave;
3. slave 1 mysql> show slave status \G # Master_Log_File(mysql-bin.010002), Read_Master_Log_Pos(31774316)
4. ssh slave 2
5. slave 2 mysql> SHOW SLAVE STATUS\G # 確認 Seconds_Behind_Master 為 0
6. slave 2 mysql> stop slave;
7. slave 2 mysql> CHANGE MASTER TO
   MASTER_HOST='MasterMachine',
   MASTER_PORT=3306,
   MASTER_USER='repl',
   MASTER_PASSWORD='您的同步密碼',
   MASTER_LOG_FILE='mysql-bin.010002',
   MASTER_LOG_POS=31774316;
8. slave 2 mysql> start slave;
9. slave 1 mysql> start slave;