MySQL 雙機熱備份配置詳細教程：主從複製架構實戰

架構原理

MySQL 無增量備份機制，大數據量備份耗時長。主從複製基於二進制日誌，將主庫變更即時同步至從庫，實現熱備份。版本要求：主從均 ≥ 3.23，從庫版本 ≥ 主庫版本。

一、主庫配置

1. 修改 my.cnf（[mysqld] 區段）

server-id=1
log-bin=mysql-bin
binlog-do-db=db_name1,db_name2
binlog-ignore-db=test,mysql

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';

3. 重啟主庫服務

service mysqld restart

4. 鎖表備份基線資料

mysql> FLUSH TABLES WITH READ LOCK;
# 方式一：物理複製 data 目錄（推薦，速度快）
# 方式二：mysqldump --master-data=1 dbname > dump.sql
mysql> UNLOCK TABLES;

5. 記錄主庫二進制日誌位置

mysql> SHOW MASTER STATUS\G;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

記錄 File 與 Position，從庫配置需用到。

二、從庫配置

1. 修改 my.cnf

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

2. 導入基線資料

將主庫備份的資料導入從庫 data 目錄。

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

# 若未加 --skip-slave-start 參數，進入後先停止
mysql> STOP SLAVE;

# 設定主庫連線資訊與同步起點
mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_host_name',
    -> MASTER_USER='replication_user_name',
    -> MASTER_PASSWORD='replication_password',
    -> MASTER_LOG_FILE='mysql-bin.003',
    -> MASTER_LOG_POS=73;

# 啟動同步執行緒
mysql> START SLAVE;

三、驗證同步狀態

mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

兩個 Yes 且延遲為 0 代表同步正常。

四、常見問題排查

• Slave_IO_Running: No：檢查網路連通性、帳號密碼、防火牆、master-host 解析
• Slave_SQL_Running: No：查看 Last_SQL_Error，常見主鍵衝突、字元集不一致
• 同步延遲大：優化從庫 innodb_flush_log_at_trx_commit、sync_binlog、增加 slave_parallel_workers（5.6+）