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

Linux MySQL 主從復制 (Replication) 搭建全流程：GTID 模式、雙主高可用、監控告警與故障切換實戰

編輯：MySQL 綜合教學

前言

MySQL Replication 是讀寫分離、災備、多活架構的基石。本文基於 **GTID 模式 (Global Transaction Identifier)** 給出生產級 **單向主從、雙主 (Master-Master)、延遲從庫** 搭建全流程，涵蓋配置模板、權限最小化、備份建立、同步驗證、監控指標、故障切換 SOP，拒絕舊式 File/Position 模式痛點。

一、核心架構選型：為什麼選 GTID？

模式	定位方式	痛點	GTID 優勢
File/Position (傳統)	`MASTER_LOG_FILE` + `MASTER_LOG_POS`	從庫重搭需精確位點、多級復制易斷、故障切換需手動計算位點	全局唯一事務 ID，自動定位、支援多源復制、故障切換零位點運維
GTID (5.6+ 推薦)	`GTID_SET` (UUID:1-100)	需 5.6+、主從版本一致性要求高	生產標準，簡化運維 90%

二、環境準備與配置模板

主庫配置 (Master - 10.0.1.10)

[mysqld]
# === 基礎 ===
server-id = 10                              # 集群唯一，建議 IP 末段擴展
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# === 復制相關 ===
log_slave_updates = ON                      # 關鍵：鏈式復制/雙主必開
binlog_rows_query_log_events = ON           # ROW 格式記錄原 SQL
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON                     # 從庫崩潰自動修復 Relay Log

# === 安全/效能 ===
max_connections = 2000
skip-name-resolve = 1                       # 禁用 DNS 反解，加速連接

從庫配置 (Slave - 10.0.1.20)

[mysqld]
server-id = 20
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = /var/log/mysql/mysql-bin          # 從庫也開 Binlog (支援級聯/升主)
log_slave_updates = ON
binlog_format = ROW
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
relay_log = /var/log/mysql/relay-bin
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
read_only = ON                              # 關鍵：防止誤寫入
super_read_only = ON                        # 8.0+ 僅 super 權限可寫
skip-name-resolve = 1

三、搭建標準流程 (單向主從)

步驟 1：主庫創建復制賬號 (最小權限)

mysql> CREATE USER 'repl_user'@'10.0.1.%' IDENTIFIED BY 'StrongReplPass@123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.1.%';
mysql> FLUSH PRIVILEGES;

步驟 2：主庫全量備份 (含 GTID 集合)

# 熱備份 (推薦 xtrabackup)
xtrabackup --backup --target-dir=/backup/full_$(date +%F) \
  --user=root --password='***' --host=10.0.1.10 \
  --slave-info --safe-slave-backup

# 或邏輯備份 (小數據量)
mysqldump --single-transaction --master-data=2 --flush-logs \
  --all-databases --routines --triggers --events \
  --set-gtid-purged=ON \
  -u root -p > /backup/full_$(date +%F).sql

✅ 關鍵：--master-data=2 將 `CHANGE MASTER TO` 註釋寫入文件；--set-gtid-purged=ON 導出 GTID 集合。

步驟 3：從庫恢復備份

# 邏輯備份恢復
mysql -u root -p < /backup/full_20240115.sql

# 物理備份恢復 (xtrabackup)
xtrabackup --prepare --target-dir=/backup/full_20240115
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full_20240115
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

步驟 4：配置從庫同步起點 (GTID 模式極簡)

mysql> CHANGE MASTER TO
    MASTER_HOST = '10.0.1.10',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongReplPass@123',
    MASTER_PORT = 3306,
    MASTER_AUTO_POSITION = 1;   -- 核心：啟用 GTID 自動定位，無需 File/Position

mysql> START SLAVE;

步驟 5：驗證同步狀態

mysql> SHOW SLAVE STATUS\G
-- 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Retrieved_Gtid_Set: xxxxxxxx:1-100
Executed_Gtid_Set: xxxxxxxx:1-100
-- 兩個 GTID_SET 一致且 Running=Yes 即同步正常

四、進階拓撲：雙主 (Master-Master) 高可用

適用於**同城雙活、跨 AZ 熱備**，雙向寫入需業務層保證無主鍵衝突 (自增步長/偏移量)。

雙主關鍵配置差異

# Node A (10.0.1.10)
auto_increment_increment = 2
auto_increment_offset = 1

# Node B (10.0.1.20)
auto_increment_increment = 2
auto_increment_offset = 2

# 互為主從
# Node A 上
CHANGE MASTER TO MASTER_HOST='10.0.1.20', MASTER_AUTO_POSITION=1 ...; START SLAVE;

# Node B 上
CHANGE MASTER TO MASTER_HOST='10.0.1.10', MASTER_AUTO_POSITION=1 ...; START SLAVE;

⚠️ 雙主風險與規避

**寫入衝突**：唯一鍵衝突導致 SQL 線程報錯停止。→ 業務層分片、或使用 Orchestrator/MHA 自動切換單寫。
**環狀復制循環**：需 `log_slave_updates=ON` + `replicate_same_server_id=OFF` (默認)。
**強烈建議**：生產環境**單寫多讀**，由 ProxySQL/MHA/Orchestrator 管理單點寫入，避免雙寫複雜度。

五、監控告警體系 (核心指標)

指標	告警閾值	採集方式
Slave_IO_Running / Slave_SQL_Running	!= Yes	Exporter / Shell 腳本
Seconds_Behind_Master	> 300 秒 (5 分鐘)	SHOW SLAVE STATUS
Relay_Log_Space	> 10GB	SHOW SLAVE STATUS
GTID 差集 (Executed - Retrieved)	非空集合	自定義 SQL 對比
主庫 Binlog 磁盤佔用	> 磁盤 70%	Node Exporter
從庫 Relay Log 磁盤佔用	> 磁盤 70%	Node Exporter

六、故障切換 SOP (主庫掛了 → 從庫升主)

# 1. 確認主庫不可用 (網絡/進程/硬件)
# 2. 選定最新從庫 (Seconds_Behind_Master=0, GTID 集合最全)
# 3. 升主操作
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;          -- 清除從庫元數據 (8.0+)
# 或 5.7: RESET SLAVE;
mysql> SET GLOBAL read_only = OFF;
mysql> SET GLOBAL super_read_only = OFF;

# 4. 應用層切換寫入地址 (VIP 浮動 / DNS 修改 / ProxySQL 後端切換)
# 5. 原主庫恢復後，作為新從庫掛載
#    重新 CHANGE MASTER TO 新主庫 ... MASTER_AUTO_POSITION=1; START SLAVE;

# 工具化切換 (推薦)
# Orchestrator / MHA / ProxySQL 自動故障轉移，秒級切換，零人工干預

七、總結

MySQL 主從搭建鐵律：
1. **GTID 模式 + ROW 格式 + MASTER_AUTO_POSITION=1**，簡化 90% 運維。
2. **從庫 read_only=ON + super_read_only=ON**，防誤寫。
3. **備份恢復用 xtrabackup**，大數據量唯一選擇。
4. **監控核心：IO/SQL Running、Seconds_Behind_Master、GTID 一致性**。
5. **故障切換工具化 (Orchestrator/MHA)**，拒絕手動切換。
建立「搭建 → 驗證 → 監控 → 演練 → 切換」完整生命週期管理體系，才是高可用架構的正確打開方式。