MySQL 5.x 主從複製部署實戰日誌：從踩坑到穩定運行

前言

MySQL 主從複製是讀寫分離、災備、數據分發的基石架構。本文基於真實部署日誌（MySQL 5.0.18/5.0.19，Linux），完整記錄配置流程、遇到的經典錯誤（Relay Log 錯誤、Schema 不一致、Binlog 位點偏移）及解決方案，作為生產環境部署參考。

環境拓撲

步驟一：Master 配置

1. 編輯 /etc/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = test          # 僅同步 test 資料庫（可選，建議生產環境指定）
binlog-format = ROW          # 推薦 ROW 格式，減少主從不一致風險
expire-logs-days = 7         # 自動清理 7 天前 Binlog
max-binlog-size = 100M
sync-binlog = 1              # 每次提交刷盤，最高數據安全（性能折中可設 100-1000）
innodb-flush-log-at-trx-commit = 1

2. 建立複製專用賬號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.100.234' IDENTIFIED BY 'StrongReplPass123!';
FLUSH PRIVILEGES;

3. 重啟 Master 並記錄初始 Binlog 位點

systemctl restart mysqld
mysql -e "SHOW MASTER STATUS;"
-- 記錄 File (如 mysql-bin.000001) 和 Position (如 154)

步驟二：Slave 配置

1. 編輯 /etc/my.cnf

[mysqld]
server-id = 2
relay-log = relay-bin
relay-log-index = relay-bin.index
log-slave-updates = 1        # 若 Slave 需作為其他節點的 Master
read-only = 1                # 防止誤寫入
replicate-do-db = test       # 僅同步 test，與 Master binlog-do-db 對應
# replicate-ignore-db = mysql,information_schema,performance_schema

2. 重啟 Slave 並配置複製連接

systemctl restart mysqld
mysql -e "
CHANGE MASTER TO
    MASTER_HOST = '192.168.100.231',
    MASTER_USER = 'repl_user',
    MASTER_PASSWORD = 'StrongReplPass123!',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154,
    MASTER_CONNECT_RETRY = 10,
    MASTER_RETRY_COUNT = 86400;
START SLAVE;
"

3. 驗證複製狀態

mysql -e "SHOW SLAVE STATUS\G"
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0
-- Last_IO_Error / Last_SQL_Error: 空

實戰踩坑記錄與解決

坑 1：SQL 線程報錯「Table 'passport.loginuser' doesn't exist」

現象：Slave_IO_Running=Yes，Slave_SQL_Running=No，錯誤碼 1146。

原因分析：Master 的 Binlog 包含 passport 庫的 DML，但 Slave 未建立該庫，或 replicate-do-db 未包含 passport。日誌顯示 Default database: 'passport'，說明 Master 執行時當前庫是 passport。

解決：

1. 在 Slave 建立缺失庫：CREATE DATABASE passport;
2. 或調整 replicate-wild-do-table=passport.%（需重啟）
3. 若數據已不同步，需重新同步該庫數據（見坑 3）。

坑 2：手動拷貝 Master 數據目錄導致 Relay Log 混亂

操作：直接 scp -r /var/lib/mysql/* slave:/var/lib/mysql/。

後果：Slave 的 auto.cnf (server_uuid) 被覆蓋為 Master 的 UUID，導致主從 UUID 衝突；Relay Log 位點與 Master Binlog 位點不匹配，SQL 線程無法解析。

正確做法：使用 mysqldump + 導入 或 Percona XtraBackup 進行物理備份恢復，保證 UUID 獨立、位點一致。

坑 3：測試時 Slave 報錯「Can't create database 'test'; database exists」

現象：SQL 線程中斷，錯誤碼 1007。

原因：Master Binlog 開頭包含 CREATE DATABASE test，Slave 因之前手動建庫/拷貝已存在該庫，重放失敗。

解決：

1. 在 Slave 刪除受影響的庫：DROP DATABASE test;
2. 重置 Slave：STOP SLAVE; RESET SLAVE ALL; -- 5.1+ 版本
3. 重新配置 CHANGE MASTER TO ... 從有效位點開始（或重新導入基準數據）。
4. 啟動：START SLAVE;

坑 4：版本差異導致語法不兼容

Master 5.0.19，Slave 5.0.18。雖然通常向下兼容，但建議主從版本保持一致，避免 Binlog 事件解析異常。

生產級優化配置建議

# Master 額外優化
binlog-format = ROW
binlog-row-image = MINIMAL        # 5.6+ 減少 Binlog 體積
gtid-mode = ON                    # 5.6+ 啟用 GTID，簡化故障切換
enforce-gtid-consistency = ON
master-info-repository = TABLE    # 將主從元數據存表，更可靠
relay-log-info-repository = TABLE

# Slave 額外優化
slave-parallel-workers = 4        # 5.6+ 多線程複製（庫級並行）
slave-parallel-type = LOGICAL_CLOCK  # 5.7+ 基於邏輯時鐘並行，無庫限制
slave-preserve-commit-order = 1   # 保證提交順序一致性
relay-log-recovery = ON           # 啟動時自動修復損壞 Relay Log

監控與告警指標

• Seconds_Behind_Master > 300 秒告警
• Slave_IO_Running / Slave_SQL_Running 非 Yes 立即告警
• Relay_Log_Space 過大（> 10G）可能表示 SQL 線程阻塞
• Master Binlog 增長速率 異常暴增檢查大事務/誤操作

結語

MySQL 主從複製「配置易、調優難、排錯更難」。本文從真實部署日誌提煉出的核心經驗：版本一致、GTID 模式、ROW 格式、專用賬號、嚴格的 replicate-do-db、標準化備份恢復流程、自動化監控告警。避免手動拷貝數據目錄、隨意變更 server-id/UUID、忽略 Binlog 位點管理，是穩定運行的關鍵。建議引入 Orchestrator、MHA、ProxySQL 等中間件構建高可用架構，將人工運維轉為自動化故障切換。