MySQL 主從複制與 SQL 備份完整實戰指南

MySQL 主從複制與 SQL 備份完整實戰指南

MySQL 伺服器的主從配置本身並不複雜，但實際環境往往不是從零開始，而是在別人已安裝好的 MySQL 之上操作，這會涉及版本差異、啟動檔案等問題。本文記錄完整配置流程與常用備份方法。

環境準備

• 主機 A: 192.168.1.100
• 從機 B: 192.168.1.101
• 兩台機器已安裝 MySQL（建議同版本、同網段、可互通）
• 先確認兩點：my.cnf 位置、MySQL 啟動/停止腳本位置

主從配置步驟

1. 主機授權從機連線權限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.1.101' IDENTIFIED BY '123456';

多台從機需重複執行。

2. 主機 my.cnf 配置

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
read-only = 0
binlog-do-db = test
binlog-ignore-db = mysql

3. 從機 my.cnf 配置

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
master-host = 192.168.1.100
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
replicate-do-db = test
replicate-ignore-db = mysql

4. 資料同步（最笨但最穩妥的方法）

1. 主機 A 執行 mysqldump 導出 test 資料庫為 test.sql
2. 從機 B 建立 test 資料庫並導入 test.sql

5. 重啟服務驗證

1. 先重啟主機 A MySQL，再重啟從機 B MySQL
2. 主機執行 SHOW MASTER STATUS\G
3. 從機執行 SHOW SLAVE STATUS\G
4. 檢查 Slave_IO_Running 和 Slave_SQL_Running 皆為 Yes
5. 在主機做 INSERT/UPDATE/DELETE 測試同步

二進制日誌備份與還原

在 my.ini 加入 log-bin=shenghua_binlog 啟用二進制日誌。常用指令：

SHOW MASTER STATUS;           -- 查看 binlog 名稱
SHOW BINLOG EVENTS;           -- 查看 binlog 寫入的資料
mysqlbinlog mysql-bin.000001 > backup.sql  -- 導出為 SQL

參考資源

• PHP MySQL 入門
• MySQL 主從配置詳解
• MySQL 二進制日誌備份