MySQL 主從同步備份配置完整指南

MySQL 主從同步備份配置完整指南

主從複製是 MySQL 高可用架構的基石，透過將主庫的二進制日志同步到從庫實現數據冗災與讀寫分離。本文以雙節點環境為例，詳細記錄配置步驟與關鍵參數說明。

一、環境準備

版本原則： 主從版本最好一致；若不一致，從庫版本必須高於主庫，確保兼容性。

二、配置 Master 服務器

1. 編輯 my.cnf 啟用二進制日志

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-do-db=extmail
binlog-ignore-db=mysql,test

• log-bin：啟用二進制日志（必須）。
• server-id：唯一正整數，範圍 1 ~ 2³²-1。
• binlog-do-db：需同步的資料庫。
• binlog-ignore-db：排除同步的資料庫，多個用逗號分隔。

2. 建立同步專用帳號

GRANT REPLICATION SLAVE ON *.* 
TO 'abc'@'192.0.0.2' IDENTIFIED BY '123';

僅授予 REPLICATION SLAVE 權限，限制來源 IP，遵循最小權限原則。

3. 重啟 MySQL 服務

service mysql restart

4. 備份主庫數據並記錄位點

mysqldump --master-data extmail > extmail_backup_20071120.sql

--master-data 會在備份文件中自動記錄當前 binlog 文件名與位置，從庫導入後可直接定位同步起點。

5. 查看 Master 狀態

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 79       | extmail      | mysql,test       |
+------------------+----------+--------------+------------------+

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

三、配置 Slave 服務器

1. 編輯 my.cnf

[mysqld]
server-id=2
master-host=192.0.0.1
master-port=3306
master-user=abc
master-password=123
master-connect-retry=60

• server-id：不可與 Master 及其他 Slave 重複。
• master-connect-retry：連線失敗重試間隔（秒）。

2. 導入主庫備份數據

mysqladmin create extmail
mysql extmail < extmail_backup_20071120.sql

3. 重啟 MySQL 服務

4. 停止 Slave、設定同步參數、啟動 Slave

SLAVE STOP;
CHANGE MASTER TO
  MASTER_HOST='192.0.0.1',
  MASTER_USER='abc',
  MASTER_PASSWORD='123',
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=79;
SLAVE START;

MASTER_LOG_FILE 與 MASTER_LOG_POS 需與 Master 端 SHOW MASTER STATUS 結果一致。

5. 驗證同步狀態

SHOW PROCESSLIST;

觀察 system user 執行緒狀態為 Waiting for master to send event 即代表同步正常運行。

常見問題排查

• 防火牆阻擋 3306 埠
• server-id 衝突
• 網路不通或帳號密碼錯誤
• binlog 格式不一致（建議統一 ROW 格式）