MySQL 雙主同步複製配置實戰指南

架構說明

MySQL 支援主從複製、主主互備。雙主模式下，兩台資料庫互為主從，實現雙向即時同步，兼具資料備份與讀寫負載分攤功能。本文以兩台伺服器為例：

• Server A：192.168.0.1
• Server B：192.168.0.2

一、Server A 配置

1. 建立同步帳號

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.0.2' IDENTIFIED BY '123456';

2. 修改 my.cnf

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
log-bin=mysql-bin
server-id=1
master-host=192.168.0.2
master-user=tongbu
master-password=123456
master-port=3306
master-connect-retry=30
binlog-do-db=mysql
replicate-do-db=mysql
skip-name-resolve
back_log = 500
max_connect_errors = 100
max_connections=1000

3. 重啟服務

service mysqld restart

二、Server B 配置

1. 建立同步帳號

mysql> GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'192.168.0.1' IDENTIFIED BY '123456';

2. 修改 my.cnf（作為備庫的差異配置）

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
server-id=2
master-host=192.168.0.1
master-user=tongbu
master-password=123456
master-port=3306
binlog-do-db=mysql
replicate-do-db=mysql
skip-name-resolve
back_log = 500
max_connections = 1000
max_connect_errors = 100
slave_skip_errors=1062
sync_binlog=1
log-bin=mysql-bin

關鍵差異：Server B 額外設定 slave_skip_errors=1062（忽略主鍵衝突）與 sync_binlog=1（每次操作同步刷盤），因定位為備庫角色。

3. 重啟服務

service mysqld restart

三、同步初始化與驗證

1. 在 Server A 查看主庫狀態

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 11809679 | mysql        |                  |
+------------------+----------+--------------+------------------+

記錄 File 與 Position 值。

2. 在 Server B 設定同步起點並啟動

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=11809679;
mysql> START SLAVE;

3. 驗證同步狀態

mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

兩者皆為 Yes 即代表雙主同步已正常運行。

四、生產環境注意事項

• 雙主模式下避免雙向寫入同一張表，易產生主鍵衝突與資料不一致
• 建議採用「一寫一讀」或「分庫分表」策略分攤寫入壓力
• 監控 Seconds_Behind_Master 確保同步延遲在可接受範圍
• 定期校驗主從資料一致性（可用 pt-table-checksum）
• auto_increment_offset 與 auto_increment_increment 配置防止自增 ID 衝突