MySQL 雙主熱備（Master-Master）搭建實戰：配置、同步驗證、衝突避免

MySQL 雙主複製架構部署全流程：互為主從、雙寫衝突防範、故障切換

雙主（Master-Master）實現雙向同步，提供寫入高可用。核心難點：自增 ID 衝突、雙寫衝突、同步延遲監控。

一、架構拓撲與規劃

Server A (10.16.0.246) ←→ Server B (10.16.0.247)
  Master/Slave              Master/Slave
  auto_increment_offset=1   auto_increment_offset=2
  auto_increment_increment=2

二、雙主配置文件

Server A /etc/my.cnf

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = cdn

# 雙主防衝突核心參數
auto_increment_offset = 1
auto_increment_increment = 2

# 從庫參數（作為 B 的從庫）
relay_log = relay-bin
log_slave_updates = 1
read_only = 0  -- 雙主雙寫需開啟，單寫可設 1

# 超時/重試
slave_net_timeout = 60
master_connect_retry = 60

Server B /etc/my.cnf

[mysqld]
server_id = 2
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = cdn

auto_increment_offset = 2
auto_increment_increment = 2

relay_log = relay-bin
log_slave_updates = 1
read_only = 0

slave_net_timeout = 60
master_connect_retry = 60

三、同步用戶創建

-- Server A
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.16.0.247' IDENTIFIED BY 'strong_pass';
FLUSH PRIVILEGES;

-- Server B
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.16.0.246' IDENTIFIED BY 'strong_pass';
FLUSH PRIVILEGES;

四、初始化同步步驟

1. 停雙主服務，備份 A 數據目錄，恢復到 B（或用 xtrabackup 熱備份）
2. 啟動 A，記錄 SHOW MASTER STATUS\G：File、Position
3. 啟動 B，配置指向 A：

-- Server B
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='10.16.0.246',
    MASTER_USER='repl',
    MASTER_PASSWORD='strong_pass',
    MASTER_LOG_FILE='mysql-bin.000008',
    MASTER_LOG_POS=106;
START SLAVE;

4. B 追上後，記錄 B 的 SHOW MASTER STATUS
5. A 配置指向 B：

-- Server A
STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='10.16.0.247',
    MASTER_USER='repl',
    MASTER_PASSWORD='strong_pass',
    MASTER_LOG_FILE='mysql-bin.000008',
    MASTER_LOG_POS=106;
START SLAVE;

五、驗證同步狀態

SHOW SLAVE STATUS\G

-- 關鍵指標必須為 Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0  -- 延遲秒數，生產環境監控告警閾值建議 > 10s

-- 詳細檢查
Last_IO_Error: 
Last_SQL_Error: 
Exec_Master_Log_Pos: 106
Relay_Log_Space: 256

六、雙寫衝突防範策略

七、監控與運維

• 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running
• 定期 pt-table-checksum 數據一致性校驗
• 故障切換：應用層 VIP/Proxy（MHA、Orchestrator、ProxySQL）自動切換
• 備份：從庫做備份不影響主庫，建議從 B 備份

雙主非銀彈：引入雙寫衝突風險、同步延遲、運維複雜度。優先考慮：單主多從 + MHA/Orchestrator 自動故障切換。雙主僅適用於：跨機房雙活、業務可明確分片、極高寫入可用性要求且接受最終一致性。