MySQL 主從同步故障排查：server-id 衝突導致大量 TIME_WAIT 與 I/O 線程異常重連

MySQL 複製故障實戰：server-id 重複引發的連線風暴與同步中斷

新增從庫後，主從雙雙出現大量 TIME_WAIT 連線，從庫錯誤日誌反覆報錯：

[Note] Slave: received end packet from server, apparent master shutdown:
[Note] Slave I/O thread: Failed reading log event, reconnecting to retry...
[Note] Slave I/O thread killed while reading event

根因定位：server-id 唯一性違反

檢查發現兩個從庫配置了相同的 server-id。MySQL 複製協議要求叢集內每個節點 server-id 必須全域唯一 (1~2^32-1)。重複導致：

1. 主庫無法區分兩個從庫，Binlog 傳送邏輯混亂
2. 從庫 I/O 線程頻繁建立/斷開連線 → 大量 TIME_WAIT 佔用埠資源
3. Relay Log 寫入衝突，SQL 線程無法正常重放

修復步驟

# 1. 修改從庫 my.cnf
[mysqld]
server-id = 102  # 必須唯一，建議規劃：1=主, 101/102=從, 201/202=從的從

# 2. 重啟 MySQL 服務
systemctl restart mysqld

# 3. 重新建立複製 (如需)
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='...', MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
START SLAVE;

最佳實踐：server-id 規劃與自動化

自動化生成腳本 (Ansible/Shell 片段)

# 根據 IP 最後段自動生成唯一 server-id
LAST_OCTET=$(ip -4 addr show eth0 | grep -oP '(?<=inet\s)\d+(\.\d+){3}' | cut -d. -f4)
SERVER_ID=$((100 + LAST_OCTET))  # 從庫基礎 100
sed -i "s/^server-id.*/server-id = $SERVER_ID/" /etc/my.cnf

延伸排查：為何錯誤訊息誤導性強？

錯誤 apparent master shutdown 實為I/O 線程讀取 Binlog 時連線被主動斷開 (因 server-id 衝突主庫拒絕服務)，而非主庫真正關機。MySQL 錯誤碼未覆蓋此邊緣場景，建議：

• 監控 Slave_IO_Running + Slave_SQL_Running 雙重指標
• 部署 pt-heartbeat 或 orchestrator 實時檢測複製拓撲異常
• 配置 log_slave_updates=ON 確保鏈路完整性

生產環境檢查清單

1. ✅ 所有節點 server-id 唯一且已規劃
2. ✅ auto_increment_increment/offset 配合雙主/多主避免主鍵衝突
3. ✅ 複製用戶權限最小化 (REPLICATION SLAVE)
4. ✅ 半同步複製 rpl_semi_sync_master=ON
5. ✅ GTID 模式 gtid_mode=ON + enforce_gtid_consistency=ON
6. ✅ 監覽 Seconds_Behind_Master、Slave_*_Running、Last_IO_Error