MySQL 5 主從複寫完整部署實戰：從配置到故障排除全紀錄

環境規劃

Step 1：Master 配置

編輯 /etc/my.cnf（或 /etc/init.d/mysqld 啟動腳本參數）：

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = test          # 僅同步 test 資料庫

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

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.234' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

Step 2：Slave 配置

[mysqld]
server-id = 2
master-host = 192.168.100.231
master-user = repl
master-password = repl_pass
replicate-do-db = test       # 僅應用 test 資料庫的 relay log

重啟 Slave。

Step 3：初始化同步與驗證

1. Master 鎖表備份：
   FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p --master-data=2 test > test_master.sql
UNLOCK TABLES;
2. 將 test_master.sql 複製至 Slave 匯入
3. Slave 啟動複寫：
   START SLAVE;
4. 檢查狀態：
   SHOW SLAVE STATUS\G
   關鍵指標：Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0

實戰踩坑與解決

坑 1：Slave SQL Thread 報錯 Table 'passport.loginuser' doesn't exist

原因：Master 早期 Binlog 包含 passport 庫操作，但 Slave 只同步 test 庫，導致 Relay Log 執行失敗。
解決：在 my.cnf 加入 replicate-do-db=test 並重啟 Slave，或使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 跳過錯誤事件（謹慎使用）。

坑 2：測試插入後 Slave 未同步，報 Can't create database 'test'; database exists

原因：人工複製資料目錄導致 Master Binlog 含 CREATE DATABASE test，Slave 重放衝突。
解決：刪除 Slave 上手工複製的 test 庫，重新 START SLAVE 讓 Binlog 自然重放建表。

坑 3：Binlog 格式不一致

Master/Slave 版本微異（5.0.19 vs 5.0.18）可能導致 Binlog 事件解析差異。建議統一版本，或升級至 5.1+ 使用 Row-based Replication（RBR）規避。

常用運維指令速查

生產環境建議

• 啟用 sync_binlog=1、innodb_flush_log_at_trx_commit=1 保證零遺失
• 部署監控：Seconds_Behind_Master > 300 秒告警
• 定期演練 MASTER HA 切換（MHA、Orchestrator、GTID 自動故障轉移）

遵循標準化部署流程、拒絕手工複製資料目錄、建立完善監控體系，是 MySQL 主從複穩定運行的三大基石。