MySQL 資料同步配置實戰：主從模式與雙主互備

版本與環境

• MySQL 4.0.4-beta (教學用舊版，現代版本 5.7+/8.0 概念相同，參數以 CHANGE MASTER TO 為主)
• OS：Windows 2000 (示範 my.ini)，Linux 同理修改 /etc/my.cnf
• 兩台伺服器：A (10.10.10.22)、B (10.10.10.53)

前置作業：雙邊建立同步帳號與資料庫

-- 在 A 執行
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.10.10.53' IDENTIFIED BY '1234';
CREATE DATABASE backup;

-- 在 B 執行
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'10.10.10.22' IDENTIFIED BY '1234';
CREATE DATABASE backup;

注意：舊版用 GRANT FILE 不安全，現代版本請用 REPLICATION SLAVE。

架構一：單向主從 (A → B)

A (Master) 設定 my.ini

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=backup
# 可選：binlog-ignore-db=mysql,test

重啟 MySQL，執行 SHOW MASTER STATUS; 記下 File、Position。

B (Slave) 設定 my.ini

[mysqld]
server-id=2
master-host=10.10.10.22
master-user=backup
master-password=1234
master-port=3306
master-connect-retry=60
replicate-do-db=backup
# 可選：replicate-ignore-db=mysql,test

重啟 MySQL。MySQL 會在資料目錄產生 master.info 記錄連線資訊。

驗證

-- 在 B 執行
SHOW SLAVE STATUS\G
-- 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

架構二：雙主互備 (Dual Master / Master-Master)

在單向主從基礎上，讓 B 也啟用 Binlog 並成為 A 的 Slave。

額外設定 (雙邊皆需)

[mysqld]
server-id=1/2 (各自唯一)
log-bin=mysql-bin
log-slave-updates=1          # 關鍵：將從 Master 接收的變更寫入自己的 Binlog
auto-increment-increment=2   # 避免自增 ID 衝突
auto-increment-offset=1/2    -- A=1, B=2
binlog-do-db=backup
replicate-do-db=backup

雙向建立複寫

-- A 上執行 (指向 B)
CHANGE MASTER TO MASTER_HOST='10.10.10.53', MASTER_USER='backup', MASTER_PASSWORD='1234', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

-- B 上執行 (指向 A)
CHANGE MASTER TO MASTER_HOST='10.10.10.22', MASTER_USER='backup', MASTER_PASSWORD='1234', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

關鍵參數深度解析

Windows 環境特殊說明

• 配置檔為 my.ini (通常在 C:\Windows\ 或 MySQL 安裝目錄)，winmysqladmin.exe 會將 C:\my.cnf 改名為 mycnf.bak 並建立 C:\Windows\my.ini。
• 服務管理：net start mysql / net stop mysql 或服務管理員。
• 路徑分隔符：log-bin=C:/mysqlbak/mysqlbin 使用正斜線或雙反斜線。

故障處理與維運建議

1. 修改 Slave 參數必須先 RESET SLAVE：STOP SLAVE; RESET SLAVE ALL; -- 刪除 master.info、relay-log.info 再重新 CHANGE MASTER TO。
2. 監控指標：Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Relay_Log_Space。
3. 資料一致性驗證：定期執行 pt-table-checksum (Percona Toolkit)。
4. 備份策略：Slave 停止 SQL 執行緒 (STOP SLAVE SQL_THREAD) 進行邏輯/物理備份，不影響 Master。
5. 升級注意：跨大版本升級需遵循官方升級路徑，複寫相容性請查閱 Release Notes。

現代替代方案

• GTID 複寫 (MySQL 5.6+)：無需手動指定 File/Position，自動定位，故障轉移更簡單。
• Group Replication / InnoDB Cluster (MySQL 5.7+)：原生多主同步、自動故障轉移、一致性保證。
• MariaDB Galera Cluster：同步多主、無延遲、支援寫入擴展。