MySQL 主從復制原理與配置全攻略：從二進制日志到 GTID 的完整實施指南

核心原理：三線程協作模型

主庫                          從庫
┌─────────────┐              ┌─────────────┐
│ Binlog Dump │ ──網絡傳輸──→ │   I/O 線程  │ ← 讀取 Binlog 寫入 Relay Log
│   線程      │              │             │
└─────────────┘              └──────┬──────┘
                                    │
                                    ▼
                            ┌─────────────┐
                            │  SQL 線程   │ ← 讀取 Relay Log 重放事務
                            │             │
                            └─────────────┘

• Binlog Dump 線程：主庫為每個從庫創建一個，發送 Binlog 事件。
• I/O 線程：從庫連接主庫，請求 Binlog，寫入本地 Relay Log。
• SQL 線程：從庫讀取 Relay Log，順序執行事務，保證數據一致性。

關鍵文件與狀態持久化

備份策略： 備份從庫時必須包含 這兩個 info 文件 + Relay Log，恢復後才能無縫續傳。

標準配置步驟（基於文件位置）

1. 主庫配置

[mysqld]
server_id = 1                    # 全局唯一 1..2^32-1
log_bin = mysql-bin              # 啟用二進制日志
binlog_format = ROW              # 推薦 ROW 格式
sync_binlog = 1                  # 事務提交強制刷盤，防丟失
innodb_flush_log_at_trx_commit = 1
gtid_mode = ON                   # 5.6+ 推薦啟用 GTID
enforce_gtid_consistency = ON

2. 創建復制賬號

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

3. 獲取主庫快照與 Binlog 位置

-- 僅 MyISAM 表可用
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記下 File, Position
-- 打包數據目錄傳輸到從庫
UNLOCK TABLES;

-- InnoDB 表：使用 mysqldump --master-data=2 --single-transaction
mysqldump --master-data=2 --single-transaction -u root -p --all-databases > full.sql

4. 從庫配置

[mysqld]
server_id = 2                    # 唯一，≠ master
relay_log = relay-bin            # 可選，指定 relay log 名稱
read_only = ON                   # 防止誤寫入
gtid_mode = ON
enforce_gtid_consistency = ON

5. 建立復制關係

-- 文件位置模式（傳統）
CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongReplPass!',
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=471632;

-- GTID 模式（5.6+ 推薦，自動定位）
CHANGE MASTER TO
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongReplPass!',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

6. 驗證狀態

SHOW SLAVE STATUS\G
-- 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 471632
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 471632

GTID 模式優勢（5.6+ 強烈推薦）

• 自動定位：無需手動指定 File/Position，故障切換、重新配置極簡單。
• 事務級幂等：同一 GTID 只會執行一次，防止重複執行。
• 多源復制/級聯復制：原生支持複雜拓撲。

常見問題與排查

高可用架構演進

1. MHA / Orchestrator：自動故障切換、主庫遷移。
2. MGR (MySQL Group Replication)：原生多主 Paxos 協議，強一致性集群。
3. ProxySQL / MyCat：讀寫分離、路由、熔斷中間件。