MySQL 主從數據不一致修復實戰：跳過錯誤事務、Binlog 事件分析、Profiling 定位慢 SQL

MySQL 主從數據不一致修復實戰：跳過錯誤事務、Binlog 事件分析、Profiling 定位慢 SQL

一、問題發現

Slave SHOW SLAVE STATUS\G 顯示 Slave_SQL_Running: No，錯誤為主鍵衝突導致 INSERT 失敗，SQL 線程停止。

二、修復策略對比

三、跳過錯誤事務操作步驟

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳過 1 個事務
START SLAVE;

建議：循環執行並觀察 SHOW SLAVE STATUS\G 中的 Last_SQL_Error，確認每個跳過的事務內容，避免誤跳關鍵業務。若錯誤事務過多，效率較低。

四、精準定位跳過數量：Master Binlog 事件分析

1. 從 Slave 狀態獲取關鍵座標：
   • Master_Log_File（如 mysql-bin.000217）
   • Read_Master_Log_Pos（如 673146776）
2. 在 Master 查看該位置後續事件：

   SHOW BINLOG EVENTS IN 'mysql-bin.000217' FROM 673146776\G;
   -- 或限制筆數
   SHOW BINLOG EVENTS IN 'mysql-bin.000217' FROM 673146776 LIMIT 10;

3. 或用命令列工具：

   mysqlbinlog mysql-bin.000217 --start-position=673146776

分析事件內容，確認需跳過的事務數量，一次性設定 SQL_SLAVE_SKIP_COUNTER=N。

五、恢復後延遲暴增排查：Profiling 定位慢 SQL

啟動 Slave 後 Seconds_Behind_Master 緩慢上升。SHOW PROCESSLIST 發現首條 SQL 狀態 Sending data 執行極久。

Profiling 步驟

SET profiling=1;
-- 執行目標慢查詢
SHOW PROFILES;                    -- 獲取 Query_ID
SHOW PROFILE FOR QUERY ; -- 細分各階段耗時

確認耗時集中在 Sending data 階段（大量磁碟讀取），針對性優化索引或改寫 SQL。

六、踩坑記錄與避雷指南

1. STOP SLAVE 卡住：可能被長 SQL 或鎖阻塞。避免執行 SHOW SLAVE STATUS 等元數據操作加劇爭用。解法：等待鎖釋放或重啟 MySQL（本案選擇重啟）。
2. 重啟後 START SLAVE 報錯：

   ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

   網上多建議重搭主從。實測：改用 mysqld_safe --user=mysql 啟動（而非直接 mysqld）即恢復正常，疑與啟動參數/權限初始化順序有關。

七、參考資源

• Slave SQL 線程鎖住 Bug 分析：penglixun.com
• 主從修復經驗分享：tuicool.com
• Binlog 事件分析技巧：cnblogs.com

八、總結

主從不一致修復核心權衡：**一致性 vs 可用性**。非核心業務優先 SQL_SLAVE_SKIP_COUNTER 秒級恢復，輔以 Binlog 事件分析精準跳過。恢復後必須 Profiling 定位慢 SQL 消除積壓。運維過程中避免在 Slave 執行元數據查詢加劇鎖爭用，啟動建議統一使用 mysqld_safe。