MySQL 主從雙機熱備架構配置全攻略

架構原理

MySQL 透過 Binary Log（二進制日誌）實現主從複寫：Master 記錄所有資料變更，Slave 透過 I/O 執行緒拉取 Relay Log，SQL 執行緒重放，達到資料同步。版本需求：Master ≥ 3.23，Slave 版本 ≥ Master 版本。

Master 端配置

1. my.cnf / my.ini 關鍵參數：

   [mysqld]
   server-id=1
   log-bin=mysql-bin
   binlog-do-db=db_name       # 僅記錄指定庫
   binlog-ignore-db=mysql     # 排除系統庫

2. 建立複寫帳號：

   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.domain.com' IDENTIFIED BY 'slavepass';

   4.0.2 前版本用 GRANT FILE ON *.*。

3. 重啟服務 使配置生效。
4. 鎖表備份基線資料：

   FLUSH TABLES WITH READ LOCK;
   -- 物理複製 data 目錄或 mysqldump --master-data
   SHOW MASTER STATUS;  -- 記錄 File 與 Position
   UNLOCK TABLES;

Slave 端配置

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

2. 匯入 Master 基線資料（物理檔或 SQL 檔）。
3. 啟動 Slave 並指定同步座標：

   SLAVE STOP;
   CHANGE MASTER TO
       MASTER_HOST='master_ip',
       MASTER_USER='repl',
       MASTER_PASSWORD='slavepass',
       MASTER_LOG_FILE='mysql-bin.003',  -- Master 的 File
       MASTER_LOG_POS=73;                -- Master 的 Position
   SLAVE START;

驗證與維護

• SHOW PROCESSLIST\G 觀察 system user 執行緒狀態。
• SHOW SLAVE STATUS\G 關注 Seconds_Behind_Master（理想為 0）。

Binary Log 清理程序（無停機）

1. Master：FLUSH TABLES WITH READ LOCK;
2. Slave：SLAVE STOP;
3. Master：SHOW MASTER STATUS; 記錄新 File/Position。
4. Master：UNLOCK TABLES;
5. Slave：CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
6. Slave：SLAVE START;
7. Master：PURGE BINARY LOGS TO 'new_file';

版本升級注意

升級 Master 時先升級 Slave，確保相容性；參數 innodb_table_locks（5.0.3+）預設開啟，可能導致 LOCK TABLES 死鎖，視業務調整。