萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Linux MySQL 自動備份腳本詳解:crontab 定時任務、邏輯/物理備份、保留策略與監控

Linux 下 MySQL 自動備份依賴 crontab 定時調度 Shell 腳本。本文提供兩套生產級備份腳本:邏輯備份(mysqldump+壓縮)與物理備份,涵蓋多庫並發、錯誤處理、日誌記錄、保留策略、監控集成完整流程。

crontab 基礎與編輯

# 編輯當前用戶 crontab
crontab -e

# 系統級 crontab(需 root)
vi /etc/crontab

# 格式:分 時 日 月 周 命令
# 示例:每天凌晨 2:00 執行
0 2 * * * /bin/bash /scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

時間表達式速查

頻率表達式
每 5 分鐘*/5 * * * *
每小時0 * * * *
每天凌晨0 0 * * *
每周日0 0 * * 0
每月 1 號0 0 1 * *
每年 1 月 1 號0 0 1 1 *

腳本一:邏輯備份(mysqldump + 多庫並發 + 壓縮 + 保留)

#!/bin/bash
# /scripts/mysql_backup.sh
# 功能:多庫邏輯備份、壓縮、保留最近 N 天、日誌記錄
# 依賴:mysqldump, pigz(並行壓縮), find

set -euo pipefail

# ===== 配置區 =====
DB_USER="backup_user"
DB_PASS_FILE="/etc/mysql/backup.pass"  # 權限 600,僅存密碼
DB_HOST="127.0.0.1"
DB_PORT="3306"
DATABASES=("db1" "db2" "db3")           # 空數組 = 所有用戶庫(排除系統庫)
BACKUP_BASE="/backup/mysql"
RETENTION_DAYS=7
COMPRESSION_THREADS=4
LOG_FILE="/var/log/mysql_backup.log"
MYSQLDUMP_OPTS="--single-transaction --quick --hex-blob --routines --triggers --events --skip-lock-tables"
# ===================

mkdir -p "$BACKUP_BASE"
exec >>"$LOG_FILE" 2>&1

log() { echo "[$(date '+%F %T')] $*"; }

log "=== Backup START ==="

# 讀取密碼
DB_PASS=$(cat "$DB_PASS_FILE")

# 確定備份庫列表
if [ ${#DATABASES[@]} -eq 0 ]; then
    mapfile -t DATABASES < <(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW DATABASES;" | grep -Ev "^(mysql|information_schema|performance_schema|sys)$")
fi

BACKUP_DATE=$(date '+%Y%m%d_%H%M%S')
BACKUP_DIR="$BACKUP_BASE/$BACKUP_DATE"
mkdir -p "$BACKUP_DIR"

# 並發備份每個庫
for db in "${DATABASES[@]}"; do
    log "Backing up database: $db"
    OUT_FILE="$BACKUP_DIR/${db}_${BACKUP_DATE}.sql"
    
    if mysqldump -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" $MYSQLDUMP_OPTS "$db" > "$OUT_FILE"; then
        log "  Dump OK, compressing..."
        pigz -p "$COMPRESSION_THREADS" "$OUT_FILE"
        log "  Compressed: ${OUT_FILE}.gz"
    else
        log "  Dump FAILED for $db"
        rm -f "$OUT_FILE"
    fi
done

# 清理舊備份
log "Cleaning backups older than $RETENTION_DAYS days..."
find "$BACKUP_BASE" -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

log "=== Backup END ==="

腳本二:物理熱備份(Percona XtraBackup)

#!/bin/bash
# /scripts/xtrabackup_full.sh
# 依賴:xtrabackup, qpress

set -euo pipefail

BACKUP_DIR="/backup/xtrabackup"
FULL_DIR="$BACKUP_DIR/full_$(date '+%Y%m%d_%H%M%S')"
INCR_BASE="$BACKUP_DIR/incr_base"
RETENTION=2  # 保留 2 個全備為基準

mkdir -p "$FULL_DIR"
xtrabackup --backup --target-dir="$FULL_DIR" --user=backup_user --password="$(cat /etc/mysql/backup.pass)" --compress --compress-threads=4 --parallel=4

# 準備(應用日誌)
xtrabackup --prepare --target-dir="$FULL_DIR" --use-memory=4G

# 清理舊增量(保留最近 2 個全備鏈)
# 實際生產建議用 xtrabackup --backup --incremental-basedir=... 做增量鏈

備份驗證與恢復演練(關鍵)

# 邏輯備份驗證
gunzip -c backup.sql.gz | mysql -u root -p test_db

# 物理備份恢復演練(測試環境)
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/xtrabackup/full_20240115_020000
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

監控集成(Prometheus + Node Exporter + 自定義腳本)

# /scripts/mysql_backup_exporter.sh
# 輸出文本格式指標供 node_exporter textfile collector 採集
cat <<EOF > /var/lib/prometheus/node.d/mysql_backup.prom
mysql_backup_last_success_timestamp $(date -d "$(grep 'Backup END' /var/log/mysql_backup.log | tail -1 | cut -d']' -f1 | tr -d '[')" +%s)
mysql_backup_last_size_bytes $(du -sb /backup/mysql/latest | cut -f1)
mysql_backup_duration_seconds $(grep -A1 'Backup START' /var/log/mysql_backup.log | tail -1 | ...)
EOF

常見問題排查

現象原因解決
crontab 不執行環境變量缺失(PATH)、腳本無執行權限、Shebang 錯誤腳本內寫絕對路徑、chmod +x、#!/bin/bash
備份文件為空/極小密碼錯誤、權限不足、mysqldump 版本不匹配手動跑一次腳本核心命令觀察輸出
大表備份卡住/超時無 --quick、網路抖動、鎖等待加 --quick --single-transaction --max-allowed-packet=1G
壓縮極慢單線程 gzip用 pigz 並行壓縮
磁碟寫滿保留策略失效、備份目錄未掛載獨立卷獨立卷掛載、find -mtime 定時清理、磁碟告警

企業級備份架構建議

  • 分層備份:日增量(Binlog/xtrabackup 增量)+ 週全量(xtrabackup/mysqldump)+ 月歸檔(冷存儲/OSS)
  • 異地容災:備份文件同步至異地機房/雲存儲(rsync/rclone/OSS SDK)
  • 備份加密:openssl enc -aes-256-gcm 或 gpg 加密後再上傳
  • RPO/RTO 量化:明確業務容忍丟失數據量(RPO)與恢復時間(RTO),選型工具與頻率

將備份從「跑個腳本」升級為「可觀測、可驗證、可恢復、合規」的工程體系,是數據資產安全的核心保障。

copyright © 萬盛學電腦網 all rights reserved