您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主從復制與備份恢復生產實戰：從架構原理到故障切換全流程

編輯：MySQL 綜合教學

MySQL 主從復制與備份恢復生產實戰：從架構原理到故障切換全流程

主從復制是 MySQL 高可用架構的基石，支援即時備份、讀寫分離、災難恢復。本文以生產環境視角，完整演示從配置、同步驗證、讀寫分離、主庫故障切換到雙主架構，並深入講解 mysqldump 全量/增量備份與恢復策略。

一、MySQL 主從復制

1. 核心原理

主庫開啟 binlog，從庫透過 I/O 執行緒拉取 binlog 寫入本地 relay log，SQL 執行緒重放 relay log 實現資料同步。關鍵三線程：Master Binlog Dump Thread、Slave I/O Thread、Slave SQL Thread。

2. 配置檔修改（雙機器）

# 主庫 /data/3306/my.cnf
[mysqld]
log-bin = /data/3306/mysql-bin
server-id = 1

# 從庫 /data/3307/my.cnf
[mysqld]
log-bin = /data/3307/mysql-bin
server-id = 3
# 建議從庫啟用 read-only 防誤寫入
read-only = 1

驗證：SHOW VARIABLES LIKE 'log_bin'; 應顯示 ON。

3. 建立復制專用帳號

GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.200.%' IDENTIFIED BY 'nick';
FLUSH PRIVILEGES;

4. 全量備份主庫並恢復到從庫

# 1. 主庫鎖表（唯讀）
FLUSH TABLES WITH READ LOCK;
# 2. 記錄 binlog 位置
SHOW MASTER STATUS;
-- 記下 File: mysql-bin.000013, Position: 410
# 3. 另開終端機備份（含 --master-data=2 記錄位置）
mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2 | gzip > /opt/rep.sql.gz
# 4. 解鎖
UNLOCK TABLES;
# 5. 從庫恢復
gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock

5. 配置從庫同步參數並啟動

CHANGE MASTER TO
  MASTER_HOST='192.168.200.98',
  MASTER_PORT=3306,
  MASTER_USER='rep',
  MASTER_PASSWORD='nick',
  MASTER_LOG_FILE='mysql-bin.000013',
  MASTER_LOG_POS=410;
START SLAVE;

驗證關鍵指標：

SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

6. 讀寫分離實作

主庫 binlog-ignore-db=mysql,performance_schema,information_schema 忽略系統庫同步。
從庫設 read-only=1，僅授予應用帳號 SELECT,INSERT,UPDATE,DELETE 權限（無 SUPER/ALL），即可阻擋寫入。
常見同步故障處理：
- 錯誤 1007/1062/1032：STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
- 或配置 slave-skip-errors=1032,1062,1007 自動跳過。
從庫開啟 log-bin + log-slave-updates 可作為下一級主庫（鏈式復制）。

7. 主庫宕機：從庫提升為新主

# 1. 比對所有從庫 master.info，選擇 Position 最大（資料最新）者
# 2. 確保 relay log 全部應用完畢
STOP SLAVE IO_THREAD; SHOW PROCESSLIST; -- 等待 "Has read all relay log"
# 3. 於被提升從庫執行
STOP SLAVE; RESET MASTER;
# 4. 刪除舊同步元數據
rm -f /data/3307/data/master.info /data/3307/data/relay-log.info
# 5. 修改配置：啟用 log-bin，註釋 read-only、log-slave-updates
# 6. 重啟 MySQL
# 7. 其他從庫重新指向新主
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.200.98', MASTER_PORT=3307, ...;
START SLAVE;

8. 雙主（Master-Master）架構

適用於雙活場景，核心在於 自增 ID 錯開 避免衝突：

# M1 (3306)
auto_increment_increment = 2
auto_increment_offset = 1
# M2 (3307)
auto_increment_increment = 2
auto_increment_offset = 2

雙向建立復制帳號、同步資料、CHANGE MASTER TO 指向對方、START SLAVE。寫入時應用層路由至單一主庫，或由中間件（如 ProxySQL）分發。

二、MySQL 備份與恢復策略

1. mysqldump 核心參數速查

參數	說明
`-A`	備份所有資料庫
`-B`	指定多個資料庫，輸出含 `CREATE DATABASE` 與 `USE`
`--single-transaction`	InnoDB 熱備份關鍵，啟動一致性快照，不鎖表
`-F`	備份前 `FLUSH LOGS` 切換 binlog，利於增量恢復
`--master-data=2`	輸出 `CHANGE MASTER TO` 註解，記錄 binlog 位置
`-x / --lock-all-tables`	鎖定所有表（MyISAM 必須）
`-d`	僅備份結構
`-t`	僅備份資料

2. 生產環境全量備份腳本範例

#!/bin/bash
MYUSER=root; MYPASS=nick; MYSOCK=/data/3306/mysql.sock
DATA_FILE=/server/backup/mysql_$(date +%F).sql.gz

# InnoDB 熱備份（推薦）
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK \
  --default-character-set=utf8mb4 \
  --single-transaction -F -A -B | gzip > $DATA_FILE

# MyISAM 混合環境
# mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables | gzip > $DATA_FILE

3. 增量恢復：關鍵條件與流程

前提：主庫開啟 log-bin，且擁有最近一次全量備份。

場景：誤刪 nick 資料庫，需恢復到誤操作前一刻。

# 1. 檢視全量備份中的 binlog 位置
gzip -d mysql_2015-10-31.sql.gz
grep -i "CHANGE MASTER" mysql_2015-10-31.sql
# 輸出：-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=410;

# 2. 立即刷新 binlog 並備份誤操作前的 binlog
mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs
cp /data/3306/mysql-bin.000030 /server/backup/

# 3. 從 binlog 提取指定資料庫的 SQL
mysqlbinlog -d nick /server/backup/mysql-bin.000030 > bin.sql
# 編輯 bin.sql，刪除誤操作語句（如 DROP DATABASE nick）

# 4. 恢復：先全量，再增量
mysql -uroot -pnick -S /data/3306/mysql.sock < mysql_2015-10-31.sql
mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql