您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 雙向主備復制 (雙主) 生產級部署文檔：GTID、auto_increment 防衝突、log-slave-updates、故障切換腳本與監控體系

編輯：MySQL 綜合教學

環境基線

**OS：** Ubuntu 10.10 x86 (演示)，生產建議 CentOS 7+/Ubuntu 20.04+
**MySQL：** 5.1.61 (舊版)，**強烈建議 5.7+/8.0+** (GTID、性能、安全)
**節點：** Node A (192.85.1.175, server-id=1)、Node B (192.85.1.176, server-id=2)
**同步庫：** `hrkip_zhhwd`

1. 創建雙向複製賬號 (雙節點互授)


-- Node A
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'mysqlsync';
GRANT FILE, SELECT, REPLICATION SLAVE ON *.* TO 'replication'@'192.85.1.176' IDENTIFIED BY 'mysqlsync';
FLUSH PRIVILEGES;

-- Node B
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'mysqlsync';
GRANT FILE, SELECT, REPLICATION SLAVE ON *.* TO 'replication'@'192.85.1.175' IDENTIFIED BY 'mysqlsync';
FLUSH PRIVILEGES;

2. 驗證賬號與網絡互通


-- 雙向測試
mysql -ureplication -pmysqlsync -h192.85.1.176 -e "SELECT 1"
mysql -ureplication -pmysqlsync -h192.85.1.175 -e "SELECT 1"

-- 若報錯 ERROR 2003 (HY000): Can't connect... (111)
-- 原因：bind-address=127.0.0.1 限制遠程
-- 修復：my.cnf 註釋 #bind-address=127.0.0.1，重啟 mysqld

3. 核心配置 (現代化 GTID 版，適用 5.6+)

Node A (/etc/my.cnf)


[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
sync_binlog = 1

binlog_do_db = hrkip_zhhwd
binlog_ignore_db = mysql,information_schema,performance_schema,phpmyadmin
replicate_do_db = hrkip_zhhwd
replicate_ignore_db = mysql,information_schema

# 防主鍵衝突 (雙主核心)
auto_increment_increment = 2
auto_increment_offset = 1

Node B (/etc/my.cnf)


[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_slave_updates = 1
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_format = ROW
sync_binlog = 1

binlog_do_db = hrkip_zhhwd
binlog_ignore_db = mysql,information_schema,performance_schema,phpmyadmin
replicate_do_db = hrkip_zhhwd
replicate_ignore_db = mysql,information_schema

auto_increment_increment = 2
auto_increment_offset = 2

4. 啟動雙向同步 (GTID 自動定位)


-- Node A
CHANGE MASTER TO MASTER_HOST='192.85.1.176', MASTER_USER='replication', MASTER_PASSWORD='mysqlsync', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;
START SLAVE;

-- Node B
CHANGE MASTER TO MASTER_HOST='192.85.1.175', MASTER_USER='replication', MASTER_PASSWORD='mysqlsync', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;
START SLAVE;

5. 驗證同步狀態


SHOW SLAVE STATUS\G;
-- 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Retrieved_Gtid_Set: :1-100
Executed_Gtid_Set: :1-50,:1-50  -- 合集

6. 重置 Slave 重新同步 (故障恢復標準流程)


STOP SLAVE;
RESET SLAVE ALL;  -- 5.1 用 RESET SLAVE; 5.6+ RESET SLAVE ALL 清除 GTID 狀態
-- 刪除 binlog 文件 (可選，若從頭同步)
rm -f /var/log/mysql/mysql-bin.*
START SLAVE;

7. 測試雙向寫入


-- Node A
INSERT INTO ddgl_qdlx (qdlxmc, lxjc, qdlxbz) VALUES ('175a', '75a', '175a');
-- Node B 秒級可見

-- Node B
INSERT INTO ddgl_qdlx (qdlxmc, lxjc, qdlxbz) VALUES ('176b', '76b', '176b');
-- Node A 秒級可見

生產級監控與告警 (Prometheus + Grafana)


# 核心指標
mysql_slave_status_slave_io_running{instance="node_a"} == 1
mysql_slave_status_slave_sql_running{instance="node_a"} == 1
mysql_slave_status_seconds_behind_master{instance="node_a"} < 5
mysql_slave_status_retrieved_gtid_set != mysql_slave_status_executed_gtid_set  -- 偏移告警

# 自動化故障切換腳本 (優先級：ProxySQL / MHA / Orchestrator / 自研)
# 核心邏輯：檢測 Master 失效 → 提升 Slave 為新 Master → 修正另一邊 CHANGE MASTER → 應用流量切換