MySQL 雙機熱備 (Master-Slave) 完整部署手冊：從 my.cnf 到驗證同步

MySQL 原生非同步複寫 (Replication) 實現雙機熱備。本文詳述版本需求、Master/Slave 設定檔、建立複寫帳號、資料同步、啟動驗證、日志清理全流程。

前置條件

• 版本 ≥ 3.23；Slave 版本 ≥ Master 版本 (向上相容)。
• Master 必須啟用 log_bin (二進位日志)。

1. Master 設定 (my.cnf / my.ini)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = db_name          # 僅記錄指定庫 (多庫用逗號分隔)
binlog-ignore-db = mysql,test   # 忽略系統庫

重啟：service mysql restart

2. 建立複寫帳號

-- MySQL 4.0.2+
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

-- 4.0.2 以前
GRANT FILE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

FLUSH PRIVILEGES;

3. Master 鎖表、備份、記錄位置

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;
-- 記錄 File (mysql-bin.003) 與 Position (73)

-- 備份方式二選一：
-- A. 直接打包 datadir 目錄 (速度快、含索引)
-- B. mysqldump --master-data=2 (含 CHANGE MASTER TO 陳述式)

UNLOCK TABLES;

4. Slave 設定 (my.cnf / my.ini)

[mysqld]
server-id = 2                   # 唯一，不可與 Master 相同
master-host = 192.168.1.100     # Master IP
master-port = 3306
master-user = repl
master-password = slavepass
master-connect-retry = 60
report-host = slave-hostname

重啟 Slave 服務。

5. Slave 匯入備份、設定同步點、啟動

-- 若啟動時未加 --skip-slave-start
STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='slavepass',
    MASTER_LOG_FILE='mysql-bin.003',
    MASTER_LOG_POS=73;

START SLAVE;

6. 驗證同步狀態

SHOW SLAVE STATUS\G;
-- 關鍵：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

SHOW PROCESSLIST\G 可見 system user 執行 Slave_IO/Slave_SQL 執行緒。

7. 日志過大清理流程 (不中斷複寫)

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

常見問題

• Slave_IO_Running: No → 檢查網路、帳號、防火牆、MASTER_LOG_FILE/POS。
• Slave_SQL_Running: No → 查 Last_SQL_Error，手動修正衝突後 START SLAVE。
• 延遲大 → 檢查 Slave 硬碟 IO、innodb_flush_log_at_trx_commit、長交易。