MySQL 資料庫備份與還原實戰：從檔案級複製到 mysqldump 企業級策略

兩大備份範式對比

策略一：邏輯備份 - mysqldump 企業級最佳實踐

每日全量 + Binlog 增量 (標準 RPO=24h, RTO=小時級)

# 1. 全量備份腳本 (02:00 執行)
mysqldump --defaults-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --triggers --events \
  --hex-blob --master-data=2 --flush-logs \
  --all-databases | zstd -T0 -19 > /backup/full/full_$(date +%F).sql.zst

# 2. Binlog 持續歸檔 (獨立進程)
mysqlbinlog --raw --stop-never --host=master --user=repl --password \
  --result-file=/backup/binlog/ mysql-bin.000001

# 3. 恢復流程
# 停止應用寫入 → 還原全量 → 重放 Binlog 至目標時間 → 啟動應用
mysql < full_2024-01-15.sql.zst
mysqlbinlog /backup/binlog/mysql-bin.000123 --start-position=456 | mysql

大表並行備份與還原 (mydumper/myloader)

# 安裝
dnf install -y mydumper  # 或從 GitHub Release 下載最新版

# 並行導出 (8 線程，每表獨立檔案)
mydumper --defaults-file=/etc/mysql/backup.cnf \
  --threads=8 --compress --build-empty-files \
  --rows=1000000 --regex '^(app_prod|app_logs)\.' \
  --outputdir /backup/mydumper/$(date +%F)

# 並行導入 (8 線程)
myloader --defaults-file=/etc/mysql/restore.cnf \
  --threads=8 --overwrite-tables \
  --directory /backup/mydumper/2024-01-15

策略二：物理備份 - Percona XtraBackup (熱備份、不鎖表、InnoDB 專用)

安裝與基礎備份

# 安裝 (Percona 官方源)
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-80

# 完全備份 (需備份帳號有 RELOAD, LOCK TABLES, REPLICATION CLIENT, PROCESS, SUPER)
xtrabackup --defaults-file=/etc/mysql/backup.cnf \
  --user=backup_user --password='***' \
  --backup --target-dir=/backup/xtrabackup/full_$(date +%F) \
  --parallel=4 --compress --compress-threads=2

# 備份完成後輸出：xtrabackup: Transaction log of lsn (xxxx) to (yyyy) was copied.
# 必須記錄 xtrabackup_checkpoints 檔案中的 to_lsn 供增量基準

增量備份鏈

# 第 1 次增量 (基於全量 to_lsn=12345678)
xtrabackup --backup --target-dir=/backup/xtrabackup/inc1 \
  --incremental-basedir=/backup/xtrabackup/full_2024-01-15 \
  --parallel=4 --compress

# 第 2 次增量 (基於 inc1 to_lsn=12345999)
xtrabackup --backup --target-dir=/backup/xtrabackup/inc2 \
  --incremental-basedir=/backup/xtrabackup/inc1 \
  --parallel=4 --compress

還原流程 (Prepare → Copy-back)

# 1. 準備全量備份 (重放 Redo Log 達一致性)
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_2024-01-15 \
  --use-memory=4G

# 2. 合併增量鏈 (依序準備每層)
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_2024-01-15 \
  --incremental-dir=/backup/xtrabackup/inc1 --use-memory=4G
xtrabackup --prepare --target-dir=/backup/xtrabackup/full_2024-01-15 \
  --incremental-dir=/backup/xtrabackup/inc2 --use-memory=4G

# 3. 停止 MySQL、清空 datadir、複製回
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

策略三：檔案級快照 (LVM/ZFS/雲端快照) - 秒級備份

# LVM 快照範例 (需 /var/lib/mysql 在獨立 LV)
lvcreate -L 10G -s -n mysql_snap_$(date +%F) /dev/vg0/mysql_lv
# 掛載快照
mount /dev/vg0/mysql_snap_2024-01-15 /mnt/snap
# 從快照複製/rsync 到備份儲存
rsync -av /mnt/snap/ /backup/lvm_snap/2024-01-15/
umount /mnt/snap
lvremove -f /dev/vg0/mysql_snap_2024-01-15

# ZFS 更簡單
zfs snapshot zpool/mysql@backup_$(date +%F)
zfs send zpool/mysql@backup_2024-01-15 | zstd -T0 -19 > /backup/zfs/2024-01-15.zst

# AWS EBS / Azure Disk / GCP Persistent Disk - 控制台/API 一鍵快照
# 注意：快照前需 FLUSH TABLES WITH READ LOCK (或 XtraBackup --ftwrl-wait-timeout=0 配合)
# 8.0+ 可用 LOCK INSTANCE FOR BACKUP (輕量級鎖，僅阻塞 DDL/檔案操作)

檔案級複製備份 (舊文檔方法) - 僅限極受限環境

⚠️ 嚴格前提條件

• MySQL **完全停止** (`systemctl stop mysqld`)，確保無寫入、所有頁面刷新至磁碟
• 源/目標 **相同大版本、相同編譯參數、相同 my.cnf 關鍵參數** (innodb_page_size, innodb_log_file_size 等)
• **相同架構** (x86_64 ↔ x86_64)，不支援跨平台
• 複製 **整個 datadir** (含 ibdata1, ib_logfile*, *.ibd, mysql/, performance_schema/ 等)

操作步驟

# 1. 停止服務
systemctl stop mysqld

# 2. 複製資料目錄 (保留權限、擁有者、時間戳)
rsync -avHAX /var/lib/mysql/ /mnt/backup/mysql_$(date +%F)/
# 或 tar 打包
tar -czf /backup/mysql_$(date +%F).tar.gz -C /var/lib/mysql .

# 3. 啟動服務
systemctl start mysqld

# 4. 目標機還原 (同版本 MySQL 已安裝、未啟動)
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -xzf /backup/mysql_2024-01-15.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

風險與限制

• **停機時間長** (複製 TB 級需小時)
• **無法增量** (每次全量)
• **版本綁定死** (升級 MySQL 需重新建立基準備份)
• **無壓縮/加密/驗證機制**
• **不支援 PITR (時間點恢復)**

備份策略選型矩陣

備份驗證鐵律 (不可妥協)

1. 每季度至少 1 次完整還原演練 於獨立測試環境
2. 關鍵表抽樣比對：`CHECKSUM TABLE`、`COUNT(*)`、關鍵業務欄位抽樣
3. 備份檔案完整性：`sha256sum`、`zstd -t`、`gpg --decrypt --verify`
4. Binlog 連續性檢查：`mysqlbinlog --verify-binlog-checksum`
5. 文檔化恢復手冊：含 RTO/RPO 目標、聯繫人、決策樹、腳本參數