MySQL 主從復制原理、Windows 搭建實戰與故障切換全流程

MySQL 主從復制原理、Windows 搭建實戰與故障切換全流程

復制核心原理（三階段）

1. Master 將事務寫入 Binlog（提交前）
2. Slave I/O 線程連接 Master，請求 Binlog 事件，寫入本地 Relay Log
3. Slave SQL 線程讀取 Relay Log 重放，實現數據同步

復制為異步，Slave 無需持續連線；支持一主多從、鏈式復制（需開啟 log-slave-updates）。

Windows 環境搭建步驟（5.5/5.6 通用）

環境準備

Master 配置 (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

• 重啟後 SHOW VARIABLES LIKE 'log_bin' 確認 ON
• 創建復制賬號：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
• SHOW MASTER STATUS; 記錄 File/Position

數據初始化

# Master 導出
mysqldump -uroot -p -h127.0.0.1 test > D:\TEST.TXT
# Slave 導入
mysql -uroot -p
USE test;
SOURCE D:\TEST.TXT;

Slave 配置 (my.ini)

[mysqld]
server-id=2
# 不配置 log-bin，避免誤放 [mysql] 節點

啟動復制

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_LOG_FILE='binlog.000005',
  MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G;

關鍵指標：Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0。

常見啟動參數

• log-slave-updates：鏈式復制必開
• master-connect-retry=60：重連間隔秒數
• read-only：限制普通用戶寫入從庫
• slave-skip-errors=1007,1051,1062：跳過指定錯誤（僅非核心從庫）

監控復制延遲

SHOW PROCESSLIST\G;
-- Slave_SQL_Running 線程 Time 欄位 = 延遲秒數

日常維護與故障處理

常見報錯

• max_allowed_packet 過小：主從同步調大至 16M+
• 多主 auto_increment 衝突：設置 auto_increment_increment/offset 錯開

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

1. Slave 執行 STOP SLAVE IO_THREAD 待 Has read all relay log
2. STOP SLAVE; RESET MASTER; 升級為新 Master
3. 新 Master 創建復制賬號，調整 server-id
4. 原 Master 配置 CHANGE MASTER TO 指向新 Master，START SLAVE
5. 驗證雙向同步

生產建議

• 使用 InnoDB + sync_binlog=1 保證數據零丟失
• 主從網絡放同一 VPC，減少延遲
• 生產環境採用 MHA/Orchestrator/Gtid 自動故障轉移，避免手動操作