MySQL 備份策略三大類型核心對比

1. 邏輯備份 — mysqldump

原理： 透過 SQL 語句重現資料結構與資料，輸出 .sql 文字檔。

優點	缺點
跨平台、跨版本、跨儲存引擎完美相容 人類可讀，易於修改、審計、部分還原 線上熱備份：配合 --single-transaction (InnoDB) 可不鎖表 支援壓縮、加密、遠端傳輸	備份/還原速度慢（需解析 SQL） 大資料庫（>100GB）耗時極長 MyISAM 需 --lock-all-tables 造成寫入阻塞

關鍵參數：

mysqldump --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --quick --compress \
  -u backup_user -p db_name > backup_$(date +%F).sql

2. 物理備份 — 檔案層級複製

原理： 直接複製資料目錄（datadir）或使用 Percona XtraBackup / MySQL Enterprise Backup。

優點	缺點
備份/還原極快（單純 I/O 操作） 不影響資料庫效能（XtraBackup 熱備） 支援增量備份、壓縮、加密、串流	跨平台/版本遷移困難 需相同 MySQL 版本、相同編譯選項、相同儲存引擎 MyISAM 需鎖表/停機；InnoDB 需專用工具 無法單獨還原單一資料表

XtraBackup 熱備指令：

xtrabackup --backup --target-dir=/backup/full_$(date +%F) \
  --user=backup_user --password=xxx --compress --compress-threads=4

3. 雙機熱備（主從複寫）— 高可用架構

架構： Master 寫入 Binlog → Slave 透過 IO Thread 拉取 → Relay Log → SQL Thread 重放

優點	缺點/挑戰
近即時備份，RPO 接近 0 讀寫分離分攤查詢壓力 Slave 可作離線備份、報表查詢、升級測試 Master 故障可快速切換（配合 MHA/Orchestrator/GTID）	複寫延遲（大事務、DDL、單執行緒 SQL Thread） 資料一致性風險（非同步/半同步模式） 運維複雜度高：故障切換、錯誤跳過、Schema 變更 硬體成本翻倍

實戰策略選型決策樹

1. 資料量 < 50GB、需跨平台遷移、容忍分鐘級 RPO → 邏輯備份 (mysqldump)
2. 資料量 > 100GB、追求極速還原、同構環境 → 物理備份 (XtraBackup)
3. 核心業務、要求高可用、RPO < 1min、RTO < 5min → 主從複寫 + 定期全備 + Binlog 歸檔
4. 雲端部署 → 直接使用雲廠商托管備份（RDS 自動備份、快照、PITR）

生產級邏輯備份自動化腳本實戰

#!/bin/bash
# mysql_backup.sh - 每日晨間 04:00 執行，保留 7 天

DB_NAME="prod_db"
DB_USER="backup_user"
DB_PASS="strong_password"
BIN_DIR="/usr/bin"
BACK_DIR="/data/backdata"
DATE=$(date +"%Y%m%d-%H%M%S")
LOGFILE="${BACK_DIR}/dbbakup.log"
BACKFILE="${BACK_DIR}/mysql-${DATE}.sql.gz"

# 確保備份目錄存在
mkdir -p "${BACK_DIR}"

# 執行備份（單一交易、壓縮、記錄二進位日誌位置）
${BIN_DIR}/mysqldump --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --quick \
  -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" | gzip > "${BACKFILE}"

# 記錄結果
if [ $? -eq 0 ]; then
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] SUCCESS: ${BACKFILE}" >> "${LOGFILE}"
else
  echo "[$(date +"%Y-%m-%d %H:%M:%S")] FAILED: mysqldump exited with $?" >> "${LOGFILE}"
  exit 1
fi

# 清理 7 天前備份
find "${BACK_DIR}" -name "mysql-*.sql.gz" -mtime +7 -delete -print | \
  while read f; do echo "[$(date +"%Y-%m-%d %H:%M:%S")] DELETED: ${f}" >> "${LOGFILE}"; done

echo "------------------------------------------------------------" >> "${LOGFILE}"

Crontab 排程設定

# 每日 04:00 執行備份
0 4 * * * /opt/scripts/mysql_backup.sh >/dev/null 2>&1

# 每週日 02:00 執行完整還原演練（到測試環境）
0 2 * * 0 /opt/scripts/restore_drill.sh >/dev/null 2>&1

備份驗證與災難演練 - 常被忽視的關鍵環節

• 校驗和驗證： 備份完成後計算 SHA256，上傳異地儲存前比對
• 定期還原演練： 每月至少一次完整還原到測試環境，驗證資料完整性
• 時間點恢復 (PITR) 測試： 結合 Binlog 還原到指定秒數
• 監控告警： 備份失敗、檔案大小異常、磁碟空間不足需觸發 PagerDuty/企業微信/釘釘告警

總結建議

「沒有驗證過的備份，等於沒有備份。」建議採用 分層備份策略：
📅 每日邏輯全備（跨平台遷移、審計）
⚡ 每日物理增量備份（快速還原）
🔄 即時主從複寫（高可用、讀寫分離）
☁️ 異地離線備份（勒索軟體防護、合規要求）