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

MySQL 三大備份策略深度解析：邏輯、物理、快照與 XtraBackup 實戰

編輯：MySQL 綜合教學

備份分類維度

維度	分類	核心差異
業務影響	冷備 (離線) / 溫備 (只讀) / 熱備 (在線不影響業務)	MyISAM 僅支援冷/溫備，InnoDB 支援熱備
數據範圍	完全備份 / 增量備份 (上次備份以來變化) / 差異備份 (上次完全備份以來變化)	恢復鏈：完全+增量+Binlog 或完全+差異+Binlog
備份形式	邏輯備份 (SQL 文本) / 物理備份 (文件級) / 快照備份 (LVM/存儲層)	邏輯跨版本/引擎、物理恢復快、快照近熱備

備份對象清單 (不止數據)

數據 (表數據)
配置文件 (`my.cnf`)
代碼對象 (存儲過程、函數、觸發器、視圖、事件)
OS 層配置 (limits.conf、sysctl、防火牆、NTP)
複寫配置 (主從拓撲、GTID 狀態)
二進位日誌 (Binlog) — **增量恢復關鍵**

方案一：邏輯備份 — mysqldump + Binlog (最通用)

核心策略：完全備份 + 增量 Binlog + 時間點恢復 (PITR)

# 1. 完全備份 (熱備 InnoDB，溫備 MyISAM)
mysqldump -uroot -p --single-transaction --master-data=2 --routines --triggers --events --databases db_name > /backup/full_db_$(date +%F).sql
# --single-transaction: InnoDB 一致性快照 (不鎖表)
# --master-data=2: 記錄 Binlog 位置 (註釋行)
# --routines/triggers/events: 備份代碼對象

# 2. 增量備份 = 導出 Binlog 區間
# 從完全備份文件提取 Binlog 起始位置
head -50 /backup/full_db_2023-01-15.sql | grep "CHANGE MASTER TO"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684;

# 導出增量 Binlog (到當前或指定位置)
mysqlbinlog --start-position=15684 /var/lib/mysql/mysql-bin.000013 > /backup/inc_$(date +%F_%H).sql

# 3. 恢復演練 (核心：定期演練！)
# 關閉 Binlog 避免恢復期產生新 Binlog
mysql> SET SQL_LOG_BIN=0;
# 導入完全備份
mysql < /backup/full_db_2023-01-15.sql
# 導入增量 Binlog
mysql < /backup/inc_2023-01-15_14.sql
# 時間點恢復 (指定停止位置)
mysqlbinlog --stop-position=XXXXX /var/lib/mysql/mysql-bin.000013 | mysql

關鍵注意點

浮點數精度可能損失 (文本轉換)
恢復後需 `ANALYZE TABLE` 更新統計信息
大庫導入極慢 (單線程)，TB 級不可行
**備份未驗證 = 無備份**，必須定期恢復演練

方案二：LVM 快照備份 — 近熱備物理備份 (秒級凍結)

前提條件

數據目錄在獨立 LV (Logical Volume) 上
事務日誌 (Redo Log) 必須與數據同 LV
VG 有足夠剩餘空間建立快照 (建議 10%~20% 原 LV 大小)

操作流程

# 1. 主庫加全局讀鎖 (極短時間，秒級)
mysql> FLUSH TABLES WITH READ LOCK;
# 另開終端，勿退出！

# 2. 記錄 Binlog 位置
mysql> SHOW MASTER STATUS;
# File: mysql-bin.000004, Position: 107

# 3. 滾動 Binlog (便於增量備份)
mysql> FLUSH LOGS;

# 4. 創建 LVM 快照 (瞬間完成)
lvcreate -L 5G -s -n mysql_snap /dev/vg_data/lv_mysql

# 5. 釋放主庫鎖 (業務恢復正常)
mysql> UNLOCK TABLES;

# 6. 掛載快照備份文件
mkdir /mnt/snap
mount /dev/vg_data/mysql_snap /mnt/snap
rsync -av /mnt/snap/ /backup/lvm_snap_$(date +%F)/

# 7. 導出 Binlog 位置文件
mysql -e "SHOW MASTER STATUS;" > /backup/lvm_snap_$(date +%F)/binlog_pos.txt

# 8. 卸載並刪除快照 (釋放空間)
umount /mnt/snap
lvremove -f /dev/vg_data/mysql_snap

# 9. 後續增量 = 導出 Binlog (同方案一)

恢復流程

# 1. 停止 MySQL，清空數據目錄
systemctl stop mysqld
rm -rf /var/lib/mysql/*

# 2. 還原快照文件
rsync -av /backup/lvm_snap_2023-01-15/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql

# 3. 啟動 MySQL (關閉 Binlog)
systemctl start mysqld
mysql> SET SQL_LOG_BIN=0;

# 4. 導入增量 Binlog 至目標時間點
mysql < /backup/binlog_incremental.sql

# 5. 開啟 Binlog，驗證數據
mysql> SET SQL_LOG_BIN=1;

方案三：Percona XtraBackup — 專業級物理熱備份 (首選生產)

核心優勢

**非阻塞** 物理備份 (複製 `.ibd`、`.frm`、Redo Log)，不影響讀寫
支援 **流式傳輸** (`--stream=xbstream`)、**壓縮** (`--compress`)、**加密** (`--encrypt`)
支援 **增量備份** (基於 LSN)、**單表備份/恢復** (需 `innodb_file_per_table=1`)
內建 **備份驗證** (`--apply-log` 過程即驗證)
官方工具，Percona 維護，社區活躍

前提配置

[mysqld]
innodb_file_per_table = 1  # 單表單獨恢復必須

完全備份

innobackupex --user=root --password=xxx --no-timestamp /backup/xtra/full_20230115
# 成功標誌：innobackupex: completed OK!

增量備份 (基於 LSN 遞增)

# 1. 查看完全備份 LSN 範圍
cat /backup/xtra/full_20230115/xtrabackup_checkpoints
# backup_type = full-backuped
# from_lsn = 0
# to_lsn = 123456789

# 2. 第一次增量
innobackupex --user=root --password=xxx --incremental /backup/xtra --incremental-basedir=/backup/xtra/full_20230115

# 3. 第二次增量 (基於上一次增量目錄)
innobackupex --user=root --password=xxx --incremental /backup/xtra --incremental-basedir=/backup/xtra/2023-01-15_14-30-00

備份文件關鍵解讀

文件	含義
`xtrabackup_checkpoints`	備份類型、LSN 範圍、狀態
`xtrabackup_binlog_info`	備份瞬間 Binlog 文件名 + Position
`xtrabackup_logfile`	備份過程中複製的 Redo Log (用於 --apply-log 重放)
`backup-my.cnf`	備份時的 my.cnf 相關參數

恢復流程 (核心：準備階段合併增量)

# 1. 準備完全備份 (--redo-only 只重做已提交事務，不回滾未提交)
innobackupex --apply-log --redo-only /backup/xtra/full_20230115

# 2. 合併第一次增量
innobackupex --apply-log --redo-only /backup/xtra/full_20230115 --incremental-basedir=/backup/xtra/inc1

# 3. 合併第二次增量
innobackupex --apply-log --redo-only /backup/xtra/full_20230115 --incremental-basedir=/backup/xtra/inc2

# 4. 最後一次增量不加 --redo-only (需回滾未提交事務)
innobackupex --apply-log /backup/xtra/full_20230115 --incremental-basedir=/backup/xtra/inc_last

# 5. 恢復數據 (--copy-back 覆蓋 datadir)
systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --copy-back /backup/xtra/full_20230115
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# 6. 導入最後 Binlog (增量備份後到故障點的間隙)
mysql < /backup/binlog_tail.sql

四大方案對比選型指南

維度	mysqldump + Binlog	LVM Snapshot	XtraBackup	企業級備份系統
業務影響	熱備 (InnoDB) / 溫備 (MyISAM)	秒級凍結 (FTWRL)	完全無阻塞	無阻塞 (存儲層快照)
恢復速度	慢 (SQL 重放)	快 (文件複製)	快 (物理恢復)	極快 (存儲級瞬間恢復)
跨版本/引擎	最好	同版本/引擎	同大版本	同版本
單表恢復	易 (grep SQL)	難	支援 (需 file_per_table)	支援
壓縮/加密/傳輸	外部工具	外部工具	內建支援	內建
運維複雜度	低	中 (LVM 管理)	中	高 (專用設備/軟體)
成本	零	零 (OS 層)	零 (開源)	高 (授權/硬體)

生產環境推薦架構

# 每日 02:00 完全備份 (XtraBackup 壓縮流式上傳對象存儲)
0 2 * * * innobackupex --compress --stream=xbstream --user=root --password=xxx /backup | ssh storage "cat > /s3/xtra/full_$(date +%F).xbstream"

# 每小時增量備份
0 * * * * innobackupex --incremental --compress --stream=xbstream --incremental-basedir=... | ssh storage ...

# Binlog 實時同步 (關鍵 PITR)
# 部署 mysqlbinlog --raw --read-from-remote-server --stop-never 持續拉取 Binlog 到備份存儲

# 定期恢復演練 (每月至少一次)
# 自動化腳本：拉取最新完全+增量 → 準備合併 → 啟動驗證實例 → 校驗核心表行數/校驗和 → 銷毀

總結

MySQL 備份無銀彈，**XtraBackup 物理熱備份 + Binlog 實時歸檔 + 定期恢復演練** 是生產環境鐵三角。邏輯備份適合小庫/跨版本遷移，LVM 快照適合有 LVM 且能接受秒級鎖的環境。核心原則：**備份必須可恢復、恢復必須演練、演練必須自動化、RPO/RTO 必須量化**。