MySQL主從復制架構深度解析：從單向同步到雙主高可用構建全流程

核心架構：二進制日志驅動的異步複製

• Master：寫入節點，啟用log-bin記錄所有數據變更
• Slave：讀取節點，通過I/O線程拉取Binlog，SQL線程重放
• 協調文件：master.info（連接參數+復制坐標）、relay-log.info（中繼日志進度）

版本兼容性鐵律

黃金法則：同版本部署，優先升級Slave再升級Master，保持Master版本號 ≥ Slave版本號。

標準主從部署全流程

Phase 1：初始同步（冷備份法通用）

1. Master鎖表防寫：FLUSH TABLES WITH READ LOCK;
2. 獲取Binlog坐標：SHOW MASTER STATUS; 記錄File、Position
3. 打包數據目錄：tar czf /backup/mysql_snapshot.tar.gz /var/lib/mysql
4. 釋放鎖：UNLOCK TABLES;
5. 傳輸解壓至Slave數據目錄，修正權限

Phase 2：Master配置

[mysqld]
server-id=1                    # 全局唯一，1..2^32-1
log-bin=mysql-bin              # 啟用二進制日志
binlog-format=ROW              # 推薦ROW格式，避免非確定性函數導致不一致
sync_binlog=1                  # 每次提交刷盤，防數據丟失
# 可選：過濾不需同步的庫
binlog-ignore-db=mysql
binlog-ignore-db=test

重啟生效。若已有Binlog，執行RESET MASTER;清理舊日志重新開始。

Phase 3：建立復制賬號

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass2024!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Phase 4：Slave配置與啟動

[mysqld]
server-id=2
relay-log=mysql-relay-bin      # 顯式命名避免主機名變更導致中斷
relay-log-index=mysql-relay-bin.index
read-only=1                    # 防止誤寫入Slave

# 啟動後動態配置（MySQL 5.6+ 推薦 CHANGE MASTER TO 替代配置文件）
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='repl',
    MASTER_PASSWORD='StrongPass2024!',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154,
    MASTER_CONNECT_RETRY=30,
    MASTER_RETRY_COUNT=100000;

啟動復制：START SLAVE; 驗證：SHOW SLAVE STATUS\G 確認Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0。

進階拓撲與管理

級聯復制（Master → Slave → Slave）

中間節點同時配置log-bin與log-slave-updates=1，將重放的事務寫入自身Binlog，下游Slave指向中間節點。

雙主互備（Active-Active 需應用層分流）

# Node A 額外配置
server-id=1
log-bin=mysql-bin
log-slave-updates=1
auto-increment-increment=2
auto-increment-offset=1

# Node B 額外配置
server-id=2
log-bin=mysql-bin
log-slave-updates=1
auto-increment-increment=2
auto-increment-offset=2

關鍵：auto_increment_increment/offset防主鍵衝突；應用層必須按業務分流寫入（如按用戶ID奇偶分庫），避免雙寫衝突。

監控與運維核心命令

故障處理速查

• IO線程斷連：檢查網絡/防火牆/賬號密碼/MASTER_CONNECT_RETRY
• SQL線程報錯：SHOW SLAVE STATUS查看Last_SQL_Error，若可跳過：STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE;
• 數據不一致：使用pt-table-checksum/pt-table-sync巡檢修復
• Relay日志損壞：STOP SLAVE; RESET SLAVE; CHANGE MASTER TO ...; START SLAVE; 重拉全量