萬盛學電腦網

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

MySQL 主從複制架構原理與手把手部署教學

複制原理核心:三執行緒模型

  • Master - Binlog Dump Thread:讀取 Binlog 發送給 Slave I/O Thread
  • Slave - I/O Thread:接收 Binlog 寫入本地 Relay Log
  • Slave - SQL Thread:讀取 Relay Log 重放 SQL,更新資料

優勢:非同步、解耦、可多從庫、支援異地災備。

為什麼部署主從?

  1. 高可用:Master 故障可提升 Slave 為新 Master
  2. 讀寫分離:讀請求分流至 Slave,降低 Master 壓力
  3. 零影響備份:在 Slave 執行 mysqldump/xtrabackup,Master 完全無感
  4. 異地容災:跨 IDC 部署 Slave,單點故障不丟資料

完整部署步驟 (CentOS 7 / MySQL 5.7+ 為例)

1. 環境準備

  • 兩台主機:Master (192.168.1.100)、Slave (192.168.1.101)
  • 同版本 MySQL、關閉防火牆/開放 3306、時間同步 (ntpdate)

2. Master 配置

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
# 可選:只同步特定庫
# binlog_do_db = biz_db
systemctl restart mysqld

3. 建立複制帳號

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

4. 取得同步基準點 (不停機熱備份建議用 xtrabackup)

-- 方法 A:mysqldump (小資料量、可接受短暫鎖表)
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Position
mysqldump -u root -p --all-databases --master-data=2 --single-transaction > master_full.sql
UNLOCK TABLES;

-- 方法 B:xtrabackup (大資料量、完全不鎖表,推薦)
innobackupex --user=root --password=xxx --slave-info /backup/

5. Slave 配置

[mysqld]
server-id = 2
relay_log = relay-bin
log_slave_updates = 1      # 若此 Slave 還要做 Master
read_only = 1
skip_slave_start = 1       # 手動啟動,便於檢查
systemctl restart mysqld

6. 匯入基線資料

mysql -u root -p < master_full.sql
# 或 xtrabackup 恢復後 apply-log

7. 設定複制起點並啟動

CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'StrongPass#2024',
    MASTER_LOG_FILE = 'mysql-bin.000003',
    MASTER_LOG_POS = 73,
    MASTER_CONNECT_RETRY = 30;

START SLAVE;

8. 驗證狀態

SHOW SLAVE STATUS\G
-- 關鍵欄位:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_IO_Error / Last_SQL_Error: (空白)

常見故障與排查

現象可能原因處理
IO_Running=Connecting網路不通/帳號密碼錯/防火牆telnet master 3306、檢查 grants、error log
SQL_Running=NoSQL 執行錯誤 (主鍵衝突、欄位缺失)查 Last_SQL_Error,修正資料後 STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Seconds_Behind_Master 偏大Slave 機能不足/大事務/長查詢阻塞升級 Slave 規格、優化慢查詢、並行複制 (slave_parallel_workers)

進階優化建議

  • GTID 複制 (MySQL 5.6+):GTID_MODE=ON,自動定位位置,故障切換更簡單。
  • 並行複制:slave_parallel_workers=4 + slave_parallel_type=LOGICAL_CLOCK。
  • 半同步複制:rpl_semi_sync_master=ON,確保至少一個 Slave 收到 Binlog 再回應 Commit,降低資料遺失風險。
  • 監控告警:Prometheus + mysqld_exporter 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running。
copyright © 萬盛學電腦網 all rights reserved