MySQL 雙機熱備（主從複製）生產級配置與故障處理全流程

MySQL 雙機熱備：主從複製生產級部署、運維與故障處理完整指南

MySQL 無增量備份機制，大數據量下全量備份耗時長、影響業務。主從複製（基於 Binlog 異步重放）實現準實時熱備。本文覆蓋版本要求、主庫配置、從庫搭建、狀態監控、Binlog 清理、故障切換全生命周期。

版本兼容性鐵律

• MySQL ≥ 3.23 支持複製
• 從庫版本 ≥ 主庫版本（向前兼容），反之會報錯
• 生產環境建議主從版本完全一致

主庫配置 /etc/my.cnf 核心參數

[mysqld]
server-id=1                           # 全局唯一
log-bin=mysql-bin                     # 必須啟用，複製基礎
binlog-do-db=business_db              # 只記錄業務庫（可選，減少日志量）
binlog-ignore-db=mysql,test,tmp       # 忽略系統/臨時庫（可選）
sync_binlog=1                         # 每次提交刷盤，防數據丟失（性能略降）
innodb_flush_log_at_trx_commit=1      # InnoDB ACID 保障

重啟主庫生效。

創建複製專用賬號

-- MySQL 4.0.2+ 標準語法
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
FLUSH PRIVILEGES;

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

主庫基線數據導出（不停機方案）

1. 鎖表刷盤：FLUSH TABLES WITH READ LOCK;
2. 記錄基線位置：SHOW MASTER STATUS\G; 記下 File (如 mysql-bin.003) 和 Position (如 73)
3. 導出數據（擇一）：
   • 物理複製（快）：tar czf /backup/mysql_snapshot.tar.gz /var/lib/mysql/（需鎖表期間完成）
   • 邏輯導出（慢、跨版本安全）：mysqldump --single-transaction --master-data=2 --all-databases > /backup/full.sql
4. 解鎖：UNLOCK TABLES;

從庫配置 /etc/my.cnf

[mysqld]
server-id=2                           # 唯一，≠1
master-host=192.168.1.100             # 主庫 IP/域名
master-port=3306
master-user=repl
master-password=slavepass
master-connect-retry=60               # 斷線重試間隔(秒)
report-host=slave1.mydomain.com       # 向主庫報告的從庫標識
# replicate-do-db=business_db         # 可選：只同步業務庫
# replicate-ignore-db=test,tmp        # 可選：忽略庫
relay-log=relay-bin                   # 中繼日志文件名
relay-log-index=relay-bin.index
log-slave-updates=1                   # 從庫也寫 Binlog，支持鏈式複製 A→B→C
read-only=1                           # 從庫只讀，防止誤寫入

從庫導入基線數據並啟動複製

1. 導入基線數據（解壓物理備份或 mysql < full.sql）
2. 啟動從庫服務（若未加 --skip-slave-start 會自動嘗試連接，建議手動控制）
3. 進入 MySQL 客戶端：

   SLAVE STOP;
   CHANGE MASTER TO
       MASTER_HOST='192.168.1.100',
       MASTER_USER='repl',
       MASTER_PASSWORD='slavepass',
       MASTER_LOG_FILE='mysql-bin.003',   -- 主庫 SHOW MASTER STATUS 記錄的 File
       MASTER_LOG_POS=73;                 -- 主庫記錄的 Position
   SLAVE START;
   

4. 驗證：SHOW SLAVE STATUS\G; 確認 Slave_IO_Running=Yes、Slave_SQL_Running=Yes、Seconds_Behind_Master=0

雙機熱備監控與告警

Binlog 膨脹清理標準化流程（零停機）

1. 主庫鎖表：FLUSH TABLES WITH READ LOCK;
2. 從庫停止複製：SLAVE STOP;
3. 主庫查看當前 Binlog：SHOW MASTER STATUS; 記錄 File、Position
4. 主庫解鎖：UNLOCK TABLES;
5. 從庫更新同步座標：

   CHANGE MASTER TO
       MASTER_LOG_FILE='mysql-bin.005',   -- 新文件名
       MASTER_LOG_POS=120;                -- 新位置
   

6. 從庫啟動複製：SLAVE START;
7. 主庫清理舊日志：PURGE MASTER LOGS TO 'mysql-bin.005';（保留當前正在寫入及從庫未處理的日志）

故障切換（主庫掛掉）應急預案

1. 確認主庫不可恢復（硬件故障、數據目錄損壞）
2. 從庫提升為新主庫：

   SLAVE STOP;
   RESET MASTER;              -- 清空舊 Binlog，重新開始
   -- 修改 my.cnf：移除 master-* 參數，添加 server-id=1, log-bin=mysql-bin
   -- 重啟服務
   

3. 應用端修改數據庫連接指向新主庫（建議使用 VIP/域名 + DNS 切換，秒級生效）
4. 修復舊主庫後，作為新從庫加入集群（重新搭建主從）

進階架構演進

• 雙主 (Master-Master)：雙向複製，auto_increment_offset/increment 避免主鍵衝突，需應用層路由寫入單一主庫
• GTID 複制 (MySQL 5.6+)：全局事務 ID 簡化故障轉移，自動定位同步位置
• 半同步複製：rpl_semi_sync_master=1 至少一個從庫收到日志才提交，平衡性能與數據安全
• MHA/Orchestrator：自動化故障檢測與切換工具