MySQL InnoDB 主從復制同步實戰總結：從 MyISAM 遷移到 DRBD+Heartbeat 雙主架構

架構演進路線

舊：單主(MyISAM) + 多從  →  新：DRBD+Heartbeat 雙主 + 多從 (InnoDB)

驅動力：InnoDB 事務/行鎖/崩潰恢復 + DRBD 塊級同步高可用。

核心經驗總結（5 大鐵律）

1. 異步復制本質：延遲不可避免，必須監控

• MySQL 複制是**異步/半同步**，非強一致。
• 讀寫分離架構下，**從庫延遲直接導致讀舊數據**。
• 監控指標：Seconds_Behind_Master、pt-heartbeat、Prometheus + Grafana 告警閾值建議 ≤ 1s。

2. server-id 全局唯一，否則報 Fatal Error

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

• 規範：主庫 10、從庫 20、備庫 30... 統一 CMDB 分配，杜絕手工衝突。

3. 從庫不必開啟 Binlog（除非做雙主/級聯）

• 復制原理：Slave IO 線程拉取 Master Binlog → 寫入本地 Relay Log → SQL 線程回放。
• 從庫只需 Relay Log，**關閉 Binlog 可減少磁盤 IO、避免誤寫入**。
• 若規劃雙主/級聯從庫，則需開啟 log_bin + log_slave_updates=ON。

4. 授權最小化：僅 REPLICATION SLAVE/CLIENT

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'StrongPass!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

• 禁用 GRANT OPTION、SUPER、FILE 等高危權限。
• 內網同樣嚴格限制來源 IP 段。

5. 搭建流程標準化，錯誤多半在網絡/權限

• 標準 SOP：備份主庫 → 導入從庫 → 配置 CHANGE MASTER → START SLAVE → 校驗狀態。
• 失敗排查順序：ping/telnet 3306 → SHOW GRANTS → SHOW MASTER STATUS → SHOW SLAVE STATUS\G。

InnoDB 遷移關鍵決策

實戰案例：1062 Duplicate Entry 排查全程

現象

Last_SQL_Error: Error 'Duplicate entry ... for key PRIMARY' 
Query: INSERT INTO lee_sessions ...

根因

• 初期用 mysqldump --single-transaction 導出主庫快照，但在**高併發寫入下**，單事務視圖無法覆蓋全庫一致性時間點，導致備份數據與 Binlog 位點不匹配。
• 從庫回放 Binlog 時，主鍵衝突。

修正方案：--master-data=1 鎖定一致性位點

mysqldump -uroot --quick --flush-logs --master-data=1 -p myproject > myproject.sql
# --master-data=1：輸出文件包含 CHANGE MASTER TO 語句（非註釋）
# 自動隱含 --lock-all-tables（全局讀鎖），確保備份與 Binlog 位點強一致

提取位點並配置

grep "CHANGE MASTER" myproject.sql
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=106;

導入後啟動 Slave，**長期觀察零 1062 錯誤**。

生產級復制運行維護清單

1. GTID 模式強制開啟：gtid_mode=ON, enforce_gtid_consistency=ON，簡化故障切換與拓扑變更。
2. 半同步復制：rpl_semi_sync_master=ON, rpl_semi_sync_slave=ON，關鍵事務至少雙節點落盤。
3. 並行回放：slave_parallel_workers=8, slave_preserve_commit_order=1，降低大事務回放延遲。
4. 定期一致性校驗：每周 pt-table-checksum --recurse=1 --databases=core_db，差異自動 pt-table-sync 修復。
5. 備份驗證演練：月度從備份恢復並驗證業務可用性。

總結

從 MyISAM 單主遷移到 InnoDB DRBD 雙主，**核心收益在事務安全與高可用**。復制層面必須守住「異步本質監控、server-id 唯一、從庫關 Binlog、最小權限、標準化 SOP、GTID+半同步+並行回放」六大基線。遷移過程中用 --master-data=1 替代 --single-transaction 解決一致性備份難題，是本次實戰最大技術亮點。