MySQL 主從復制全流程部署指南：Windows 環境手把手實戰、故障切換與日常運維

架構原理

MySQL Replication（3.23.15+）基於 **二進制日志** 異步複製：

1. Master 寫入 Binlog（事務提交前）
2. Slave I/O 線程連接 Master，請求 Binlog 事件
3. Master Binlog Dump 線程發送事件
4. Slave I/O 線程寫入本地 Relay Log
5. Slave SQL 線程回放 Relay Log，重放事務

支持 **一主多從**、**級聯復制**（Slave 再做 Master）、**讀寫分離** 架構。

Windows 雙機部署實戰

環境

Master 配置

1. my.ini [mysqld] 區塊：

   log-bin="D:/MYSQLDataBase/binlog"
   expire_logs_days=10
   max_binlog_size=100M
   server-id=1
   binlog-do-db=test
   binlog-ignore-db=mysql

2. 重啟服務，創建復制賬號：

   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
   FLUSH PRIVILEGES;

3. 查看狀態記錄 File/Position：

   SHOW MASTER STATUS;
   -- File: binlog.000004, Position: 107

4. 導出數據：

   mysqldump -uroot -p -h127.0.0.1 test > D:\TEST.TXT

Slave 配置

1. 導入數據：USE test; SOURCE D:\TEST.TXT;
2. my.ini：

   [mysqld]
   server-id=2
   # log-bin 等可選，若啟用需放在 [mysqld] 非 [mysql]

3. 重啟服務，配置同步：

   STOP SLAVE;
   CHANGE MASTER TO
     MASTER_HOST='192.168.1.100',
     MASTER_USER='repl',
     MASTER_PASSWORD='123',
     MASTER_LOG_FILE='binlog.000004',
     MASTER_LOG_POS=107;
   START SLAVE;

4. 驗證：

   SHOW SLAVE STATUS\G
   -- Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0

常見坑點

• MASTER_LOG_FILE 寫錯（中文句號）
• Slave 误在 [mysql] 區塊配 log-bin
• 防火牆/安全組阻擋 3306
• Master 賬號無 @'%' 遠程權限

主從切換（手動故障轉移）

1. 確認 Slave 已回放完所有 Relay Log：
   STOP SLAVE IO_THREAD; → SHOW PROCESSLIST 見 Slave has read all relay log
2. Slave 停止復制、重置為 Master：
   STOP SLAVE; RESET MASTER;（需先啟用 log-bin，否則報錯）
3. 新 Master 創建復制賬號、查看 SHOW MASTER STATUS
4. 原 Master 改為 Slave：修改 server-id、執行 CHANGE MASTER TO ... 指向新 Master、START SLAVE
5. 驗證雙向同步正常

生產環境**強烈建議使用自動化工具**（MHA、Orchestrator、GTID+自動故障轉移腳本），手動切換僅供演練/應急。

關鍵啟動參數

監控與運維

• SHOW SLAVE STATUS\G 核心指標：Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master
• SHOW PROCESSLIST 觀察 I/O、SQL 線程 State
• 延遲告警：Seconds_Behind_Master > 300 觸發
• 錯誤碼 1236（Binlog 找不到）：檢查 Master Binlog 是否被 purge、網絡抖動
• 錯誤碼 2013（連接斷開）：檢查防火牆、max_allowed_packet 雙邊一致

不同步常見原因

1. 網絡延遲/抖動（首因）
2. Master 大事務/長 SQL 阻塞 Dump 線程
3. Slave 單線程回放慢（5.6+ 可用並行復制 slave_parallel_workers）
4. max_allowed_packet 不一致導致大包傳輸失敗

總結

MySQL 主從復制是「高可用、讀寫分離、異地備份」的基石。**GTID 模式（5.6+）大幅簡化故障轉移**，建議新部署直接啟用 GTID。運維核心：**監控延遲、定期演練切換、保護 Binlog 完整性**。