MySQL 備份無銀彈，需在 RPO/RTO、數據量、架構複雜度、團隊能力間權衡。本文對比三大主流策略優劣，給出中小型業務「邏輯備份 + 定時腳本 + 保留策略」的落地實現。
| 策略 | 原理 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| 邏輯備份 (mysqldump) | 導出 SQL 文本（DDL+INSERT） | 跨版本/跨平台/跨引擎可遷移；人類可讀；可單表/Where 過濾 | 備份慢（串行）、導入慢、大表鎖表久（非 --single-transaction）、佔磁盤大（文本膨脹） | 數據量 < 50GB、需異構遷移、團隊輕量 |
| 物理備份 (XtraBackup / cp data) | 拷貝數據文件（ibd/frm/ibdata1） | 備份快（流式）、導入快（直接啟動）、不阻塞寫入（XtraBackup）、壓縮率高 | 通常同版本/同架構/同參數才能還原；MyISAM 需鎖表；InnoDB 需 apply-log 步驟 | 數據量 > 50GB、同構環境、追求極致 RTO |
| 雙機熱備 (主從/群組複制) | Binlog 異步/半同步/同步複制 | 近實時 RPO≈0；可分流讀流量；故障秒級切換 | 架構複雜、需額外硬件、主從延遲/衝突處理、運維成本高 | 高可用核心業務、數據量大、團隊成熟 |
mysqldump --single-transaction 耗時 < 5 分鐘可接受/usr/local/bin/mysql_backup.sh#!/bin/bash
set -euo pipefail
# === 配置區 ===
DB_HOST="127.0.0.1"
DB_USER="backup_user"
DB_PASS="********"
DB_NAME="biz_db" # 留空或 --all-databases 為全庫
BACKUP_DIR="/data/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%F_%H%M)
LOG_FILE="$BACKUP_DIR/backup.log"
BIN_DIR="/usr/bin"
# === 執行區 ===
mkdir -p "$BACKUP_DIR"
DUMP_FILE="$BACKUP_DIR/full_$DATE.sql"
echo "[$(date '+%F %T')] START backup $DB_NAME -> $DUMP_FILE" >> "$LOG_FILE"
$BIN_DIR/mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" \
--single-transaction --quick --routines --triggers --events \
--master-data=2 --flush-logs --hex-blob \
${DB_NAME:+--databases "$DB_NAME"} > "$DUMP_FILE"
if [ ${PIPESTATUS[0]} -eq 0 ]; then
gzip "$DUMP_FILE"
echo "[$(date '+%F %T')] SUCCESS $(basename "$DUMP_FILE").gz" >> "$LOG_FILE"
else
echo "[$(date '+%F %T')] FAILED mysqldump exit code ${PIPESTATUS[0]}" >> "$LOG_FILE"
exit 1
fi
# === 清理舊備份 ===
find "$BACKUP_DIR" -name 'full_*.sql.gz' -mtime +$RETENTION_DAYS -delete >> "$LOG_FILE" 2>&1
echo "[$(date '+%F %T')] CLEANUP done" >> "$LOG_FILE"
echo "------------------------------------------------------------" >> "$LOG_FILE"crontab -e
# 30 4 * * * /usr/local/bin/mysql_backup.shrclone copy $BACKUP_DIR remote:bucket/mysql/mysql < dump.sql 驗證可還原性mysqlbinlog --read-from-remote-server 定時拉取backup.log 關鍵字 FAILED 推送钉钉/企業微信/郵件| 坑點 | 後果 | 對策 |
|---|---|---|
未用 --single-transaction | 大表長時間鎖表，業務寫入阻塞 | InnoDB 必加 |
| 備份用戶權限過大 | 洩露風險 | 僅授 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT |
| 磁盤寫滿 | 備份中斷、甚至損壞數據目錄 | 監控磁盤使用率 < 80%，預留空間 |
| 從未測試還原 | 故障時發現備份不可用 | 季度演練全量還原 |
備份的價值在於「可還原」。從邏輯備份起步，建立「備份→壓縮→異地→驗證→清理」閉環，是數據安全的基石。
