MySQL 自動備份與資料損毀恢復實戰：mysqldump + 定時任務 + 還原演練完整流程

備份策略設計原則

• 3-2-1 原則：3 份副本、2 種介質、1 份異地
• RPO/RTO 量化：RPO (資料遺失容忍) = 備份頻率；RTO (恢復時間) = 還原演練實測
• 分層備份：
  • 邏輯備份：`mysqldump` (全量/單庫/單表)、`SELECT INTO OUTFILE`
  • 物理備份：`Percona XtraBackup` (熱備份、增量、流式)
  • Binlog 歸檔：Point-in-Time Recovery (PITR) 關鍵

方案一：mysqldump 邏輯備份腳本 (中小型資料庫 < 50GB)

備份腳本 backup_mysql.sh

#!/bin/bash
# 配置區
MYSQL_HOST="localhost"
MYSQL_USER="backup_user"
MYSQL_PASS="StrongBackupPass123!"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%F_%H-%M-%S)
LOG_FILE="${BACKUP_DIR}/backup_${DATE}.log"

# 確保目錄存在
mkdir -p ${BACKUP_DIR}

# 記錄開始
echo "[$(date)] 開始備份" | tee -a ${LOG_FILE}

# 1. 獲取所有業務資料庫 (排除系統庫)
DATABASES=$(mysql -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW DATABASES;" | grep -Ev "^(Database|mysql|information_schema|performance_schema|sys)$")

# 2. 逐庫備份 (單檔案、含建表語句、單一事務、Binlog 位置)
for DB in ${DATABASES}; do
    echo "[$(date)] 備份資料庫: ${DB}" | tee -a ${LOG_FILE}
    mysqldump -h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} \
        --single-transaction \
        --master-data=2 \
        --routines \
        --triggers \
        --events \
        --hex-blob \
        --quick \
        --max_allowed_packet=512M \
        --databases ${DB} \
        | gzip > ${BACKUP_DIR}/${DB}_${DATE}.sql.gz
    
    if [ ${PIPESTATUS[0]} -eq 0 ]; then
        echo "[$(date)] ${DB} 備份成功" | tee -a ${LOG_FILE}
    else
        echo "[$(date)] ${DB} 備份失敗!" | tee -a ${LOG_FILE}
        exit 1
    fi
done

# 3. 刪除過期備份
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
echo "[$(date)] 清理 ${RETENTION_DAYS} 天前備份完成" | tee -a ${LOG_FILE}

# 4. 驗證備份檔案完整性 (抽樣)
LATEST=$(ls -t ${BACKUP_DIR}/*.sql.gz | head -1)
if gzip -t ${LATEST}; then
    echo "[$(date)] 最新備份檔案完整性驗證通過" | tee -a ${LOG_FILE}
else
    echo "[$(date)] 警告: 備份檔案損壞!" | tee -a ${LOG_FILE}
    exit 1
fi

echo "[$(date)] 備份流程結束" | tee -a ${LOG_FILE}

關鍵參數解析

定時任務配置 (crontab)

# 每天凌晨 2:30 執行全量備份
30 2 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup_cron.log 2>&1

# 每小時增量備份 (需配合 Binlog)
# 0 * * * * /usr/local/bin/backup_binlog.sh

方案二：資料損毀場景恢復演練

場景 1：整庫誤刪 (DROP DATABASE)

# 1. 停止應用寫入
# 2. 找到最新全量備份 + 該時間點後的 Binlog
LATEST_BACKUP=/backup/mysql/mydb_2024-01-15_02-30-00.sql.gz
BINLOG_START_POS=...  # 從備份檔案頭部 CHANGE MASTER TO 註解獲取

# 3. 還原全量備份
gunzip -c ${LATEST_BACKUP} | mysql -u root -p

# 4. 重放 Binlog (PITR)
mysqlbinlog --start-position=${BINLOG_START_POS} /var/lib/mysql/mysql-bin.0000xx | mysql -u root -p

場景 2：單表誤刪/誤更新 (DROP TABLE / 錯誤 UPDATE)

# 方法 A：從全量備份中提取單表 (適用小表)
# 1. 解壓備份
gunzip -c mydb_2024-01-15_02-30-00.sql.gz > /tmp/full.sql

# 2. 提取目標表段落 (從 CREATE TABLE 到下一個 CREATE TABLE)
sed -n '/^CREATE TABLE `target_table`/,/^CREATE TABLE `/p' /tmp/full.sql | head -n -1 > /tmp/target_table.sql

# 3. 修正表名 (避免衝突)
sed -i 's/`target_table`/`target_table_recovered`/g' /tmp/target_table.sql

# 4. 導入
mysql -u root -p mydb < /tmp/target_table.sql

# 5. 校驗資料後，RENAME TABLE 互換
mysql -u root -p mydb -e "RENAME TABLE target_table TO target_table_corrupted, target_table_recovered TO target_table;"

場景 3：資料檔案損壞 (ibdata1/ibd 損壞、磁碟壞軌)

# 1. 立即備份損壞檔案 (防二次損壞)
cp -a /var/lib/mysql /backup/mysql_corrupted_$(date +%F)

# 2. 嘗試 innodb_force_recovery (1-6 級漸進)
# my.cnf
[mysqld]
innodb_force_recovery = 3  # 從 1 開始嘗試，3 通常能導出大部分資料

# 3. 啟動 MySQL 導出資料
mysqldump -u root -p --all-databases > /tmp/recovery_dump.sql

# 4. 重建乾淨實例
# - 停止 MySQL
# - 刪除 /var/lib/mysql/*
# - 初始化新資料目錄
# - 導入 recovery_dump.sql

還原驗證清單 (每次演練必檢)

進階：XtraBackup 物理熱備份 (大型資料庫 > 50GB)

# 全量備份
xtrabackup --backup --target-dir=/backup/full_$(date +%F) --user=backup_user --password=xxx

# 增量備份 (基於上次全量/增量)
xtrabackup --backup --target-dir=/backup/inc_$(date +%F_%H) --incremental-basedir=/backup/full_2024-01-15 --user=backup_user --password=xxx

# 準備 (合併增量到全量)
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_2024-01-15
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_2024-01-15 --incremental-dir=/backup/inc_2024-01-15_06
# ... 依序合併所有增量

# 還原 (需停止 MySQL、清空 datadir)
xtrabackup --copy-back --target-dir=/backup/full_2024-01-15
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

備份系統監控與告警

# Prometheus 指標範例
mysql_backup_last_success_timestamp{database="mydb"} 1705321800
mysql_backup_size_bytes{database="mydb"} 1073741824
mysql_backup_duration_seconds{database="mydb"} 320

# 告警規則
- alert: MySQLBackupFailed
  expr: time() - mysql_backup_last_success_timestamp > 86400
  for: 0m
  labels:
    severity: critical
  annotations:
    summary: "MySQL 備份超過 24 小時未成功"

- alert: MySQLBackupSizeAnomaly
  expr: mysql_backup_size_bytes / mysql_backup_size_bytes offset 1d > 2
  for: 0m
  labels:
    severity: warning
  annotations:
    summary: "備份體積異常暴增 (可能包含異常資料)"

總結：建立可信賴的備份體系

1. 自動化：腳本化、定時任務、零人工干預
2. 分層：邏輯備份 (日/週) + Binlog 歸檔 (時) + 物理備份 (大庫)
3. 異地：備份檔案同步至異地儲存 (S3/OSS/NFS/異地機房)
4. 加密：敏感資料備份檔案 AES-256 加密 (openssl/gpg)
5. 演練：**每季度至少 1 次完整還原演練**，記錄 RTO、發現問題、優化流程
6. 文件化：備份/還原 SOP、聯繫人、決策樹、風險登記

「備份不演練，等於沒備份。」只有經過實戰驗證的還原流程，才能在災難來臨時成為挽救業務的最後防線。