您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 主從與雙主複制配置實戰：從單向同步到雙向熱備

編輯：MySQL 資料庫相關知識

場景對比

架構	寫入高可用	讀取擴展	衝突風險	適用場景
單向主從	無（主掛需人工切換）	強（多從庫分攤讀）	無	讀多寫少、可接受分鐘級 RTO
雙主（Master-Master）	強（任意節點可寫）	強	有（需自增交錯、業務去重）	雙活數據中心、跨機房災備

環境準備

兩台實例：192.168.1.100 (Master A)、192.168.1.101 (Master B)
MySQL 5.7+，已安裝啟動，防火牆放行 3306，關閉 SELinux/AppArmor 或配置放行。
同步庫：test1，忽略系統庫 mysql、test。

第一階段：單向主從（A → B）

1. Master A 配置 (my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = test1
binlog-ignore-db = mysql,test
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = 1

2. Slave B 配置 (my.cnf)

[mysqld]
server-id = 2
relay-log = relay-bin
log-slave-updates = 1
gtid_mode = ON
enforce_gtid_consistency = ON
replicate-do-db = test1
replicate-ignore-db = mysql,test

3. 重啟兩側 MySQL

systemctl restart mysqld

4. Master A 建立複制賬號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

5. Master A 全量備份並記錄位點

# A 上執行
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;
-- 記下 File、Position
mysqldump --single-transaction --master-data=2 --databases test1 > /tmp/test1.sql
UNLOCK TABLES;

6. Slave B 恢復數據並啟動複制

# B 上執行
mysql < /tmp/test1.sql

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;  -- GTID 模式無需手動指定 File/Position

START SLAVE;
SHOW SLAVE STATUS\G;

驗證 Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0。

第二階段：升級為雙主（B → A）

1. Master B 開啟 Binlog 並配置同步庫

[mysqld]  # 追加在 B 的 my.cnf
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = test1
binlog-ignore-db = mysql,test

重啟 B：systemctl restart mysqld

2. Master B 建立複制賬號給 A

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.100' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

3. Master A 配置為 Slave 指向 B

# A 的 my.cnf 追加
relay-log = relay-bin
log-slave-updates = 1
replicate-do-db = test1
replicate-ignore-db = mysql,test
master-host = 192.168.1.101
master-user = repl_user
master-password = repl_pass
master-port = 3306
master-connect-retry = 60

重啟 A：systemctl restart mysqld

4. 同步 B 的位點到 A（GTID 模式自動，非 GTID 需手動）

# 非 GTID 時在 B 查位點
SHOW MASTER STATUS\G;
# 假設 File=mysql-bin.000001, Pos=106

# 在 A 執行
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.101',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=106;
START SLAVE;

5. 雙向驗證

# A 寫入
INSERT INTO test1.t VALUES ('from_A');
# B 查詢
SELECT * FROM test1.t;  -- 見 from_A

# B 寫入
INSERT INTO test1.t VALUES ('from_B');
# A 查詢
SELECT * FROM test1.t;  -- 見 from_B

生產級防衝突與運維加固

自增交錯：A auto_increment_offset=1, auto_increment_increment=2；B offset=2, increment=2，主鍵永不衝突。
業務層去重：唯一索引約束 + 冪等設計，防止雙寫臟數據。
只讀從庫分流：掛掛從庫給報表/查詢，主庫專注寫入。
監控告警：Seconds_Behind_Master > 5、Slave_SQL_Running=No、Slave_IO_Running=No 即時告警。
定期一致性校驗：pt-table-checksum --databases test1 --recursion-method=hosts。

常見故障速查表

現象	排查方向
IO_Running=No	網路、防火牆、授權、MASTER_HOST/PORT/USER/PASS、Server UUID 衝突
SQL_Running=No	Last_Error 看具體錯誤：主鍵衝突、外鍵失敗、字符集不符、長事務鎖等待
延遲飆升	從庫硬體弱、大事務、單線程複制、磁碟 IO 瓶頸 → 升級 5.7+ 開啟 MTS
GTID 跳號/斷裂	`SHOW MASTER STATUS` 對比 `Retrieved_Gtid_Set`、`Executed_Gtid_Set`，必要時 `SET GTID_NEXT` 手動補丁

總結

MySQL 複制從單向主從到雙主熱備，核心步驟是 Binlog 開啟 → 專用賬號授權 → 全量基準同步 → GTID/位點對齊 → 雙向驗證。生產環境務必配合 GTID、自增交錯、半同步、MHA/Orchestrator 自動切換、pt-table-checksum 巡檢，才能真正承擔核心業務雙活流量。