MySQL 主從複製常見異常與解決方案：中繼日志、同步失敗、IO 線程、主鍵衝突、延遲、Change Master 報錯

MySQL 主從架構在生產環境中長期運行，必然遭遇各類異常。本文針對六大高頻故障——中繼日志暴增、同步報錯、IO 線程卡 Connecting、主鍵衝突 1062、從庫延遲、Change Master 報 1201——給出成因分析、緊急處理與根治配置。

故障一：中繼日志不斷增長，磁盤爆滿

現象

從庫 Relay_Log_Space 持續增長，未自動清理舊中繼日志。

根因

默認 relay_log_purge=ON，但 SQL 線程處理緩慢或 IO 線程拉取過快，導致清理來不及；或參數被誤關閉。

解決

# 1. 確認參數開啟
SHOW VARIABLES LIKE 'relay_log_purge';  -- 必須為 ON

# 2. 臨時手動清理（謹慎）
PURGE RELAY LOGS TO 'relay-bin.000123';  -- 清理到指定文件前

# 3. 根治：限制中繼日志總大小（MySQL 5.7+）
[mysqld]
relay_log_space_limit = 10G  # 超過限制 IO 線程暫停，倒逼 SQL 線程消費

故障二：主從同步報錯，需快速恢復同步

緊急跳過錯誤（數據可能不一致，謹慎用）

# 跳過 1 個事件（到事務結束）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

# 跳過 N 個事件
SET GLOBAL sql_slave_skip_counter = N;

永久忽略特定錯誤碼

[mysqld]
slave-skip-errors = 1062,1032,1007  # 常見：主鍵衝突、記錄不存在、庫已存在
# 或忽略所有（極度危險，僅極端場景）
# slave-skip-errors = all

正規軍方案：重做從庫

1. 主庫：FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; 記錄 File/Position。
2. 主庫：mysqldump --single-transaction --master-data=2 -A > full.sql。
3. 從庫：導入全量 → CHANGE MASTER TO ... → START SLAVE。

故障三：IO 線程長期 Connecting

四大排查清單

故障四：主鍵衝突報錯 1062

成因

Statement 格式 Binlog 下，非確定性函數（UUID()、NOW()）、自增鎖模式導致從庫重放時主鍵衝突。

根治配置

[mysqld]
# 1. 行級/混合 Binlog 格式（推薦）
binlog_format = ROW  # 或 MIXED

# 2. 自增鎖模式調整（配合 ROW 格式）
innodb_autoinc_lock_mode = 2  # 交換模式，高併發插入性能最佳

故障五：從庫同步延遲高

五大排查維度

1. 系統時間不同步：ntpdate pool.ntp.org 確保主從時鐘一致，Seconds_Behind_Master 才準確。
2. 從庫資源瓶頸：top/iostat/sar 檢查 CPU、IO、內存、網絡；MyISAM 表鎖導致讀寫阻塞 → 全面遷移 InnoDB。
3. 大事務/長 SQL 阻塞：SHOW PROCESSLIST 找長事務；主庫拆分大事務、避免大表 DDL。
4. 單線程 SQL 瓶頸：MySQL 5.7+ 開啟 slave_parallel_workers > 0、slave_parallel_type = LOGICAL_CLOCK 並行複製。
5. 實例過多：單實例承載過多庫 → 拆分實例、減少從庫負載。

故障六：CHANGE MASTER TO 報錯 ERROR 1201 (HY000)

現象

ERROR 1201 (HY000): Could not initialize master info structure

原因

從庫已存在舊的主從元數據（master.info/relay-log.info），直接 CHANGE MASTER 衝突。

解決

STOP SLAVE;
RESET SLAVE ALL;  -- MySQL 5.7.6+ 清空所有主從元數據
# 或舊版
RESET SLAVE;
# 再執行 CHANGE MASTER TO ...

總結：主從運維黃金法則

• 格式統一 ROW：消除 90% 主鍵衝突與非確定性風險。
• 並行複製開啟：slave_parallel_workers=4 起步，按核心數調整。
• 監控三件套：Seconds_Behind_Master、Relay_Log_Space、Slave_SQL_Running_State 納入告警。
• 定期演練重做：季度一次從庫重建演練，驗證備份可用性與 RTO/RPO。
• 嚴禁手工寫從庫：從庫唯讀、無獨立寫入，防數據分叉。