MySQL 主從雙機熱備配置詳解（含版本要求、參數、驗證、日誌清理）

核心原則

• MySQL ≥ 3.23 支援複製
• 從庫版本 ≥ 主庫版本 (向上相容)
• 主庫必須啟用 log-bin，複製基於二進位日誌

主庫配置步驟

1. my.cnf 關鍵參數

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=db_name          # 需同步的庫
binlog-ignore-db=mysql,test   # 排除的庫
# 多庫用逗號分隔

重啟生效。

2. 建立複製帳號

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

舊版本 (≤4.0.2) 用 GRANT FILE ON *.*。

3. 鎖表、備份、記錄位點

FLUSH TABLES WITH READ LOCK;
-- 物理複製 data 目錄 (最快) 或 mysqldump --master-data=2
SHOW MASTER STATUS\G;
-- 記錄 File (如 mysql-bin.000003) 和 Position (如 73)
UNLOCK TABLES;

從庫配置步驟

1. my.cnf 參數

[mysqld]
server-id=2                 # 唯一，≠主庫
master-host=master_ip
master-port=3306
master-user=repl
master-password=slavepass
master-connect-retry=60
report-host=slave_ip

MySQL 5.6+ 建議改用 CHANGE MASTER TO 動態設定，不寫死配置檔。

2. 導入主庫備份資料

物理檔案覆蓋或 mysql < dump.sql。

3. 設定主庫連線並啟動

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='slavepass',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=73;
START SLAVE;

4. 驗證同步狀態

SHOW SLAVE STATUS\G;
-- 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0
SHOW PROCESSLIST\G;  -- 確認有 system user 執行 Slave_IO/SQL 線程

主庫 Binlog 過大清理流程 (不中斷複製)

1. 主庫 FLUSH TABLES WITH READ LOCK;
2. 從庫 STOP SLAVE;
3. 主庫 SHOW MASTER STATUS; 記錄新 File/Position
4. 主庫 UNLOCK TABLES;
5. 從庫 CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
6. 從庫 START SLAVE;

避坑提醒

• server-id 全局唯一：多從庫環境必須各異。
• binlog-do-db **僅對當前預設庫生效**，跨庫更新不記錄；建議主庫全記錄，從庫用 replicate-do-db 過濾。
• 防火牆需放行 3306 雙向。
• 新架構優先啟用 GTID 模式 (MySQL 5.6+)，簡化故障轉移與位點管理。

按此流程配置，即可建立穩定可靠的 MySQL 雙機熱備架構。