MySQL 高可用架構 ── Master–Slave 複寫

簡介

在現代網站與服務中，資料庫的可用性與可擴展性是系統成功的關鍵因素。單一 MySQL 伺服器若因硬體故障、維護或突發流量而無法服務，整個應用程式很快就會癱瘓。為了降低單點失效 (Single Point of Failure) 的風險，業界普遍採用 Master–Slave（主從）複寫 來建置高可用 (HA) 與讀寫分離的架構。

Master–Slave 複寫的核心概念是：所有寫入操作只在 Master（主庫）執行，Slave（從庫）會以非同步方式同步這些變更，從而提供額外的讀取節點、備援與容錯能力。即使 Master 暫時失效，仍可將其中一台 Slave 提升為新的 Master，快速恢復服務。

本篇文章將從概念說明、實作步驟、常見陷阱與最佳實踐，帶領讀者一步一步完成一套 基本的 MySQL Master–Slave 複寫，並探討它在真實環境中的應用場景。

---

核心概念

1. 複寫的工作原理

1. Binary Log (binlog)

   • Master 端把所有會改變資料的語句（INSERT、UPDATE、DELETE…）寫入 binlog，每筆事件都有唯一的 log file 與 position。

2. IO Thread（從庫）

   • 從 Master 讀取 binlog，並把原始檔寫到本機的 relay log。

3. SQL Thread（從庫）

   • 依序執行 relay log 中的事件，讓 Slave 的資料與 Master 同步。

重要：MySQL 預設的複寫是 非同步 的，這意味著寫入操作在 Master 完成後，Slave 仍可能尚未同步。若需要更嚴格的一致性，可考慮 半同步 或 組式複寫（Group Replication）。

---

2. 複寫模式分類

模式	特色	適用情境
基於語句的複寫 (Statement Based Replication, SBR)	把執行的 SQL 語句寫入 binlog。	大多數情況下都可使用，較省磁碟空間。
基於列的複寫 (Row Based Replication, RBR)	把實際變更的列資料寫入 binlog。	需要精確的資料同步（如使用 NOW()、UUID() 等非決定性函式）時。
混合模式 (Mixed)	依情況自動切換 SBR 與 RBR。	MySQL 5.5+ 預設，兼容性最佳。

建議：在新建專案時，直接使用 混合模式，若有特殊需求再調整為 RBR。

---

3. 設定 Master（主庫）

以下範例假設兩台 Linux 主機：

• Master：192.168.10.10
• Slave：192.168.10.20

3.1. 修改 my.cnf

# /etc/mysql/my.cnf (Master)
[mysqld]
server-id = 1                 # 每台 MySQL 必須唯一
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = mixed         # 或 statement / row
expire_logs_days = 7          # 保留 binlog 天數
max_binlog_size = 100M

提示：server-id 必須是 整數且唯一，否則複寫會失敗。

3.2. 建立複寫帳號

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

3.3. 取得當前 binlog 位址

SHOW MASTER STATUS\G
-- 輸出範例
-- File: mysql-bin.000001
-- Position: 154

將 File 與 Position 記錄下來，稍後設定 Slave 時會用到。

---

4. 設定 Slave（從庫）

4.1. 修改 my.cnf

# /etc/mysql/my.cnf (Slave)
[mysqld]
server-id = 2                 # 必須與 Master 不同
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1                # 防止直接寫入

注意：read_only 只在 非 super 使用者 時生效，root 仍可寫入以利故障排除。

4.2. 設定複寫來源

CHANGE MASTER TO
  MASTER_HOST='192.168.10.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPass!23',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;

4.3. 啟動複寫

START SLAVE;

4.4. 檢查狀態

SHOW SLAVE STATUS\G
-- 重要欄位說明
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (表示同步完成)

若 Slave_IO_Running 或 Slave_SQL_Running 為 No，請檢查 Last_IO_Error、Last_SQL_Error。

---

5. 常用管理指令與程式碼範例

以下提供 5 個實務上常見的範例，皆以 SQL 為主，並加上簡短說明。

5.1. 暫停與恢復複寫

-- 暫停 IO 與 SQL Thread（例：維護期間）
STOP SLAVE;

-- 只暫停 IO（允許已取得的 relay log 繼續執行）
STOP SLAVE IO_THREAD;

-- 恢復
START SLAVE;

5.2. 手動指定同步點（重新定位）

-- 假設 Master 已切換 binlog，從庫需要重新定位
CHANGE MASTER TO
  MASTER_LOG_FILE='mysql-bin.000010',
  MASTER_LOG_POS=4523;
START SLAVE;

5.3. 檢查從庫延遲（監控指標）

SELECT 
  ROUND(UNIX_TIMESTAMP(NOW()) - 
        UNIX_TIMESTAMP(SELECT_EVENT_TIME), 2) AS seconds_behind_master
FROM performance_schema.replication_applier_status_by_worker;

說明：seconds_behind_master 越大代表從庫落後越多，需檢查網路或 I/O 瓶頸。

5.4. 讀寫分離的簡易實作（應用層）

// Node.js 範例：根據 SQL 類型自動切換連線
const mysql = require('mysql2/promise');

const masterPool = mysql.createPool({
  host: '192.168.10.10',
  user: 'app_user',
  password: 'AppPass!45',
  database: 'mydb'
});

const slavePool = mysql.createPool({
  host: '192.168.10.20',
  user: 'app_user',
  password: 'AppPass!45',
  database: 'mydb'
});

async function query(sql, params) {
  const isWrite = /INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|ALTER/i.test(sql);
  const pool = isWrite ? masterPool : slavePool;
  const [rows] = await pool.execute(sql, params);
  return rows;
}

重點：只要 SQL 包含寫入關鍵字，就直接走 Master；讀取則走 Slave，降低 Master 的負載。

5.5. 故障切換腳本（簡易 Bash）

#!/bin/bash
# promote_slave.sh - 將 Slave 提升為新的 Master

SLAVE_HOST="192.168.10.20"
REPL_USER="repl"
REPL_PASS="StrongPass!23"

# 停止複寫
ssh $SLAVE_HOST "mysql -e 'STOP SLAVE;'"

# 設定為可寫
ssh $SLAVE_HOST "mysql -e 'RESET MASTER; SET GLOBAL read_only = OFF;'"

# 更新其他 Slave (若有) 的 master 設定
# 這裡僅示範單一 Slave 情境
echo "Slave $SLAVE_HOST 已升級為 Master"

備註：正式環境建議使用 MHA、Orchestrator 或 ProxySQL 等工具，避免手動操作失誤。

---

常見陷阱與最佳實踐

陷阱	可能的原因	建議的解決方式
Slave 延遲過高	網路頻寬不足、磁碟 I/O 瓶頸、binlog_row_image 設為 FULL	- 測試並調整 innodb_flush_log_at_trx_commit、sync_binlog - 使用 SSD、加大 relay_log 緩衝 - 若延遲不可接受，考慮半同步或組式複寫
主從資料不一致	非決定性函式（如 NOW()、RAND()）在 SBR 模式下造成差異	使用 Row Based Replication 或 Mixed，或在程式中將時間戳記以變數方式傳入
read_only 無效	以 SUPER 權限的使用者（如 root）直接寫入	嚴格限制 root 只用於管理，應建立普通應用帳號，並在 my.cnf 中加入 skip-grant-tables=0
CHANGE MASTER TO 失敗	Master 的 bind-address 限制或防火牆阻擋	確認 my.cnf 中 bind-address = 0.0.0.0 並開放 3306 端口
故障切換時資料遺失	Master 故障前的交易尚未寫入 binlog	啟用 半同步複寫（rpl_semi_sync_master_enabled=1）或使用 GTID 方式管理位移

最佳實踐要點

1. 使用 GTID（Global Transaction ID）：簡化故障切換與位置同步，避免手動記錄 log_file 與 log_pos。

   gtid_mode = ON
   enforce_gtid_consistency = ON
   

2. 監控與告警：部署 Percona Monitoring and Management (PMM)、Grafana + Prometheus 監控 Seconds_Behind_Master、Relay_Log_Space、IO/SQL Thread 狀態。
3. 定期測試故障切換：演練 MHA、Orchestrator 或 ProxySQL 的自動升降級流程，確保在真實故障時能即時恢復。
4. 備份策略：即使有複寫，仍需每日全備份（mysqldump、xtrabackup）與 binlog 增量備份，防止資料損毀或誤刪。
5. 分離讀寫流量：使用 ProxySQL 或 HAProxy 代理層，讓應用程式只看一個端點，內部自動路由到 Master 或 Slave。

---

實際應用場景

場景	為何使用 Master–Slave 複寫	具體實作方式
大型電商網站	高並發的商品查詢（讀）與訂單寫入（寫）需要分離，避免寫入壓力影響搜尋速度。	- Master 處理訂單、庫存更新。 - 多台 Slave 提供商品列表、分類瀏覽。 - 使用 ProxySQL 統一連線，根據 SQL 類型自動分流。
報表與資料倉儲	報表查詢常常掃描大量歷史資料，若在同一庫上執行會拖慢線上交易。	- 建立一或多台只讀 Slave，將歷史資料搬移至資料倉儲（如 MySQL + Hadoop）。 - 定時使用 pt-table-sync 同步結構變更。
跨區域容災	主資料中心故障時，需要快速切換至另一個地理位置的資料庫。	- 在不同區域部署 Master–Slave（同步或半同步），使用 GTID + MHA 做自動故障切換。
開發/測試環境	測試新功能時不希望影響正式線上資料。	- 從 Production Master 建立只讀 Slave 作為測試環境，同步資料但不允許寫入。
API 服務	API 讀取頻繁、寫入相對較少，需降低延遲。	- 讀取走 Slave，寫入走 Master，使用 READ COMMITTED 隔離級別確保一致性。

---

總結

Master–Slave 複寫是 MySQL 高可用與讀寫分離 的基礎技術，透過 binlog、IO/SQL Thread 的協作，使資料在多個節點間保持同步。本文從 概念說明、實作步驟、程式碼範例、常見陷阱與最佳實踐，以及 實務應用場景，完整呈現了建立與維護 MySQL 複寫所需的核心知識。

• 設計時，先決定使用 GTID、混合模式，並為每台 MySQL 設定唯一 server-id。
• 部署時，遵循 Master → Slave 的設定流程，務必檢查 SHOW SLAVE STATUS\G 中的 Slave_IO_Running、Slave_SQL_Running 與 Seconds_Behind_Master。
• 運維時，持續監控延遲、I/O、網路，並定期演練故障切換，確保在突發情況下能快速恢復服務。

掌握了上述要點後，你就能在 高流量、需要容災 的環境中，利用 MySQL Master–Slave 複寫提供 穩定、可擴展 的資料服務，為系統的長期成長奠定堅實基礎。祝你在實務上玩得開心、寫得順手！