複寫三線程模型 (核心架構)
Master Slave
┌─────────────┐ ┌─────────────┐
│ Client │ │ I/O Thread │──► 讀取 Master Binlog
│ (寫入) │ │ │ 寫入 Relay Log
└──────┬──────┘ └──────┬──────┘
│ │
▼ ▼
┌─────────────┐ ┌─────────────┐
│ Binlog │ Binlog Dump Thread │ Relay Log │
│ (Binary │ ◄──────────────────────── │ (Relay │
│ Log) │ 發送 Binlog Event │ Log) │
└─────────────┘ └──────┬──────┘
│
▼
┌─────────────┐
│ SQL Thread │──► 重放 Relay Log
│ │ 執行事務
└─────────────┘
關鍵位點對照表 (SHOW SLAVE STATUS)
| 欄位 | 含義 | 用途 |
|---|
| Master_Log_File / Read_Master_Log_Pos | I/O 線程已讀取到的 Master Binlog 位置 | 監控複寫延遲、故障恢復定位 |
| Relay_Master_Log_File / Exec_Master_Log_Pos | SQL 線程已執行到的 Master Binlog 位置 | 資料一致性驗證、提升 Slave 為 Master 時的基準 |
| Relay_Log_File / Relay_Log_Pos | SQL 線程已讀取到的 Relay Log 位置 | Relay Log 損壞時重建依據 |
| Seconds_Behind_Master | SQL 線程落後 Master 的秒數 (粗略) | 延遲告警閾值 |
標準化部署流程 (GTID 模式，MySQL 5.6+)
1. Master 配置
[mysqld]
server_id = 100
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
log_slave_updates = 1 # 若 Master 也是 Slave (鏈式複寫)
2. 建立複寫帳號
CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
3. Slave 配置
[mysqld]
server_id = 200
log_bin = mysql-bin
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
skip_slave_start = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
4. 資料同步 (三種方式)
| 方式 | 適用場景 | 指令 |
|---|
| mysqldump --single-transaction --master-data=2 | 小/中型庫 (<100GB)、InnoDB 為主 | 邏輯備份、跨版本、壓縮傳輸 |
| Percona XtraBackup (物理熱備) | 大型庫 (>100GB)、追求速度、不影響寫入 | xtrabackup --backup --target-dir=/backup --user=root --password=... |
| Clone Plugin (8.0.17+) | 同版本、網路佳、自動化 | CLONE INSTANCE FROM 'repl'@'master':3306; |
5. 啟動複寫 (GTID 自動定位)
CHANGE MASTER TO
MASTER_HOST='10.130.30.212',
MASTER_USER='repl',
MASTER_PASSWORD='StrongPass123!',
MASTER_AUTO_POSITION=1; -- GTID 核心，無需指定 File/Pos
START SLAVE;
SHOW SLAVE STATUS\G
故障處理決策樹
SHOW SLAVE STATUS\G
│
├─ Slave_IO_Running = No
│ │
│ ├─ Last_IO_Error: "Could not read binlog" → Master Binlog 損壞
│ │ → mysqlbinlog 驗證 → 重新同步或跳過 (SET GLOBAL sql_slave_skip_counter=1)
│ │
│ ├─ Last_IO_Error: "Access denied" → 帳號/密碼/防火牆
│ │ → 修正 CHANGE MASTER TO
│ │
│ └─ Last_IO_Error: "Timeout" → 網路/帶寬/防火牆
│ → 檢查連通性、調大 net_read_timeout
│
└─ Slave_SQL_Running = No
│
├─ Last_SQL_Error: "Duplicate entry" (1062) → 主鍵衝突
│ → 方案 A: SET GLOBAL sql_slave_skip_counter=1; START SLAVE; (資料可能不一致)
│ → 方案 B: pt-table-checksum/pt-table-sync 修復 (推薦)
│
├─ Last_SQL_Error: "Foreign key constraint fails" → FK 衝突
│ → 檢查父表資料、暫時禁用 FK、或重新同步
│
└─ Last_SQL_Error: "Table doesn't exist" → DDL 未同步/表結構不一致
→ 對比結構、補齊 DDL 或重建 Slave
高可用架構演進
| 架構 | 特點 | 工具/實現 | RTO |
|---|
| MHA (Master High Availability) | Perl、腳本化、支援多候選、自動遷移 | MHA Manager + Node | 10-30s |
| Orchestrator | Go、Web UI、拓撲視覺化、支援 GTID、自動故障轉移 | Orchestrator + Consul/etcd | 5-15s |
| Group Replication (InnoDB Cluster) | 原生、Paxos 協議、多主/單主、自動選主、一致性強 | MySQL Shell + Router | 0-5s |
| ProxySQL + Keepalived | 流量入口、查詢路由、熔斷、讀寫分離、後端健康檢查 | ProxySQL Cluster + VIP | 應用層感知 |
進階技巧：異構引擎/索引/字元集複寫
- 引擎異構：Master InnoDB (事務/行鎖) → Slave MyISAM/ColumnStore (唯讀分析/壓縮)。
SET SESSION sql_log_bin=0; ALTER TABLE ... ENGINE=MyISAM; 在 Slave 執行。
- 索引異構：Master 最小索引 (寫入快) → Slave 針對查詢模式建覆蓋索引/全文索引。
- 字元集異構：Master utf8mb4 → Slave latin1 (舊系統相容)，需應用層轉碼或
CHARSET 轉換視圖。
監控黃金指標
# Prometheus mysqld_exporter 關鍵指標
mysql_slave_status_slave_io_running{channel=""} # 1=正常
mysql_slave_status_slave_sql_running{channel=""} # 1=正常
mysql_slave_status_seconds_behind_master{channel=""} # 延遲秒數
mysql_slave_status_master_log_file / mysql_slave_status_read_master_log_pos
mysql_global_status_binlog_commits / binlog_group_commits # 群組提交效率
複寫是高可用基石，但不是備份替代品。生產必備：GTID + 半同步 (rpl_semi_sync_master_enabled=1) + 物理備份 (XtraBackup) + 自動化故障轉移 + 定期 pt-table-checksum 一致性校驗。
