雙主復制讓兩台 MySQL 互為主從,寫入自動雙向同步。本文提供生產級配置模板:server-id、auto_increment_offset/increment 錯開自增、雙向 CHANGE MASTER TO、同步驗證、防火牆/網路互通檢查清單。
Server A (192.168.1.254, server-id=1) ←→ Server B (192.168.1.252, server-id=2)
↑ 寫入/讀取 ↑ 寫入/讀取
Binlog → Relay Log → SQL Thread Binlog → Relay Log → SQL Thread
| 參數 | Server A | Server B | 說明 |
|---|---|---|---|
| server-id | 1 | 2 | 全域唯一 |
| log-bin | mysql-bin | mysql-bin | 啟用 Binlog |
| replicate-do-db | test | test | 只同步 test 庫 |
| binlog-ignore-db | mysql,information_schema | 同 | 不同步系統庫 |
| auto-increment-increment | 2 | 2 | 節點總數 |
| auto-increment-offset | 1 | 2 | 錯開起始值 (奇/偶) |
| expire-logs-days | 100 | 100 | Binlog 保留天數 |
my.cnf 並 service mysqld restart-- On A
GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.252' IDENTIFIED BY '123456';
-- On B
GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.254' IDENTIFIED BY '123456';
mysqldump → SCP 到 B → B 導入 → 解鎖-- On A: SHOW MASTER STATUS; 記錄 File/Position
-- On B: SHOW MASTER STATUS; 記錄 File/Position
-- On A 執行 (指向 B 的 Binlog)
CHANGE MASTER TO MASTER_HOST='192.168.1.252', MASTER_USER='mysync', MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=192;
-- On B 執行 (指向 A 的 Binlog)
CHANGE MASTER TO MASTER_HOST='192.168.1.254', MASTER_USER='mysync', MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
START SLAVE;SHOW SLAVE STATUS \G 確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yesauto_increment_offset 必須錯開,否則主鍵衝突導致 SQL Thread 停止replicate-do-db),避免系統表同步衝突Seconds_Behind_Master,延遲超閾值告警MASTER_AUTO_POSITION=1),故障切換零配置測試驗證:A 插入一筆 → B 立即可查;B 更新 → A 同步生效。雙向寫入業務層需自行解決衝突 (如最後寫入勝、版本號樂觀鎖)。