MySQL 主從數據不一致修復全流程：從跳過錯誤到 Binlog 事件分析

MySQL 主從數據不一致修復全流程：從跳過錯誤到 Binlog 事件分析

從庫因主鍵衝突停止同步，如何快速恢復一致性？本文對比三種方案，詳細講解 SQL_SLAVE_SKIP_COUNTER 跳過事務、Binlog 事件定位、長 SQL 診斷與 STOP SLAVE 卡死處理。

故障現場

• SHOW SLAVE STATUS\G 顯示 Slave_SQL_Running: No
• 錯誤：Duplicate entry 'xxx' for key 'PRIMARY'（主鍵衝突）

三種修復策略對比

方案三實操：跳過事務

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

建議：循環執行並觀察 SHOW SLAVE STATUS 中的 Last_SQL_Error，確認每個跳過的事務內容。

進階：通過 Binlog 定位精確跳過數量

1. 從庫獲取：Relay_Master_Log_File、Exec_Master_Log_Pos
2. 主庫分析：SHOW BINLOG EVENTS IN 'mysql-bin.000217' FROM 673146776 LIMIT 20;
3. 或用 mysqlbinlog：mysqlbinlog mysql-bin.000217 --start-position=673146776
4. 統計連續報錯事務數，設定 SQL_SLAVE_SKIP_COUNTER = N

同步恢復後的長 SQL 診斷

同步啟動後 Seconds_Behind_Master 緩慢上升，SHOW PROCESSLIST 見 State: Sending data。
開啟 Profiling 定位：

SET profiling=1;
-- 等待問題 SQL 執行
SHOW PROFILES;
SHOW PROFILE FOR QUERY ;

確認耗時在 Sending data 階段，通常因大量磁盤隨機讀。

坑點總結

1. STOP SLAVE 卡死：常因從庫有長事務或鎖等待。解決：等待或重啟從庫（用 mysqld_safe --user=mysql）。
2. START SLAVE 報錯 1872：Slave failed to initialize relay log info。重啟從庫多試幾次，或檢查 relay_log_info_repository=TABLE 衝突。
3. 跳過事務不可逆：生產環境務必先在備庫驗證跳過內容無業務影響。

最佳實踐建議

• 監控 Seconds_Behind_Master、Slave_*_Running、Last_SQL_Error
• 啟用 binlog_format=ROW 減少非確定性函數導致的不一致
• 定期校驗：pt-table-checksum + pt-table-sync
• 重要業務啟用 GTID 複制（5.6+），故障恢復更簡單