Linux 下 MySQL 自動備份依賴 crontab 定時調度 Shell 腳本。本文提供兩套生產級備份腳本:邏輯備份(mysqldump+壓縮)與物理備份,涵蓋多庫並發、錯誤處理、日誌記錄、保留策略、監控集成完整流程。
# 編輯當前用戶 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 * |
#!/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 ==="
#!/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
# /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 定時清理、磁碟告警 |
openssl enc -aes-256-gcm 或 gpg 加密後再上傳將備份從「跑個腳本」升級為「可觀測、可驗證、可恢復、合規」的工程體系,是數據資產安全的核心保障。