MySQL 主從複制備份架構原理與配置實戰全紀錄

MySQL 主從複制備份架構原理與配置實戰全紀錄

主從複制解決讀寫分離、資料分發、負載均衡、備份與高可用。本文系統梳理複制原理、三線程模型、配置步驟與常見坑點。

複制核心原理：三步驟三線程

1. Master Binlog Dump 線程：事務提交前將變更串行寫入 Binary Log。
2. Slave I/O 線程：連接 Master，請求 Binlog 事件，寫入本地 Relay Log。
3. Slave SQL 線程：讀取 Relay Log 重放事件，更新 Slave 資料。

關鍵限制：Slave 重放為串行（單 SQL 線程），Master 並行寫入在 Slave 端順序執行，可能成為瓶頸（MySQL 5.7+ 多線程複制緩解）。

主庫配置 (my.cnf)

[mysqld]
log-bin = mysql-bin
server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql
sync_binlog = 1          # 事務提交即刷盤，最高持久性

建立複制帳號：

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.104.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

從庫配置 (my.cnf)

[mysqld]
server-id = 2
replicate-do-db = test
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
# MySQL 5.1+ 不再支援 my.cnf 內 master_* 參數，改用 CHANGE MASTER TO

啟動複制標準流程

# 1. 主從同步停止/重置
STOP SLAVE; RESET SLAVE; RESET MASTER;

# 2. 主庫鎖表備份（或用 xtrabackup 熱備）
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Position
# 複製資料目錄到從庫
UNLOCK TABLES;

# 3. 從庫設定主庫資訊
CHANGE MASTER TO
  MASTER_HOST='192.168.104.9',
  MASTER_USER='backup',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=106;

# 4. 啟動並驗證
START SLAVE;
SHOW SLAVE STATUS\G
# 確認：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File = Relay_Master_Log_File
Read_Master_Log_Pos ≈ Exec_Master_Log_Pos

雙主（互為主從）配置要點

• 兩庫均開啟 binlog、設定不同 server-id、互為 Master/Slave。
• 自增衝突：設定 auto_increment_increment=2、auto_increment_offset=1/2 錯開 ID。
• 極易發生寫入衝突、死鎖，生產環境不建議雙主寫入，改用 MHA/Orchestrator 單主高可用。

常見坑點速查

總結

主從複制是 MySQL 高可用基石。掌握三線程模型、標準化部署流程、監控關鍵指標，並配合 GTID、半同步、自動故障轉移工具，才能構建穩健的資料庫架構。

1. 上一篇文章： MySQL ORDER BY + LIMIT 分頁查詢資料遺失問題根因與解決方案
2. 下一篇文章： MySQL DATE_FORMAT 實現每日/每月新增用戶統計實戰

MySQL 綜合教學

Windows 系統 MySQL 忘記 root 密碼重置方法（任務管理器 + skip-grant-tables）

前言 Windows 下 MySQL 忘記 root 密碼，

MySQL 效能優化全攻略：從查詢分析、索引設計、架構調整到服務器參數調優

MySQL 效能優化全攻略：從查詢分析、索引設計、架構調整到

Windows 命令列安裝 MySQL 5.7 完整教程：從解壓到建庫授權

Windows 命令列安裝 MySQL 5.7 完整教程：從

MySQL 前 CEO Marten Mickos 公開信敦促歐盟批准收購 Sun：延遲損害競爭，甲骨文有動力發展 MySQL，社區影響力超越所有者

前 MySQL CEO Marten Mickos 致信歐盟