MySQL 主從雙機熱備完整配置指南（基於二進位日誌複製）

背景與原理

MySQL 原生不支援增量備份，大數據量下全量備份耗時久、佔空間。主從複製利用二進位日誌，將主庫寫入操作同步到從庫，實現近實時熱備。核心要求：

• 版本 ≥ 3.23
• 從庫版本 ≥ 主庫版本（向上相容）
• 主庫啟用 log-bin

主庫配置

1. 修改 my.cnf / my.ini

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=db_name        # 需同步的庫，多庫用逗號分隔
binlog-ignore-db=mysql      # 排除的庫
# 或僅設定 binlog-do-db，其餘不記錄

重啟 MySQL 生效。

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 --single-transaction -A > full.sql
SHOW MASTER STATUS\G;
-- 記下 File (如 mysql-bin.000003) 與 Position (如 73)
UNLOCK TABLES;

從庫配置

1. 修改 my.cnf

[mysqld]
server-id=2                 # 唯一，≠1
relay-log=relay-bin         # 建議顯式指定
read-only=1                 # 防誤寫入

2. 導入主庫備份資料

物理複製：直接覆蓋 data 目錄；邏輯備份：mysql < full.sql。

3. 設定主庫連線資訊並啟動

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_PORT=3306,
  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

日誌清理與位點重設（主庫 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 雙機熱備架構。