MySQL 主主雙向複製 (Master-Master Replication) 搭建實戰與避坑指南

前言

MySQL 主主複製 (雙向同步) 允許兩個節點互為主從，**雙寫**同步，常用於雙活架構、異地災備、讀寫分離主備切換。但**雙寫衝突風險極高** (自增 ID 衝突、唯一鍵衝突、事務隔離級別差異)。本文詳細記錄基於 Binlog (Position/GITD) 的搭建步驟、關鍵參數配置、衝突預防策略、監控運維及現代替代方案。

一、 架構與核心參數規劃

二、 核心配置文件 (my.cnf) - 關鍵差異化參數

Node A (192.168.0.1)

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin
binlog-format = ROW          # 強烈推薦 ROW 格式，減少衝突、支持幂等
binlog-do-db = business_db   # 只同步業務庫
binlog-ignore-db = mysql,sys,performance_schema,information_schema

# === 雙主防衝突核心 ===
auto_increment_increment = 2  # 自增步長
auto_increment_offset = 1     # 起始偏移 (生成 1,3,5,7...)

# 從庫相關 (作為 B 的從庫)
relay-log = /var/log/mysql/relay-bin
log-slave-updates = 1         # **關鍵**！將從庫執行的事件寫入自己的 Binlog，傳遞給下游
read-only = 0                 # 雙主雙寫需開寫 (但應用層路由控制)
# slave-skip-errors = 1062    # 危險！生產禁用，掩蓋衝突

# GTID 模式 (5.6+ 推薦)
gtid_mode = ON
enforce_gtid_consistency = ON

Node B (192.168.0.2) - 差異行高亮

server-id = 2
auto_increment_offset = 2     # 生成 2,4,6,8... **與 A 錯開**
# 其他參數同 A (log_bin 路徑、binlog_do_db 等)

三、 搭建步驟 (基於 GTID 模式，5.6+ 標準流程)

1. 準備雙向同步賬號

-- Node A 執行 (授權給 B 連接)
CREATE USER 'repl'@'192.168.0.2' IDENTIFIED BY 'ReplPass#2024!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';

-- Node B 執行 (授權給 A 連接)
CREATE USER 'repl'@'192.168.0.1' IDENTIFIED BY 'ReplPass#2024!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.1';

2. 獲取同步起點 (GTID 模式無需手動獲取 File/Position)

若為全新實例或已同步一致，直接配置源即可。若有既有數據差異，需先全量同步 (XtraBackup/Clone Plugin) 確保數據一致再建立複製。

3. 配置複製源並啟動

-- Node A (作為 B 的從庫)
CHANGE MASTER TO
    MASTER_HOST = '192.168.0.2',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplPass#2024!',
    MASTER_AUTO_POSITION = 1;  -- GTID 模式核心參數
START SLAVE;

-- Node B (作為 A 的從庫)
CHANGE MASTER TO
    MASTER_HOST = '192.168.0.1',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplPass#2024!',
    MASTER_AUTO_POSITION = 1;
START SLAVE;

4. 驗證狀態

SHOW SLAVE STATUS\G
-- 關鍵指標均為 Yes:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
-- GTID 模式下:
Retrieved_Gtid_Set: xxxx
Executed_Gtid_Set: xxxx
Auto_Position: 1

四、 雙寫衝突預防：生存法則

五、 監控與運維關鍵指標

-- 1. 延遲監控 (秒)
Seconds_Behind_Master

-- 2. GTID 集合一致性
SELECT @@GLOBAL.GTID_EXECUTED; -- 兩節點對比

-- 3. 錯誤檢測
Last_IO_Error, Last_SQL_Error

-- 4. Relay Log 空間
Relay_Log_Space

-- 5. 自動化巡檢腳本 (Prometheus + mysqld_exporter 告警規則)
- alert: MySQLReplicationLag
  expr: mysql_slave_status_seconds_behind_master > 30
- alert: MySQLReplicationDown
  expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0

六、 常見故障處理

1. SQL 線程報錯 1062 (Duplicate Entry)：
   • 原因：雙寫衝突或手動操作導致。
   • 處理：嚴禁 slave_skip_errors=1062。需對比數據、修復不一致、重做同步 (或 GTID 注入空事務跳過)。
2. IO 線程斷開 (連接超時/網絡抖動)：
   • 檢查防火牆、網絡、slave_net_timeout (默認 60s，建議 10-30s)、master_retry_count。
   • GTID 模式自動斷點續傳，無需手動干預 File/Position。
3. Binlog 文件丟失 (Master 清理過快)：
   • 增大 expire_logs_days (如 7-14 天) 或 binlog_expire_logs_seconds (8.0)。
   • 從庫需 relay_log_purge=1 (默認) 自動清理已應用 Relay Log。

七、 現代替代方案：為什麼生產環境少用雙主？

八、 總結

MySQL 雙主複製**技術上可行、工程上危險**。核心在於 auto_increment_offset/increment 錯開自增 ID、**應用層強制分片路由避免雙寫衝突**、binlog_format=ROW + log_slave_updates=1 保證傳遞鏈路完整、**GTID 模式簡化運維**。**強烈建議核心業務遷移至 MGR (InnoDB Cluster) 或 Galera Cluster 獲得原生多主強一致保障**。若必須用雙主，請建立完善的衝突監控、數據一致性校驗 (pt-table-checksum) 和應急預案。

1. 上一篇文章： MySQL InnoDB 調大 innodb_log_file_size 導致啟動失敗：原理、正確變更流程與避坑指南
2. 下一篇文章： MySQL 核心參數調優全攻略：五大類別三十餘關鍵參數系統化配置指南

MySQL 資料庫相關知識

在 Intel Edison 打造全球最小 MySQL 伺服器：物聯網邊緣計算實戰

前言 Intel Edison（22nm Silvermon

Win7 下 MySQL 5.7 安裝配置圖文教程（含 Workbench 連接驗證）

下載與安裝包選擇 官網下載 mysql-installer-

MySQL 入門系列：如何選擇最適合的 MySQL 欄位型別

型別選擇的五大決策因素 雖然任何資料皆可存為字串，但型別選擇

MSSQL 2000 本地企業管理器遠程註冊與連線伺服器

環境 Windows XP，SQL Server 2000