MySQL 主從複寫部署實戰：從零構建高可用資料同步架構

MySQL 主從複寫是實現資料庫讀寫分離、異地備份、高可用架構的基石。本文記錄一套在 Windows Server 2003 + MySQL 4.1.20 環境驗證通過的完整部署流程，涵蓋帳號授權、配置調整、資料初始化、同步驗證與故障排查全生命週期。

架構拓撲與環境規劃

部署步驟全流程

步驟 1：主庫建立複寫專用帳號

-- 僅允許從庫 IP 連線，最小權限原則
GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* 
    TO 'backup'@'192.168.100.2' IDENTIFIED BY '1234';
FLUSH PRIVILEGES;

-- MySQL 4.1+ 密碼雜湊相容性處理
SET PASSWORD FOR 'backup'@'192.168.100.2' = OLD_PASSWORD('1234');

權限說明： REPLICATION SLAVE 核心權限；RELOAD 支援 FLUSH TABLES；SUPER 允許 CHANGE MASTER TO。

步驟 2：資料一致性基線建立（關鍵）

1. 主庫停寫：FLUSH TABLES WITH READ LOCK;
2. 記錄主庫位置：SHOW MASTER STATUS\G → 記下 File、Position
3. 完整拷貝主庫資料目錄至從庫（含 ibdata1、.frm、.MYD、.MYI 等）
4. 主庫解鎖：UNLOCK TABLES;

⚠️ **全程禁止雙方寫入**，確保基線一致。

步驟 3：主庫配置 my.ini

[mysqld]
server-id = 1
log-bin = c:log-bin.log
# 可選：指定同步/忽略的庫（多庫重複寫行）
binlog-do-db = backup_db
binlog-ignore-db = mysql

重啟主庫，開始產生二進制日誌。

步驟 4：從庫配置 my.ini

[mysqld]
server-id = 2
master-host = 192.168.100.1
master-user = backup
master-password = 1234
master-port = 3306
# 可選：只同步指定庫
replicate-do-db = backup_db

重啟從庫，自動連線主庫拉取 binlog 重放。

步驟 5：驗證同步狀態

-- 從庫執行
SHOW SLAVE STATUS\G

關鍵指標均為 Yes 即成功：

• Slave_IO_Running: Yes — IO 線程正常連線主庫、拉取 binlog
• Slave_SQL_Running: Yes — SQL 線程正常重放 relay log

核心管理指令速查

常見故障排查實戰

故障 1：Slave_IO_Running: Connecting

• 檢查網路連通性：telnet 192.168.100.1 3306
• 檢查主庫防火牆/安全組放行 3306
• 驗證帳號密碼：主庫 SELECT User, Host FROM mysql.user WHERE User='backup'
• 查看從庫錯誤日誌：mysqld.log / hostname.err

故障 2：Slave_SQL_Running: No

• SHOW SLAVE STATUS\G 查看 Last_SQL_Error
• 常見原因：主鍵衝突、表結構不一致、外鍵約束失敗、觸發器錯誤
• 緊急處理：STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;（需確認業務影響）
• 根治：修正表結構、補齊缺失資料、重做基線同步

故障 3：Seconds_Behind_Master 持續增長

• 從庫硬體資源不足（CPU/磁碟 I/O/記憶體）
• 大事務阻塞（主庫大批次 UPDATE/DELETE）
• 長查詢佔用鎖資源
• 對策：升級從庫規格、主庫拆分大事務、優化慢查詢、啟用並行複寫 (MySQL 5.6+ slave_parallel_workers)

關鍵避坑經驗總結

1. 配置檔案編碼一致性：主從 my.ini 編碼、換行符必須一致，否則參數不生效
2. 基線資料必須一致：任何跳過「停主庫→拷貝→啟從庫」步驟的捷徑，最終都會以同步錯誤代價償還
3. 修改從庫配置必刪 master.info：STOP SLAVE; RESET SLAVE; 會清理，否則舊配置覆蓋新配置
4. 版號必須一致：主從版本不一致導致 binlog 格式不兼容、GTID 不支援等疑難雜症
5. 監控要覆蓋全鏈路：IO 線程、SQL 線程、延遲秒數、relay log 磁碟佔用、錯誤計數

架構演進建議

• 單主多從 + 讀寫分離：ProxySQL/MaxScale 路由，讀請求分發至從庫
• 雙主 (Master-Master)：雙向複寫，需解決自增 ID 衝突 (auto_increment_increment/offset) 與雙寫衝突（業務層路由）
• GTID 複寫 (5.6+)：MASTER_AUTO_POSITION=1 簡化故障轉移
• 半同步複寫 (rpl_semi_sync)：主庫提交前確認至少一從庫收到 binlog，降低資料丟失風險
• 自動故障轉移：MHA / Orchestrator / MySQL InnoDB Cluster 實現秒級切換

總結

MySQL 主從複雜度不在配置，而在於**資料一致性基線建立**、**持續監控體系**、**故障快速定位與恢復預案**。建議將部署流程標準化為 Ansible Playbook / Shell 腳本，配合定期演練切換，才能在故障發生時從容應對。