MySQL 主從復制 Duplicate Key 錯誤：SQL_SLAVE_SKIP_COUNTER 實戰與 Binlog 事件深度解析

問題背景

主從復制中，Slave SQL 線程因主鍵衝突（Duplicate entry '1' for key 'PRIMARY'）中斷。官方文檔稱涉及 AUTO_INCREMENT 或 LAST_INSERT_ID() 的語句需 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2，但實測發現 設置為 1 即可恢復。本文通過構造實驗環境，深度解析 Binlog 事件結構，揭示其中原理。

實驗環境構建

• Master：172.16.161.26，MySQL 5.7，Row 格式 Binlog
• Slave：172.16.161.15，同步庫 c2cdb
• 測試表：tmp_test_0208(id INT AUTO_INCREMENT PK, name VARCHAR(30)) ENGINE=InnoDB

復現步驟與現象觀察

1. 初始化數據差異製造衝突

-- Slave 先插入 3 條指定 ID
INSERT INTO tmp_test_0208 VALUES (1,'a'),(2,'b'),(3,'c');

-- Master 插入 3 條不指定 ID（自增生成 1,2,3）
INSERT INTO tmp_test_0208(name) VALUES ('a'),('b'),('c');

Master 自增生成的 ID 1,2,3 與 Slave 現有數據主鍵衝突，導致 Slave SQL 線程報錯 1062 停止。

2. 錯誤日誌與 Binlog 事件分析

Slave 錯誤日誌：

070208 16:07:59 [ERROR] Slave: Error 'Duplicate entry '1' for key 1' on query.
Default database: 'c2cdb'. Query: 'insert into tmp_test_0208(name) values('a'),('b'),('c')'
Error_code: 1062

Master Binlog 事件（關鍵發現）：

# at 14215101
#070208 16:08:00 server id 1  log_pos 14215101  Intvar SET INSERT_ID=1;
# at 14215129
#070208 16:08:00 server id 1  log_pos 14215129  Query  thread_id=2744782  exec_time=0  error_code=0
SET TIMESTAMP=1170922080; insert into tmp_test_0208(name) values('a'),('b'),('c');

關鍵觀察：單條 INSERT 語句產生了兩個 Binlog 事件——

1. Intvar 事件：設置 INSERT_ID=1（自增值上下文）
2. Query 事件：實際的 INSERT 語句

3. SQL_SLAVE_SKIP_COUNTER=1 即可跳過的原因

執行：

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Slave SQL 線程恢復正常。說明：

• SQL_SLAVE_SKIP_COUNTER 計數的是 Binlog 事件組，而非 SQL 語句條數
• 該 INSERT 語句雖包含 AUTO_INCREMENT，但在 Row 格式下，Intvar 與 Query 事件作為一個原子事務組提交
• 跳過計數器 = 1 即跳過該完整事務組（含 Intvar + Query 兩個事件）

官方文檔與實際行為的差異解讀

文檔稱「使用 AUTO_INCREMENT 需 skip 2」基於 Statement 格式 Binlog：

• Statement 格式下，INSERT_ID 事件與 Query 事件可能分離，需分別跳過
• Row 格式（現代默認）將相關事件封裝在同一事務組，計數器以事務組為單位

最佳實踐建議

標準修復流程

-- 1. 確認錯誤詳情
SHOW SLAVE STATUS\G

-- 2. 分析 Binlog 事件（可選）
mysqlbinlog --start-position=錯誤位點 master-bin.000001 | head -30

-- 3. 若確認可跳過（如數據已存在）
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- 4. 驗證同步恢復
SHOW SLAVE STATUS\G  # 確認 Slave_SQL_Running: Yes

根治方案：避免主鍵衝突

• 採用 auto_increment_offset + auto_increment_increment 實現雙主/多主自增 ID 不衝突
• 使用 UUID 或雪花算法生成全局唯一主鍵
• 啟用 slave_exec_mode=IDEMPOTENT（需評估副作用）
• 監控 Seconds_Behind_Master 與 Last_SQL_Error，設置告警

總結

MySQL 主從復制中 SQL_SLAVE_SKIP_COUNTER 計數單位是 Binlog 事件組（事務組），而非 SQL 語句條數。Row 格式下，包含 AUTO_INCREMENT 的 INSERT 單條語句產生 Intvar + Query 兩個事件，但屬於同一事務組，**設置 1 即可跳過**。理解 Binlog 事件結構，能避免盲目遵循過時文檔導致的過度跳過風險。