MySQL 備份與恢復策略全解析
四大備份策略對比
| 策略 | 類型 | 適用場景 | 優點 | 缺點 |
|---|
| 直接拷貝檔案 | 物理冷備份 | 小型、可停機 | 速度最快、最簡單 | 需停機、無增量、跨版本遷移困難 |
| mysqlhotcopy | 物理熱備份 | MyISAM、小型庫 | 速度快、支援單表 | 僅限 MyISAM、需 Perl 環境、本機執行 |
| mysqldump | 邏輯熱備份 | 中型、全引擎 | 跨版本、可壓縮、支援增量(配合 Binlog) | 大數據量慢、恢復慢 |
| 主從複製 | 即時熱備份 | 大型、高可用 | 即時、不影響主庫、可做讀寫分離 | 架構複雜、非邏輯備份(誤刪需延遲從庫) |
策略一：mysqldump 邏輯備份（最通用）
完整備份腳本範例
#!/bin/bash
# config
DB_USER="root"
DB_PASS="password"
DB_HOST="localhost"
BACKUP_DIR="/backup/mysql/$(date +%F)"
RETENTION_DAYS=7
mkdir -p "$BACKUP_DIR"
# 全庫備份(含存儲過程、觸發器、事件)
mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASS \
--single-transaction --routines --triggers --events \
--all-databases | gzip > "$BACKUP_DIR/full_$(date +%H%M).sql.gz"
# 刪除舊備份
find /backup/mysql -type f -mtime +$RETENTION_DAYS -delete
關鍵參數解析
--single-transaction：InnoDB 熱備份關鍵，獲取一致性快照不鎖表--master-data=2：輸出 Binlog 位置作註解，便於增量恢復--routines --triggers --events：包含存儲程序、觸發器、事件--hex-blob：二進制欄位用十六進制導出，避免編碼問題
增量備份策略（配合 Binlog）
- 完整備份時記錄 Binlog 位置：
SHOW MASTER STATUS;
- 定期刷新 Binlog：
FLUSH LOGS;（產生新 Binlog 檔案）
- 備份舊 Binlog 檔案（增量）
- 恢復時：先恢復全量 → 再用
mysqlbinlog 重放增量 Binlog
策略二：xtrabackup 物理熱備份（大數據量首選）
Percona XtraBackup 是開源免費的 InnoDB 物理熱備份工具，支援：
- 完全不阻塞寫入（僅最後階段短暫鎖表複製非 InnoDB 檔案）
- 增量備份（基於 LSN）
- 壓縮、加密、流式傳輸
- 恢復速度比 mysqldump 快數倍
安裝與基本用法
# 安裝
yum install percona-xtrabackup-24 # 或 apt-get
# 完全備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=root --password=xxx
# 準備(應用事務日誌，使資料一致)
xtrabackup --prepare --target-dir=/backup/full_$(date +%F)
# 增量備份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full_$(date +%F)
# 恢復：準備完整備份 → 應用增量 → 複製回 datadir → chown → 啟動
策略三：主從複製作為即時備份
- 架構：Master (寫) → Slave (讀/備份)
- 在 Slave 執行
mysqldump 或 xtrabackup，零影響 Master
- 關鍵補充：部署一個延遲從庫（
CHANGE MASTER TO MASTER_DELAY=3600;），防止誤操作(如 DROP TABLE)即時同步導致資料遺失
恢復演練與驗證（關鍵）
- 定期(月/季)在測試環境進行全量恢復演練
- 驗證資料完整性：核對表行數、關鍵業務指標
- 記錄恢復時間目標 (RTO) 與恢復點目標 (RPO)
- 備份檔案校驗：
md5sum、gzip -t
生產環境建議組合
| 數據量 | 推薦組合 | 備份頻率 | 保留策略 |
|---|
| < 10 GB | mysqldump 全量 + Binlog 增量 | 每日全量 + 每小時 Binlog | 全量 30 天、Binlog 7 天 |
| 10-500 GB | xtrabackup 全量 + 增量 | 每週全量 + 每日增量 | 全量 4 週、增量 2 週 |
| > 500 GB | xtrabackup + 延遲從庫 + 快照(存儲層) | 每週全量 + 每日增量 + 即時複製 | 視 RPO/RTO 定 |
避坑指南
- ❌ 直接
tar 數據目錄而不鎖表/停機 → 資料不一致
- ❌ 僅備份 .ibd/.frm 檔案缺少 ibdata1/redo log → 無法恢復
- ❌ 從未測試過恢復流程 → 事故時發現備份不可用
- ✅ 異地備份（跨可用區/跨雲廠商）
- ✅ 加密備份檔案（
openssl enc -aes-256-cbc）
- ✅ 監控備份任務成功與否、檔案大小異常
