MySQL 主主循環同步 (Master-Master Replication) 完整實施指南

MySQL 5.1 雙主熱備架構搭建全過程：配置、驗證、故障處理與自增衝突解決

主主同步（雙主複製）提供寫入高可用，但引入自增衝突、數據一致性風險。本文記錄基於 MySQL 5.1.21 的生產級雙主部署全流程，含關鍵參數解析、驗證步驟、測試用例及已知問題。

1. 環境規劃

同步數據庫：test。MySQL 版本：5.1.21。

2. 授權複製用戶

在 A 上：

GRANT REPLICATION SLAVE, FILE ON *.* TO 'repl1'@'192.168.0.232' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

在 B 上：

GRANT REPLICATION SLAVE, FILE ON *.* TO 'repl2'@'192.168.0.231' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

執行後停止兩台 MySQL 服務。

3. 核心配置文件 (my.cnf)

Master A

[mysqld]
user = mysql
log-bin = mysql-bin
server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates = 1
slave-skip-errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 1

Master B

[mysqld]
user = mysql
log-bin = mysql-bin
server-id = 2
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates = 1
slave-skip-errors = all
sync_binlog = 1
auto_increment_increment = 2
auto_increment_offset = 2

關鍵參數解析

• log-slave-updates：從庫收到的變更寫入自己的 binlog，實現級聯複製，雙主必須開啟。
• sync_binlog=1：每次提交同步 binlog 到磁盤，保證故障時數據不丟失（性能有損耗）。
• auto_increment_increment=2, offset=1/2：核心防衝突機制。A 生成奇數 ID (1,3,5...)，B 生成偶數 ID (2,4,6...)，從根本避免自增主鍵衝突。
• slave-skip-errors=all：複製報錯時跳過（生產環境慎用，可能掩蓋數據不一致）。

4. 重啟服務並獲取同步坐標

/usr/local/mysql/bin/mysqld_safe &

在 A 上：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G
-- 記錄 File: mysql-bin.000007, Position: 528

在 B 上：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G
-- 記錄 File: mysql-bin.000004, Position: 595

此時備份數據確保兩庫一致（可用 mysqldump、xtrabackup 等）。

5. 配置互為主從

在 A 上指向 B：

CHANGE MASTER TO
  MASTER_HOST='192.168.0.232',
  MASTER_USER='repl2',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=595;
START SLAVE;

在 B 上指向 A：

CHANGE MASTER TO
  MASTER_HOST='192.168.0.231',
  MASTER_USER='repl1',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=528;
START SLAVE;

6. 驗證複製線程狀態

執行 SHOW PROCESSLIST\G，關鍵檢查點：

• 存在 Command: Binlog Dump 線程（對方作為從庫連接我）
• 存在 State: Waiting for master to send event 的 system user 線程（我作為從庫的 I/O 線程）
• 存在 State: Has read all relay log... 的 SQL 線程（應用中繼日志）

若缺少上述紅色狀態，查看 data 目錄下 .err 錯誤日志排查。

7. 釋放鎖並測試雙向同步

UNLOCK TABLES;

測試步驟：

1. A 建表 t11_replicas，插入一行 → B 可見
2. B 插入第二行 → A 可見
3. 雙向 DDL/DML 均同步成功

-- A 執行
CREATE TABLE t11_replicas (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, str VARCHAR(255) NOT NULL) ENGINE=MYISAM;
INSERT INTO t11_replicas(str) VALUES('This is a master to master test table');

-- B 執行
INSERT INTO t11_replicas(str) VALUES('This is a test 2');

-- 雙方 SELECT 均返回 2 行，ID 分別為 1(A) 和 2(B)，無衝突！

8. 已知問題與注意事項

1. 自增衝突已由 offset/increment 解決，但涉及複合主鍵、觸發器生成 ID 等場景仍需業務層保障。
2. DDL 同步風險：大表 ALTER TABLE 會長時間鎖主庫，導致從庫延遲甚至阻塞。建議使用 pt-online-schema-change 或類似工具。
3. slave-skip-errors=all 危險：生產環境建議改為具體錯誤碼（如 1062 重複鍵），並建立監控告警。
4. 無自動故障切換：需配合 MHA、Orchestrator、ProxySQL 等中間件實現高可用。
5. 跨機房延遲：若部署在不同 IDC，需評估網絡延遲對同步一致性的影響。

總結

雙主架構通過 auto_increment_increment/offset 解決主鍵衝突，log-slave-updates 實現雙向傳播，sync_binlog=1 保證持久化。但運維複雜度高，現代架構更推薦「單主多從 + 自動故障切換」或「Galera Cluster / InnoDB Cluster」等同步多主方案。若必須用雙主，請嚴格遵循本文配置，並建立完善的監控、備份、演練體系。