MySQL 主從複製讀寫分離集群配置實戰：架構原理、參數調優與故障處理
針對高併發場景，通過主從複製實現寫入集中、查詢分流，顯著降低主庫壓力並提升可用性。本文詳述 Windows 環境下雙節點搭建全流程，含雙主鍵衝突避免、同步監控與常見坑點。
架構原理
- Master：處理所有寫操作（INSERT/UPDATE/DELETE/DDL），生成 Binlog
- Slave：處理讀操作（SELECT），通過 I/O 線程拉取 Binlog、SQL 線程重放
- 核心優勢：讀寫分離減少鎖衝突、多副本提升數據安全、水平擴展讀能力
環境規劃
| 角色 | 系統 | IP | Server ID |
|---|
| Master | Win7 x64 | 10.10.2.33 | 1 |
| Slave | WinXP x86 | 10.10.2.157 | 2 |
Master 端配置 (my.ini [mysqld] 區)
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
binlog-do-db=test # 只同步 test 庫，或用 binlog-ignore-db 排除
auto_increment_offset=1 # 自增起始值
auto_increment_increment=2 # 自增步長（雙主時避免衝突）
expire_logs_days=2 # Binlog 保留 2 天
log_bin_trust_function_creators=1 # 允許創建函數
Slave 端配置 (my.ini [mysqld] 區)
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
auto_increment_offset=2
auto_increment_increment=2
建立複製賬號與授權
-- Master 執行
CREATE USER 'repl'@'10.10.2.157' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'10.10.2.157';
FLUSH PRIVILEGES;
初始化同步步驟
- Master 鎖表並記錄位點：
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記下 File (如 master-bin.000002) 和 Position (如 8042)
- 備份數據導入 Slave（mysqldump/xtrabackup/文件拷貝）
- Slave 配置同步起點並啟動：
CHANGE MASTER TO
MASTER_HOST='10.10.2.33',
MASTER_USER='repl',
MASTER_PASSWORD='repl_pass',
MASTER_LOG_FILE='master-bin.000002',
MASTER_LOG_POS=8042;
START SLAVE;
- Master 解鎖：
UNLOCK TABLES;
驗證同步狀態
-- Slave 執行
SHOW SLAVE STATUS\G
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (理想為 0，網絡延遲下允許小值)
常見故障與處理
| 現象 | 可能原因 | 排查命令 |
|---|
| IO_Running=No | 網絡不通/賬號密碼錯/防火牆/MASTER_LOG_FILE 錯誤 | SHOW SLAVE STATUS\G 看 Last_IO_Error |
| SQL_Running=No | 數據衝突/表結構不一致/觸發器錯誤 | 看 Last_SQL_Error，必要時跳過錯誤：SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; |
| Seconds_Behind_Master 偏大 | Slave 機器弱/大事務/長 SQL/網絡延遲 | 優化 Slave 硬件、拆分大事務、啟用並行複製 (5.6+) |
| 主鍵衝突 | 雙寫/自增未配置 offset/increment | 檢查 auto_increment_* 設置，嚴禁雙寫 |
進階優化建議
- GTID 模式 (5.6+)：
GTID_MODE=ON, ENFORCE_GTID_CONSISTENCY=ON，簡化故障切換
- 並行複製：
SLAVE_PARALLEL_WORKERS=4（基於庫級並行，5.6+）
- 半同步複製：
rpl_semi_sync_master_enabled=1，提升數據安全
- 監控告警：監控
Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Relay_Log_Space
生產環境建議：至少一主二從，從庫掛載不同業務查詢；核心業務讀寫分離中間件推薦 MyCat、ProxySQL 或 ShardingSphere。
