MySQL 主從雙機熱備完整部署指南：版本需求、my.cnf 設定、資料同步、日誌輪換與故障處理

MySQL 原生不支援增量備份，大量資料備份極耗時。透過「主從複寫」機制，將主庫的 Binlog 即時同步到從庫，實現熱備份與讀寫分離。以下為生產環境可用的完整部署流程。

前置條件

• 主從版本皆 ≥ 3.23；從庫版本 ≥ 主庫版本（不可低於主庫）。
• 主庫啟用 log-bin（複寫基礎）。
• 雙方 server-id 唯一（主庫通常設 1，從庫設 2, 3...）。

1. 主庫設定 (my.cnf/my.ini)

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

重啟 MySQL 後，建立複寫專用帳號：

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

4.0.2 以前版本請用 GRANT FILE ON *.* ...

2. 主庫鎖表、備份、記錄 Binlog 位置

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;
# 記下 File (如 mysql-bin.003) 與 Position (如 73)
# 備份方式：tar 打包 datadir 或 mysqldump --master-data
UNLOCK TABLES;

3. 從庫設定 (my.cnf)

[mysqld]
server-id=2
master-host=db-master.mycompany.com
master-port=3306
master-user=repl
master-password=slavepass
master-connect-retry=60
report-host=db-slave.mycompany.com

將主庫備份資料導入從庫後啟動 MySQL（若未加 --skip-slave-start 需手動 SLAVE STOP）。

4. 從庫指定同步起點並啟動

CHANGE MASTER TO
  MASTER_HOST='master_host_name',
  MASTER_USER='replication_user_name',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='mysql-bin.003',   # 主庫 SHOW MASTER STATUS 的 File
  MASTER_LOG_POS=73;                 # 主庫 SHOW MASTER STATUS 的 Position

SLAVE START;
SHOW SLAVE STATUS\G;  # 確認 Slave_IO_Running & Slave_SQL_Running 皆為 Yes

5. 日誌過大時的輪換清理流程

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

常用監控指令

SHOW PROCESSLIST\G;  # 檢查是否有 Binlog Dump 執行緒
SHOW SLAVE STATUS\G; # Seconds_Behind_Master = 0 表示同步正常

提醒： 生產環境建議啟用 sync_binlog=1、innodb_flush_log_at_trx_commit=1 確保資料不遺失；並配合監控系統（如 Prometheus + mysqld_exporter）即時告警。