MySQL 雙機熱備 (主從復制) 架構設計與搭建全流程

當數據量大到備份耗時過長，主從復制 (Replication) 成為實現熱備、讀寫分離、災難恢復的標準方案。本文詳細記錄版本要求、主庫配置、基準備份、從庫搭建、狀態驗證與日常維護操作。

1. 版本與原則

• MySQL ≥ 3.23 支援復制；生產建議 主從版本一致或從庫版本 ≥ 主庫版本 (向後兼容)。
• 復制基於 Binlog：主庫記錄變更事件，從庫 IO 線程拉取 → Relay Log → SQL 線程重放。

2. 主庫配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = biz_db          -- 可選：只同步指定庫
binlog-ignore-db = mysql,test  -- 可選：忽略系統庫
sync_binlog = 1                -- 每次提交刷盤，最大程度保證不丟數據
innodb_flush_log_at_trx_commit = 1

建立同步帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl_pwd';
FLUSH PRIVILEGES;

基準備份 (兩種方式)

1. 物理備份 (推薦大數據量)：
   FLUSH TABLES WITH READ LOCK;
tar czf /backup/data_$(date +%F).tar.gz /var/lib/mysql (或 LVM 快照、xtrabackup)
   SHOW MASTER STATUS; 記錄 File、Position
UNLOCK TABLES;
2. 邏輯備份：
   mysqldump --master-data=2 --all-databases -u root -p > full.sql
--master-data=2 會在導出文件頭部寫入 CHANGE MASTER TO 所需的 Binlog 位置。

3. 從庫配置

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
# 可選：只同步特定庫
replicate-do-db = biz_db
replicate-ignore-db = mysql,test

導入基準數據並啟動復制

-- 導入物理備份: 解壓覆蓋 datadir，啟動 mysqld
-- 導入邏輯備份: mysql -u root -p < full.sql

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_pwd',
  MASTER_LOG_FILE='mysql-bin.000003',  -- 主庫 SHOW MASTER STATUS 所得
  MASTER_LOG_POS=73;

START SLAVE;

4. 狀態驗證

SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error: (空)
Last_SQL_Error: (空)

5. 日常維護操作

重新同步 (從庫數據損壞/偏移)

-- 主庫
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄新 File/Pos
UNLOCK TABLES;

-- 從庫
STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='新File', MASTER_LOG_POS=新Pos;
START SLAVE;

跳過單條錯誤 (謹慎)

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

延遲監控

腳本定時檢查 Seconds_Behind_Master，超過閾值 (如 300s) 告警。MySQL 5.6+ 可開啟並行復制 slave_parallel_workers=4 降低延遲。

6. GTID 模式 (5.6+ 強烈推薦)

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1

-- 從庫無需指定 File/Pos
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

優點：故障切換自動定位、多級復制拓撲簡單、數據一致性更強。