MySQL 雙向主從同步設定：互為主從的循環複寫架構與風險提示

在單向主從基礎上，建立 雙向主從 (Circular Replication)：A → B、B → A，實現雙寫入口。本文記錄具體設定步驟與關鍵風險提示。

架構圖

Server A (Master) ──binlog──→ Server B (Slave)
     ↑                              │
     │                              ↓
     ←────── binlog ──────── Server B (Master)

設定步驟

1. Server B 啟用 binlog 並建立同步帳號

# my.cnf [mysqld]
log-bin
server-id = 2

# SQL
GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'10.1.1.1' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

2. Server A 設定指向 B

# my.cnf [mysqld]
master-host = 10.1.1.2
master-user = replicate
master-password = password
server-id = 1
log-bin  # 建議也開啟，便於 B 做 Slave 時有 binlog

3. 重啟雙方服務，啟動同步

# 兩台均執行
START SLAVE;
SHOW SLAVE STATUS\G
# 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

⚠️ 關鍵風險與限制 (必讀)

1. 自增衝突：雙邊同時 INSERT 會產生相同主鍵
   • 解決：auto_increment_increment=2 + auto_increment_offset=1/2 (A 用 1, B 用 2)
2. 循環複寫風暴：A 寫入 → 複寫到 B → B 再複寫回 A → 無限循環
   • MySQL 透過 server_id 在 binlog 標記來源，遇到自己的 server_id 會忽略，單層循環安全
   • 但若拓撲超過 2 節點 (A→B→C→A)，需 log_slave_updates=ON 並小心設計
3. 衝突解決無內建機制：同一列雙邊同時 UPDATE，以「後複寫者覆蓋」為準，可能造成資料不一致
   • 應用層避免雙寫同一筆資料，或用 pt-table-sync 定期校驗修復
4. 負載均衡不建議走雙寫：複寫延遲不可控，讀寫分離應「單寫多讀」
5. 故障切換複雜：需外部協調器 (MHA, Orchestrator, ProxySQL) 管理 VIP 切換

適用場景

• 異地雙活：兩資料中心各寫入本地資料，業務邏輯保證不衝突 (如按區域分片)
• 高可用備援：單寫入口，另一端為熱備，故障時手動/自動切換

現代替代方案

結語

雙向主從 配置簡單但語意危險。生產環境強烈建議遷移至 Group Replication / Galera / InnoDB Cluster，獲得共識協議保證的一致性與自動故障轉移。若必須用雙向主從，請嚴格實施：auto_increment_offset、應用層分片寫入、pt-table-sync 定期校驗、外部故障切換器。

1. 上一篇文章： MySQL 向資料表匯入資料三大方法：INSERT、LOAD DATA、mysqlimport 完整指南
2. 下一篇文章： phpMyAdmin 2.x 安裝、配置、多伺服器管理與進階認證完整手冊

MySQL 資料庫相關知識

MySQL Root 密碼修改全方案：知密碼、忘密碼、命令行、SQL 三大場景

場景一：已知當前 Root 密碼 方法 1：SET PAS

MySQL 預存程序與函數完整教學：語法、參數模式、流程控制、游標實戰

一、預存程序 1.1 基礎語法 CREATE PROCED

MySQL OOM 系列三：擺脫被 OOM Killer 終結的厄運

前言 前兩章分析了 Linux 記憶體過量分配策略與 OOM

MySQL UNION 與 ORDER BY 共用避坑指南：括號、子查詢與去重陷阱

前言：UNION + ORDER BY 為何總報錯？ UNI