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

MySQL 雙機熱備份完整配置指南：版本要求、主從參數、鎖定備份與同步驗證

編輯：MySQL 資料庫相關知識

MySQL 雙機熱備（主從複制）是實現數據庫高可用、災難恢復的基石。本文系統梳理版本兼容性原則、主庫配置、全局鎖定備份、同步坐標獲取、從庫部署、同步啟動與狀態驗證全流程，附帶常見坑點避坑指南。

一、版本兼容性鐵律

MySQL 版本 **≥ 3.23** 支援複制。
從庫版本 ≥ 主庫版本（向上兼容）。
從庫版本 < 主庫版本：不支援，會導致 binlog 格式不識別、事件解析失敗。
建議：主從**同一大版本**（如均為 5.7.x 或 8.0.x），小版本從庫略高可接受。

二、主庫配置 (my.cnf / my.ini)

[mysqld]
server-id = 1                    # 唯一 ID，默認 1
log-bin = mysql-bin              # 啟用二進制日誌，可指定路徑/前綴
binlog-do-db = db_name           # 僅同步指定庫（多庫用逗號分隔）
binlog-ignore-db = mysql         # 忽略系統庫（推薦）
# 可選：binlog_format = ROW      # 推薦 ROW 格式，避免非確定性函數導致主從不一致

三、主庫建立複制專用帳號

-- MySQL 4.0.2+ 推薦
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

-- 4.0.2 前版本（無 REPLICATION SLAVE 權限）
GRANT FILE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

安全： 限制主機為從庫 IP 或內網段，避免 %。

四、主庫鎖定、備份、記錄坐標、解鎖（標準冷備同步基準點）

全局只讀鎖： FLUSH TABLES WITH READ LOCK;（阻塞所有寫入）。
備份數據：
- 方式 A（推薦，速度快）：直接打包 datadir 目錄（如 tar czf /backup/data.tar.gz /var/lib/mysql/db_name）。
- 方式 B：mysqldump --master-data=2 --single-transaction db_name > /backup/db.sql（含同步坐標註釋）。
記錄坐標： SHOW MASTER STATUS\G，記下 File、Position。
解鎖： UNLOCK TABLES;（恢復寫入）。

五、從庫配置 (my.cnf / my.ini)

[mysqld]
server-id = 2                    # 唯一，不可與主庫/其他從庫重複
master-host = master_ip          # 主庫 IP 或域名
master-port = 3306               # 主庫端口
master-user = repl               # 複制帳號
master-password = slavepass      # 複制密碼
master-connect-retry = 60        # 斷線重試間隔(秒)
report-host = slave_ip           # 向主庫報告的從庫主機名（用於 SHOW SLAVE HOSTS）

注意： MySQL 5.6+ 推薦用 CHANGE MASTER TO 動態配置，而非寫死配置文件（重啟才生效、不靈活）。

六、從庫導入備份並啟動同步

導入主庫備份（解壓 datadir 或 source sql 文件）。
啟動從庫 MySQL 服務（若未加 --skip-slave-start 會自動嘗試連主庫，建議啟動參數加上）。
登入從庫：STOP SLAVE;（確保未運行）。
動態配置同步參數（覆蓋配置文件）：

CHANGE MASTER TO
    MASTER_HOST = 'master_ip',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'slavepass',
    MASTER_LOG_FILE = 'mysql-bin.000003',  -- 主庫 SHOW MASTER STATUS 的 File
    MASTER_LOG_POS = 73;                   -- 主庫 SHOW MASTER STATUS 的 Position

START SLAVE; 啟動複制線程。

七、驗證同步狀態

SHOW SLAVE STATUS\G

核心指標：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

任一 No 即異常：

Slave_IO_Running: No — 網路不通、帳號密碼錯、防火牆攔截、主庫 binlog 損壞。檢查 Last_IO_Error。
Slave_SQL_Running: No — 重放邏輯錯誤（主鍵衝突、外鍵失敗、字符集不匹配）。檢查 Last_SQL_Error。

八、參數修正與維護指令

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'new_master_ip';
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000005', MASTER_LOG_POS = 106;
START SLAVE;

九、常見坑點避坑表

坑點	現象	規避
主庫未開 log-bin	SHOW MASTER STATUS 為空、從庫無法連接	配置文件必須含 log-bin，重啟生效
server-id 重複	從庫連接報錯、主庫拒絕	規劃唯一 ID，建議用 IP 最後段
字符集不一致	從庫重放報錯、數據亂碼	主從 my.cnf 統一 character-set-server
主庫防火牆擋 3306	Slave_IO_Running: No，Last_IO_Error: connection refused	雲安全組/iptables 放行從庫 IP 段
備份未加 --master-data	不知同步起始坐標、需手動 SHOW MASTER STATUS	mysqldump 必加 --master-data=2
大表備份鎖表時間過長	業務寫入阻塞、超時	用 --single-transaction (InnoDB) 或 LVM 快照/xtrabackup 熱備

十、總結

MySQL 雙機熱備核心流程：**版本對齊 → 主庫開 binlog+建帳號 → 鎖定備份+取坐標 → 從庫導入備份+CHANGE MASTER → 啟動驗證 IO/SQL 線程雙 Yes**。生產環境務必補全：GTID 模式、半同步複制、延遲從庫、自動切主腳本、監控告警體系，構建真正的高可用架構。