Windows 下 MySQL 主從同步完整部署指南：配置、授權、驗證與故障切換

架構與效益

• 讀寫分離：主庫寫、從庫讀，提升查詢吞吐
• 即時備份：從庫作熱備份，防單點故障
• 版本建議：主從同版本 (5.5+)，確保 Binlog 格式相容

環境資訊 (範例)

主庫配置 (my.ini → [mysqld] 區段)

server-id=1
log-bin=C:\Program Files\MySQL\MySQL Server 5.5\mysql-bin
binlog-do-db=test1          # 只同步指定庫，多庫多行
# binlog-ignore-db=mysql    # 或排除系統庫

重啟服務：`net stop mysql && net start mysql`，驗證產生 `mysql-bin.000001`、`mysql-bin.index`。

主庫建立同步帳號

GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* 
TO 'slave'@'192.168.0.104' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

記錄同步起始點

SHOW MASTER STATUS;
-- 記錄 File (如 mysql-bin.000001) 與 Position (如 107)

從庫配置 (my.ini → [mysqld])

server-id=2
replicate-do-db=test1
# replicate-ignore-db=mysql

重啟服務。

從庫建立同步連線

1. 測試連通性：`mysql -u slave -proot -h 192.168.0.102`
2. 配置主庫連線參數：

   
       CHANGE MASTER TO 
           MASTER_HOST='192.168.0.102',
           MASTER_USER='slave',
           MASTER_PASSWORD='root',
           MASTER_LOG_FILE='mysql-bin.000001',  -- 用 SHOW MASTER STATUS 值
           MASTER_LOG_POS=107;
       

3. 若報 `Slave thread running` 錯誤：`STOP SLAVE;` 後再執行 CHANGE MASTER
4. 啟動同步：`START SLAVE;`

驗證同步狀態

SHOW SLAVE STATUS\G
-- 關鍵指標：
Slave_IO_Running: Yes      -- IO 線程讀取 Binlog 正常
Slave_SQL_Running: Yes     -- SQL 線程重放正常
Seconds_Behind_Master: 0   -- 延遲秒數，0 為同步

常見故障與處理

主庫宕機：從庫提升為主 (手動故障切換)

1. 所有從庫 `STOP SLAVE IO_THREAD;` 等待 `Has read all relay log`
2. 選擇 `master.info` 中 `Position` 最大 (最新) 的從庫作新主
3. 新主：`STOP SLAVE; RESET MASTER;` 刪除 `master.info`、`relay-log.info`
4. 修改 `my.ini`：啟用 `log-bin`、註解 `read-only`/`replicate-*`、重啟
5. 其他從庫：`STOP SLAVE; CHANGE MASTER TO MASTER_HOST='新主IP'; START SLAVE;`

雙主 (雙向同步) 關鍵設定

-- 兩庫 my.ini 均加入：
auto_increment_increment = 2
auto_increment_offset = 1   -- 主庫1
auto_increment_offset = 2   -- 主庫2
log-slave-updates = 1       -- 將重放事件寫入自身 Binlog，傳遞給下游

應用層需路由寫入：奇數 ID 寫主庫1，偶數 ID 寫主庫2，避免主鍵衝突。

生產環境建議

• 使用 GTID (MySQL 5.6+) 簡化故障切換與拓撲變更
• 監控 `Seconds_Behind_Master`、Binlog 檔案大小、磁碟空間
• 定期 `SHOW SLAVE STATUS` 巡檢、模擬故障切換演練
• 關鍵業務考慮 MHA、Orchestrator、Group Replication (5.7+) / InnoDB Cluster (8.0+) 自動化高可用

MySQL 主從同步成熟穩定，配合讀寫分離中間件 (ProxySQL、MyCat、ShardingSphere) 可構建高擴展讀寫分離架構。