MySQL 雙機熱備（主從複寫）配置全流程：從版本需求、主庫鎖表備份到從庫同步驗證與日誌輪轉

前置條件

• 版本 ≥ 3.23（建議 5.0+），從庫版本 ≥ 主庫版本
• 主庫啟用 log_bin（複寫基礎）
• 網路互通、防火牆放行 3306 埠

一、主庫配置

1. my.cnf 關鍵參數

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=db1,db2          # 僅記錄指定庫（可選）
binlog-ignore-db=mysql,test   # 排除系統庫（可選）

2. 建立複寫專用帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'ReplPass!23';
-- 4.0.2 前版本用 GRANT FILE ON *.* ...

3. 重啟主庫生效

service mysqld restart

4. 鎖表、備份、記錄 Binlog 位點

mysql -u root -p
FLUSH TABLES WITH READ LOCK;
-- 終端 1 保持鎖定，終端 2 執行備份
# 物理備份（快、適合大庫）
tar -czf /backup/master_data.tar.gz /var/lib/mysql/db1 /var/lib/mysql/db2
-- 或 邏輯備份（跨版本、異構）
mysqldump --master-data=2 --single-transaction db1 db2 > /backup/master_dump.sql

-- 回終端 1 記錄位點
SHOW MASTER STATUS\G
-- 記下 File (如 mysql-bin.000003) 與 Position (如 73)

UNLOCK TABLES;

二、從庫配置

1. my.cnf 關鍵參數

[mysqld]
server-id=2                    # 唯一，≠ 主庫
master-host=192.168.1.10       # 主庫 IP
master-port=3306
master-user=repl_user
master-password=ReplPass!23
master-connect-retry=60
report-host=slave1.example.com
# replicate-do-db=db1          # 可選：僅同步指定庫

2. 匯入主庫備份資料

# 物理備份：直接解壓至 /var/lib/mysql/
tar -xzf /backup/master_data.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db1 /var/lib/mysql/db2

# 邏輯備份：
mysql -u root -p < /backup/master_dump.sql

3. 啟動從庫並設定同步起始點

service mysqld start
-- 若未加 --skip-slave-start 參數，需手動：
mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='ReplPass!23',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=73;
SLAVE START;

三、驗證同步狀態

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

四、日誌過大清理流程（不中斷同步）

1. 主庫：FLUSH TABLES WITH READ LOCK;
2. 從庫：SLAVE STOP;
3. 主庫：SHOW MASTER STATUS; 記錄新 File/Position
4. 主庫：UNLOCK TABLES;
5. 主庫：PURGE BINARY LOGS TO 'mysql-bin.000005';（保留最新幾個）
6. 從庫：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=4;
7. 從庫：SLAVE START;

五、監控與告警建議

• Seconds_Behind_Master > 300 觸發告警
• Slave_IO_Running=No 或 Slave_SQL_Running=No 立即告警
• 定期 SHOW SLAVE STATUS\G 輸出存檔審計

結語

MySQL 主從複寫是「異步、單執行緒 SQL 執行（5.6 前）、基於 Binlog」的經典架構。配置核心在於：server-id 唯一、log_bin 啟用、專用複寫帳號、鎖表備份記錄位點、從庫 CHANGE MASTER 對齊位點。生產環境建議升級至 GTID 模式（5.6+）簡化故障切換與拓撲管理。