MySQL 主從複寫部署筆記：原理、配置、驗證與在線擴展從庫

MySQL 主從複寫架構部署完整筆記

基於 Binlog 的非同步複寫是 MySQL 高可用、讀寫分離的基石。本文記錄從原理到生產部署、驗證、在線擴展從庫的全流程。

1. 複寫原理三線程模型

• Master Binlog Dump Thread：主庫為每個從庫建立一線程，讀取 Binlog 發送給從庫。
• Slave IO Thread：從庫連接主庫，請求 Binlog，寫入本地 Relay Log。
• Slave SQL Thread：從庫讀取 Relay Log，解析重放 SQL，實現資料重放。

關鍵檔案：Master binlog、Slave relay-log、master.info (記錄同步位置)、relay-log.info。

2. Master 配置 (/etc/my.cnf)

[mysqld]
log-bin=mysql-bin                    # 啟用 Binlog
binlog_format=MIXED                  # 混合格式 (預設 ROW 更安全)
binlog_cache_size=4M                 # 事務 Binlog 緩衝
max_binlog_size=300M                 # 單檔上限
expire_logs_days=3                   -- 自動清理天數
server-id=1                          # 全域唯一 ID
# 可選：binlog-do-db=db_test         # 僅記錄指定庫 (不建議，從庫端過濾更靈活)

3. 建立複寫帳號

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

4. Master 全量備份與遷移 (不停寫方案)

# 1. 鎖表備份 (極短暫)
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;  -- 記錄 File、Position
# 2. 新終端導出資料 (不退出上一會話，否則鎖釋放)
mysqldump -uroot -h127.0.0.1 --single-transaction --master-data=2 --databases db1 > /backup/db1.sql
# 3. 壓縮傳輸到 Slave
tar -czvf db1.tar.gz db1.sql
scp db1.tar.gz root@slave_ip:/backup/
# 4. 解鎖
mysql> UNLOCK TABLES;

5. Slave 配置 (/etc/my.cnf)

[mysqld]
server-id=2                       # 唯一，不可與 Master 衝突
relay-log=relay-log
relay-log-index=relay-log.index
read_only=1                       # 建議開啟，防止誤寫入
binlog_format=MIXED
binlog_cache_size=4M
max_binlog_size=300M
# log-slave-updates=1             # 若 Slave 也做 Master (鏈式複寫) 需開啟

6. Slave 導入資料、配置同步、啟動

# 1. 建庫導入
mysql> CREATE DATABASE db1;
mysql> USE db1;
mysql> SET NAMES utf8;
mysql> SOURCE /backup/db1.sql;

# 2. 配置 Master 連線資訊
mysql> CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl',
    MASTER_LOG_FILE='mysql-bin.000001',  -- 來自 Master SHOW MASTER STATUS
    MASTER_LOG_POS=107;

# 3. 啟動並驗證
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

7. 常見錯誤與排查

8. 在線新增從庫 (零停機)

原理：利用現有 Slave 完整備份作為新 Slave 基準，避免鎖定 Master。

1. 在現有 Slave 執行 FLUSH TABLES WITH READ LOCK; SHOW SLAVE STATUS\G 記錄 Relay_Master_Log_File、Exec_Master_Log_Pos (對應 Master 位置)。
2. mysqldump --single-transaction --master-data=2 --all-databases > new_slave.sql
3. UNLOCK TABLES;
4. 將備份傳輸到新 Slave 導入，配置 CHANGE MASTER TO ... MASTER_LOG_FILE=..., MASTER_LOG_POS=...。
5. START SLAVE; 驗證。

9. 官方建議：從庫數量上限評估

理論無上限，實際受限於 Master 網路頻寬、寫入吞吐、Binlog 傳輸能力。官方經驗公式 (假設 90% 讀、10% 寫、讀寫成本比 1:2)：

• 0 從庫：~109 寫/秒
• 1 從庫：~184 寫/秒
• 8 從庫：~400 寫/秒
• 17 從庫：~480 寫/秒 (邊際效益遞減)

建議：單 Master 掛載 3-5 從庫 為甜蠟點，超過需考慮多級複寫或叢集方案 (Group Replication、InnoDB Cluster)。