MySQL 雙向複寫技術完整實戰指南

MySQL 雙向複寫（Master-Master Replication）能讓兩台伺服器互為主從，實現資料即時雙向同步，適用於跨機房容災、讀寫分離負載均衡等場景。本文以 MySQL 5.0.27、RedHat Linux 9 為例，詳細記錄從環境準備到雙向同步啟動的完整操作流程，供生產環境部署參考。

一、環境規劃與版本原則

• 版本一致性：Master 與 Slave 版本最好相同，或 Master 版本 ≤ Slave 版本，避免 Binlog 格式不兼容。
• 本文假設環境：
  • Master (A)：192.168.0.1，basedir=/usr/local/mysql，datadir=/var/lib/mysql
  • Slave (B)：192.168.0.2，目錄結構同 A
  • 同步目標資料庫：test，忽略系統庫 mysql

二、配置單向複寫 A → B

1. 設定 Master (A) 的 my.cnf

[mysqld]
log-bin=/var/log/mysql/updatelog
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql

重啟 MySQL，建立專用複寫帳號：

GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'back'@'192.168.0.2' IDENTIFIED BY 'back';
FLUSH PRIVILEGES;

2. 設定 Slave (B) 的 my.cnf

[mysqld]
server-id = 2
master-host = 192.168.0.1
master-user = back
master-password = back
master-port = 3306
replicate-ignore-db=mysql
replicate-do-db=test

重啟 MySQL。

3. 啟動同步

在 Master (A) 查看 Binlog 位置：

SHOW MASTER STATUS;
+------------------+----------+-------------------+------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+------------------+----------+-------------------+------------------+
| updatelog.000028 | 313361   | test              | mysql            |
+------------------+----------+-------------------+------------------+

在 Slave (B) 執行：

SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000028', MASTER_LOG_POS=313361;
SLAVE START;

驗證狀態：

SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

均為 Yes 即同步正常。寫入測試資料驗證。

三、擴展為雙向複寫 B → A

1. 設定 B 為 Master

修改 B 的 my.cnf 新增：

log-bin=/var/log/mysql/updatelog
binlog-do-db=test
binlog-ignore-db=mysql

重啟並建立反向帳號：

GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'back'@'192.168.0.1' IDENTIFIED BY 'back';
FLUSH PRIVILEGES;

2. 設定 A 為 Slave

修改 A 的 my.cnf 追加：

master-host = 192.168.0.2
master-user = back
master-password = back
master-port = 3306
replicate-ignore-db=mysql
replicate-do-db=test

重啟 MySQL。

3. 啟動反向同步

在 B 查看 SHOW MASTER STATUS; 獲取 File/Position，在 A 執行：

SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000028', MASTER_LOG_POS=13753;
SLAVE START;

再次驗證 SHOW SLAVE STATUS\G 兩邊均為 Yes。雙向複寫至此建立完成。

四、關鍵注意事項與避坑指南

1. 避免自增 ID 衝突：雙主環境下建議設定 auto_increment_offset 與 auto_increment_increment 錯開 ID 範圍（如 A 奇數、B 偶數）。
2. 嚴格過濾系統庫：務必 binlog-ignore-db=mysql 與 replicate-ignore-db=mysql，防止權限變更導致同步中斷。
3. 監控同步延遲：生產環境需部署監控（如 Seconds_Behind_Master），異常時自動告警。
4. 備份策略不可少：複寫 ≠ 備份，仍需定期邏輯/物理備份應對誤操作。

五、總結

MySQL 雙向複寫本質是兩組單向複寫的疊加，核心在於：版本一致、server-id 唯一、Binlog 位置精準對接、權限帳號雙向授權。按步驟操作並做好衝突預防與監控，即可獲得高可用的雙活架構。