備份的核心價值：RPO 與 RTO
- RPO (Recovery Point Objective)：可容忍的最大資料遺失量（如 1 小時、24 小時）
- RTO (Recovery Time Objective)：從故障到服務恢復的最大允許時間
- 備份策略必須從業務 RPO/RTO 反推，而非技術導向
兩大備份路徑深度對比
| 維度 | mysqldump (邏輯備份) | 實體檔案複製 (Physical Backup) |
|---|
| 操作層級 | SQL 層（透過 Server） | 檔案系統層（繞過 Server） |
| 一致性保障 | 單交易快照（--single-transaction） | 需鎖表/停服務/快照工具 |
| 可移植性 | 極高（跨版本/架構/引擎） | 僅限相同版本、架構、引擎（MyISAM 需同端序） |
| 備份速度 | 較慢（解析/序列化/網路） | 極快（磁碟順序讀取） |
| 還原速度 | 慢（重建索引/解析 SQL） | 快（直接複製+崩潰恢復） |
| 資料粒度 | 庫/表/WHERE 條件任意組合 | 通常整個資料目錄 |
| 壓縮效果 | 極佳（文字壓縮率 80-90%） | 一般（二進位壓縮率 30-50%） |
mysqldump 核心參數速查表
| 參數 | 用途 | 建議場景 |
|---|
| --single-transaction | InnoDB 一致性快照（不鎖表） | 線上熱備份**必加** |
| --master-data=2 | 記錄 Binlog 位置（CHANGE MASTER TO 註解） | 搭建 Slave/PITR 基準點 |
| --flush-logs | 切換 Binlog，新日誌對應備份時間點 | 配合 --master-data 使用 |
| --lock-tables | 鎖定所有表（MyISAM 必備） | 僅 MyISAM/混合引擎環境 |
| --quick | 串流讀取不緩存全表 | 大表**必加**，防 OOM |
| --opt | 綜合優化（含 --quick --add-drop-table 等） | 常規備份**推薦** |
| --compress | 客戶端-伺服端壓縮傳輸 | 跨網路備份/異地備份 |
| --routines --triggers --events | 包含存儲程序/觸發器/事件 | 完整遷移**必加** |
| --hex-blob | 二進位欄位十六進位輸出 | 含 BLOB/BINARY 欄位時必加 |
生產級備份腳本範例（每日全量 + Binlog 增量）
#!/bin/bash
# /usr/local/bin/mysql_backup.sh
# 每日 02:00 執行，保留 30 天全量 + 7 天 Binlog
set -euo pipefail
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H-%M)
DB_USER="backup_user"
DB_PASS="******"
DB_HOST="127.0.0.1"
RETENTION_DAYS=30
# 1. 全量備份 (InnoDB 熱備份)
mysqldump --host=$DB_HOST --user=$DB_USER --password=$DB_PASS \
--single-transaction --master-data=2 --flush-logs \
--routines --triggers --events --hex-blob --quick \
--all-databases | gzip > "$BACKUP_DIR/full_$DATE.sql.gz"
# 2. 驗證備份完整性
if ! gzip -t "$BACKUP_DIR/full_$DATE.sql.gz"; then
echo "BACKUP CORRUPTED!" | mail -s "MySQL Backup Failed" admin@company.com
exit 1
fi
# 3. 清理舊備份
find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete
# 4. Binlog 歸檔 (需啟用 log_bin 與 expire_logs_days)
mysqladmin --host=$DB_HOST --user=$DB_USER --password=$DB_PASS flush-logs
# 實際生產建議用 mysqlbinlog --read-from-remote-server 串流歸檔
PITR (Point-In-Time Recovery) 實戰流程
- 找到故障時間點前最近的**全量備份**
- 還原全量備份：`gunzip < full_2024-01-15.sql.gz | mysql -u root -p`
- 從備份時刻的 Binlog 位置（`--master-data` 記錄）開始重放：
mysqlbinlog --start-position=12345 --stop-datetime="2024-01-15 14:30:00" \
mysql-bin.000123 mysql-bin.000124 | mysql -u root -p
- 驗證關鍵業務資料完整性
實體備份工具選型
| 工具 | 授權 | 支援引擎 | 特點 |
|---|
| Percona XtraBackup | 開源 (GPL) | InnoDB/XtraDB/MyISAM | 熱備份、增量、串流、壓縮、部分還原，生產首選 |
| MySQL Enterprise Backup | 商業 | InnoDB/MyISAM | 官方支援、GUI、加密、雲整合 |
| LVM/ZFS/btrfs 快照 | OS 內建 | 所有 | 秒級凍結、需檔案系統支援、一致性靠應用層配合 |
| cp/tar/rsync (冷備份) | OS 內建 | 所有 | 需停服務、最簡單、適合離線/測試環境 |
備份檔案管理鐵律
- 命名規範：`{env}_{type}_{db}_{YYYYMMDD_HHMM}.{sql|xb}.gz` 例：`prod_full_all_20240115_0200.sql.gz`
- 異地備份：至少兩地（本機 + 異地 NAS/雲存儲），不同可用區
- 加密儲存：`gpg --symmetric --cipher-algo AES256` 或雲端 KMS 加密
- 定期演練：**每季至少一次**完整還原演練，記錄 RTO 實測值
- Binlog 獨立歸檔：不可與全量備份同磁碟/同機器，防單點毀損
常見反模式與修正
| 反模式 | 風險 | 修正 |
|---|
| 只備份、不驗證、不演練 | 備份檔損壞/不完整發現於災難當下 | 自動化驗證 + 季度演練 |
| mysqldump 無 --single-transaction 對 InnoDB | 長時間鎖表凍結寫入、業務中斷 | 強制加參數、或改 XtraBackup |
| 備份與生產同磁碟/同伺服器 | 磁碟損壞/火災/勒索軟體同時毀滅備份 | 3-2-1 原則：3份、2介質、1異地 |
| 忽略 Binlog 歸檔 | 無法 PITR，RPO = 上次全量備份間隔 | 啟用 log_bin、配置 expire_logs_days、異地歸檔 |
結語
備份不是目的，可驗證、可演練、可在 RTO 內還原的備份才是資產。建議採用「XtraBackup 全量 + mysqlbinlog 串流增量」組合，配合自動化驗證管道與季度實戰演練，構建生產級資料韌性體系。
