萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Access 教學 >> Access 基礎教學

MySQL 複寫架構完整指南:原理、部署、故障排除與最佳實務

MySQL 複寫 (Replication) 是高可用、讀寫分離、備份卸載的核心基礎設施。本文系統整理複寫原理、主從部署步驟、常見故障診斷與生產環境最佳實務,對比 NoSQL 複寫更具備 ACID 保障與彈性拓撲。

複寫核心原理:三線程協作

  1. 主庫 Binlog Dump Thread:將本地 Binary Log 事件傳送給從庫 I/O Thread。
  2. 從庫 I/O Thread:接收事件寫入本地 Relay Log。
  3. 從庫 SQL Thread:讀取 Relay Log 重放事件,回放資料變更。

MySQL 複寫架構圖

關鍵日志座標 (SHOW SLAVE STATUS)

欄位含義
Master_Log_File / Read_Master_Log_PosI/O Thread 下一個要讀取的主庫 Binlog 位置
Relay_Master_Log_File / Exec_Master_Log_PosSQL Thread 下一個要執行的主庫 Binlog 位置
Relay_Log_File / Relay_Log_PosSQL Thread 下一個要讀取的本地 Relay Log 位置

理解三組座標對應關係,是故障診斷與手動修復的關鍵。

生產環境部署標準流程

1. 主庫建立複寫帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_host' IDENTIFIED BY 'strong_password';
-- 禁止使用 root 或 SUPER 權限帳號

2. 主庫 my.cnf 關鍵設定

[mysqld]
server_id = 100                 # 全域唯一
log_bin = mysql-bin             # 啟用 Binlog,指定固定檔名避免主機名變更導致中斷
log_bin_index = mysql-bin.index
sync_binlog = 1                 # 每次 Commit 刷盤,保證不丟 Binlog
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1           # XA 交易支援,配合雙 1 保證 Crash-safe

3. 從庫 my.cnf 關鍵設定

[mysqld]
server_id = 200
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
read_only = 1                   # 防止誤寫入 (SUPER 權限用戶除外)
skip_slave_start = 1            # 手動 START SLAVE,避免啟動衝突
log_slave_updates = 1           # 從庫也記 Binlog,利於後續升主或級聯複寫

4. 從庫資料初始化 (三種方式)

  • mysqldump 全量備份 (適用中小資料量):
    mysqldump --all-databases --master-data=1 --single-transaction > data.sql (InnoDB 必加 --single-transaction) → 從庫 mysql < data.sql
  • 實體檔案複製 (大資料量、需停機):
    1. 主庫 FLUSH TABLES WITH READ LOCK; → SHOW MASTER STATUS; 記錄 File/Position
    2. 複製 datadir 所有檔案到從庫 (InnoDB 需停止 mysqld 再複製)
    3. 從庫啟動後 CHANGE MASTER TO ... MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
  • 從現有從庫克隆新從庫 (零停機):
    1. 舊從庫 SHOW SLAVE STATUS; 取 Relay_Master_Log_File / Exec_Master_Log_Pos
    2. 同實體複製方式取得資料
    3. 新從庫 CHANGE MASTER TO ... MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;

5. 啟動並驗證

START SLAVE;
SHOW SLAVE STATUS\G
-- 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

常見故障與處理

錯誤類型判斷

# 檢查 Binlog 是否損壞
mysqlbinlog mysql-bin.000123 > /dev/null
# 檢查 Relay Log
mysqlbinlog mysql-relay-bin.000045 > /dev/null
  • 無輸出 = 正常;有報錯 = 檔案損壞。

主庫 Binlog 錯誤 → 跳過錯誤事件

SET GLOBAL sql_slave_skip_counter = 1;  -- 跳過 1 個事件
START SLAVE;
-- 多個錯誤需重複執行,注意:會造成主從資料不一致

從庫 Relay Log 錯誤 → 重新指向主庫 Binlog 位置

STOP SLAVE;
CHANGE MASTER TO
    MASTER_LOG_FILE='Relay_Master_Log_File值',
    MASTER_LOG_POS=Exec_Master_Log_Pos值;
START SLAVE;
-- 系統會丟棄現有 Relay Log 重新下載

最佳實務檢查清單

  • ✅ 主從 server_id 全域唯一
  • ✅ 主庫 sync_binlog=1 + innodb_flush_log_at_trx_commit=1 (雙 1)
  • ✅ 從庫 read_only=1 + skip_slave_start=1 + log_slave_updates=1
  • ✅ 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running 告警
  • ✅ 定期 pt-table-checksum 驗證主從資料一致性
  • ✅ 備份策略:從庫停機冷備或 xtrabackup 熱備,避免影響主庫
  • ✅ 多層級複寫拓撲 (Master → Slave → Slave) 時確保中間層 log_slave_updates=1

遵循上述規範部署與運維,MySQL 複寫可穩定支撐核心業務高可用架構。

copyright © 萬盛學電腦網 all rights reserved