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

MySQL 主主同步與主從複寫完整部署指南：跨伺服器資料庫同步備份方案

編輯：MySQL 資料庫相關知識

資料庫同步備份是確保資料高可用的核心手段。本文詳細記錄三種 MySQL 跨伺服器同步方案：雙向主主同步、傳統主從複寫、以及基於 GTID 的現代化複寫架構，涵蓋使用者建立、配置檔調整、資料初始化、同步驗證全流程。

環境規劃與前置準備

角色	IP 位址	Server ID	說明
Server A (Master/Master)	192.168.1.1	1	主庫，同時作為對方的從庫
Server B (Master/Master)	192.168.1.2	2	主庫，同時作為對方的從庫

步驟 1：建立同步專用帳號並授權

-- 在 Server A 執行，供 Server B 連線
GRANT REPLICATION SLAVE ON *.* TO 'sync_a'@'192.168.1.2' IDENTIFIED BY 'aaa';
FLUSH PRIVILEGES;

-- 在 Server B 執行，供 Server A 連線
GRANT REPLICATION SLAVE ON *.* TO 'sync_b'@'192.168.1.1' IDENTIFIED BY 'bbb';
FLUSH PRIVILEGES;

注意：僅授予 REPLICATION SLAVE 權限，遵循最小權限原則；生產環境建議限制來源 IP。

步驟 2：配置 my.cnf 核心參數

Server A 配置

[mysqld]
user = mysql
server-id = 1
log-bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates = 1
slave-skip-errors = all
sync_binlog = 1

Server B 配置

[mysqld]
user = mysql
server-id = 2
log-bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql
replicate-do-db = test
replicate-ignore-db = mysql
log-slave-updates = 1
slave-skip-errors = all
sync_binlog = 1

關鍵參數說明：

server-id：叢集內唯一，嚴禁重複
log-slave-updates：主主同步必開，讓從庫執行的 relay log 也寫入自己的 binlog，供對方拉取
sync_binlog=1：每次提交同步刷盤，最大程度保證資料不丟失（效能略有影響）
勿使用 auto_increment_increment / auto_increment_offset：這兩參數是導致自增 ID 跳號的元兇

步驟 3：資料初始化與同步啟動

雙方停庫：FLUSH TABLES WITH READ LOCK;
記錄雙方 SHOW MASTER STATUS\G 的 File 與 Position
將 Server A 資料目錄完整拷貝至 Server B（或用 mysqldump --master-data=2 導出導入）
解鎖：UNLOCK TABLES;
配置雙向複寫連線資訊（注意：雙方填寫對方的 binlog 座標）：

-- Server A 指向 Server B
CHANGE MASTER TO
    MASTER_HOST='192.168.1.2',
    MASTER_USER='sync_b',
    MASTER_PASSWORD='bbb',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=002;

-- Server B 指向 Server A
CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='sync_a',
    MASTER_PASSWORD='aaa',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=001;

啟動同步：START SLAVE;
驗證：SHOW SLAVE STATUS\G 確認 Slave_IO_Running: Yes 且 Slave_SQL_Running: Yes

方法二：傳統主從單向複寫（適用讀寫分離架構）

若僅需單向同步（A→B），配置更簡化：

主庫 (A) 僅需開啟 binlog

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = db1
binlog-do-db = db2

從庫 (B) 配置連線資訊

[mysqld]
server-id = 2
master-host = 192.168.1.1
master-user = sync_user
master-password = 123456
master-port = 3306
master-connect-retry = 60
replicate-do-db = db1
replicate-do-db = db2
replicate-ignore-db = mysql

啟動步驟：主庫鎖表導出資料 → 從庫導入 → 從庫執行 CHANGE MASTER TO ... → START SLAVE → 驗證狀態。

方法三：現代化 GTID 複寫（MySQL 5.6+ 推薦）

全域交易識別碼 (GTID) 讓複寫拓撲變更、故障轉移更簡單：

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-bin = mysql-bin
log-slave-updates = 1

啟動同步時無需指定 binlog 檔案與位置：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='repl',
    MASTER_PASSWORD='pass',
    MASTER_AUTO_POSITION = 1;

常見問題排查清單

現象	可能原因	排查指令
Slave_IO_Running: Connecting	網路不通/帳號密碼錯/防火牆阻擋	telnet master_ip 3306 / 檢查防火牆 / SHOW PROCESSLIST
Slave_SQL_Running: No	SQL 執行錯誤（鍵衝突/表結構不一致）	SHOW SLAVE STATUS\G 查看 Last_SQL_Error
Seconds_Behind_Master 偏大	從庫效能瓶頸/大事務/網路延遲	檢查長查詢 / 優化索引 / 升級硬體
主主同步資料衝突	雙方同時寫入同一主鍵	業務層路由寫入 / 使用自增步長錯開 ID