MySQL 備份策略全景：邏輯備份 vs 物理備份、mysqldump 進階參數、恢複演練標準化

MySQL 備份分為邏輯備份 (SQL 文本) 與物理備份 (文件級)，兩者互補。本文系統對比優劣、講解 mysqldump 進階用法、XtraBackup 熱備原理、恢複演練標準化流程。

備份方式核心對比

mysqldump 進階參數組合 (生產級模板)

mysqldump -h localhost -u backup_user -p \
  --single-transaction \          # InnoDB 一致性熱備 (不鎖表)
  --master-data=2 \               # 註釋記錄 Binlog 位點 (CHANGE MASTER TO)
  --routines --triggers --events \ # 存儲過程/觸發器/事件
  --hex-blob \                    # 二進制列十六進制導出 (避免字符集問題)
  --quick \                       # 流式讀取不緩存結果集 (大表必加)
  --max_allowed_packet=1G \       # 大 BLOB/TEXT 支持
  --databases db1 db2 \           # 指定庫 (或 --all-databases)
  > /backup/logical/full_$(date +%F_%H%M).sql

XtraBackup 熱備標準流程

# 1. 完全備份 (不阻塞讀寫)
xtrabackup --backup --target-dir=/backup/physical/full_$(date +%F) \
  --user=backup_user --password=xxx --parallel=4 --compress --compress-threads=4

# 2. 準備 (回放 Redo Log 達到一致性)
xtrabackup --prepare --target-dir=/backup/physical/full_$(date +%F) --use-memory=4G

# 3. 增量備份 (基於 LSN)
xtrabackup --backup --target-dir=/backup/physical/inc_$(date +%F_%H) \
  --incremental-basedir=/backup/physical/full_$(date +%F) --user=... --parallel=4

# 4. 恢複 (合併增量到完全備份目錄)
xtrabackup --prepare --apply-log-only --target-dir=/backup/physical/full_$(date +%F)
xtrabackup --prepare --target-dir=/backup/physical/full_$(date +%F)  # 最後一次不加 --apply-log-only

# 5. 啟動實例 (修改 datadir 指向備份目錄)

恢複演練標準化 (每季度必演)

1. 環境隔離：獨立測試實例/容器，禁止污染生產
2. 全量恢複：邏輯備份 mysql < backup.sql / 物理備份直接啟動
3. 增量恢複 (PITR)：全量備份 + Binlog 重放至指定時間點 (mysqlbinlog --start-datetime=... --stop-datetime=... binlog.xxx | mysql)
4. 數據校驗：核心表行數、關鍵業務指標 (金額/用戶數/訂單數) 與生產一致性比對
5. 應用驗證：掛載測試庫跑自動化回歸測試 / 手動核心流程冒煙測試
6. 記錄報告：耗時 (RTO)、數據損失量 (RPO)、發現問題、改進措施

備份保留策略 (祖父-父-子 GFS)

• 每日增量/邏輯備份 (保留 7 天)
• 每周完全備份 (保留 4 周)
• 每月完全備份 (保留 12 月)
• 年度歸檔 (冷存儲/磁帶/對象存儲 IA/Archive 級)