您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 全庫備份自動化腳本：mysqldump + gzip 壓縮 + 日期目錄 + 異地同步

編輯：MySQL 資料庫相關知識

生產環境 MySQL 備份核心需求：全庫覆蓋、壓縮存儲、按日期歸檔、異地容災、保留策略、靜默運行。本文提供生產級 Bash 腳本，含配置區塊、異常處理、日誌記錄、rsync 異地同步、crontab 定時部署全鏈路。

腳本核心特性

全庫遍歷：自動識別 /var/lib/mysql 下所有庫 (排除 performance_schema、information_schema、sys、mysql 可選)。
熱備份無鎖：--single-transaction (InnoDB) 保證一致性視圖。
管道壓縮：mysqldump ... | gzip -c > file.sql.gz 落盤即壓縮，無中間文件。
日期目錄：/backup/mysql/YYYYMMDD/ 自動創建。
異地同步：rsync -avz --delete 推送至備份服務器/對象存儲掛載點。
保留清理：本地保留 N 天，遠端保留 M 天 (通常 M > N)。
靜默/日誌雙模式：crontab 靜默、手動執行輸出進度。

生產級備份腳本 (backup_mysql.sh)

#!/bin/bash
# MySQL 全庫備份自動化腳本
# 用法: ./backup_mysql.sh [--quiet]
# 作者: 運維團隊
# 版本: 2.1

set -euo pipefail

# ========== 配置區塊 (按環境修改) ==========
BACKUP_BASE_DIR="/backup/mysql"
MYSQL_USER="backup_user"
MYSQL_PASS="StrongBackupPass123"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_DEFAULTS_FILE="/etc/mysql/backup.cnf"  # 推薦用配置文件避免明文密碼
# MYSQL_DEFAULTS_FILE 內容:
# [client]
# user=backup_user
# password=StrongBackupPass123
# host=127.0.0.1
# port=3306

EXCLUDE_DBS="('performance_schema','information_schema','sys','mysql')"  # 可選排除系統庫
KEEP_LOCAL_DAYS=7
KEEP_REMOTE_DAYS=30
REMOTE_SYNC_ENABLED=true
REMOTE_USER="backup_sync"
REMOTE_HOST="backup-server.example.com"
REMOTE_DIR="/mnt/backups/mysql/$(hostname -s)"
LOG_FILE="/var/log/mysql_backup.log"
# ===========================================

QUIET_MODE=false
[[ "${1:-}" == "--quiet" ]] && QUIET_MODE=true

log() {
    local level="$1"; shift
    local msg="$*"
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $msg" | tee -a "$LOG_FILE"
}

# 靜默模式下只記錄錯誤
if $QUIET_MODE; then
    exec 1>/dev/null
    exec 2>>"$LOG_FILE"
fi

log "INFO" "=== MySQL 備份開始 ==="

# 1. 準備目錄
TODAY=$(date '+%Y%m%d')
BACKUP_DIR="$BACKUP_BASE_DIR/$TODAY"
mkdir -p "$BACKUP_DIR"

# 2. 獲取庫列表 (排除系統庫)
DBS=$(mysql --defaults-file="$MYSQL_DEFAULTS_FILE" -N -e "SHOW DATABASES" | grep -vE "^(performance_schema|information_schema|sys|mysql)$")

if [[ -z "$DBS" ]]; then
    log "ERROR" "未獲取到任何用戶庫，退出"
    exit 1
fi

# 3. 遍歷備份
for DB in $DBS; do
    log "INFO" "備份庫: $DB"
    BACKUP_FILE="$BACKUP_DIR/${DB}_${TODAY}.sql.gz"

    # 管道備份+壓縮
    if mysqldump --defaults-file="$MYSQL_DEFAULTS_FILE" \
        --single-transaction --quick --routines --triggers --events --hex-blob \
        --databases "$DB" | gzip -c > "$BACKUP_FILE"; then
        log "INFO" "  成功: $BACKUP_FILE ($(du -h "$BACKUP_FILE" | cut -f1))"
    else
        log "ERROR" "  失敗: $DB"
        rm -f "$BACKUP_FILE"
    fi
done

# 4. 本地舊備份清理
log "INFO" "清理本地 $KEEP_LOCAL_DAYS 天前備份"
find "$BACKUP_BASE_DIR" -type d -mtime +$KEEP_LOCAL_DAYS -exec rm -rf {} + 2>/dev/null || true

# 5. 異地同步
if $REMOTE_SYNC_ENABLED; then
    log "INFO" "同步至異地: $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR"
    if rsync -avz --delete "$BACKUP_BASE_DIR/" "$REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR/" >>"$LOG_FILE" 2>&1; then
        log "INFO" "異地同步成功"
        # 遠端清理 (需遠端有同腳本或手動配置)
        ssh "$REMOTE_USER@$REMOTE_HOST" "find '$REMOTE_DIR' -type d -mtime +$KEEP_REMOTE_DAYS -exec rm -rf {} +" 2>/dev/null || true
    else
        log "ERROR" "異地同步失敗"
    fi
fi

log "INFO" "=== MySQL 備份結束 ==="

部署與驗證

1. 創建備份專用用戶 (最小權限)

CREATE USER 'backup_user'@'127.0.0.1' IDENTIFIED BY 'StrongBackupPass123';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, PROCESS ON *.* TO 'backup_user'@'127.0.0.1';
FLUSH PRIVILEGES;

2. 配置文件替代明文密碼

# /etc/mysql/backup.cnf
[client]
user=backup_user
password=StrongBackupPass123
host=127.0.0.1
port=3306
# 權限 600
chmod 600 /etc/mysql/backup.cnf

3. 異地免密鍵登錄

# 備份服務器生成鍵對
ssh-keygen -t ed25519
# 推送公鑰到遠端
ssh-copy-id backup_user@backup-server.example.com

4. Crontab 定時任務 (每日 02:30 低峰期)

# crontab -e
30 2 * * * /opt/scripts/backup_mysql.sh --quiet

5. 手動測試驗證

/opt/scripts/backup_mysql.sh
# 查看日誌
tail -f /var/log/mysql_backup.log
# 驗證備份文件完整性
gunzip -t /backup/mysql/20240115/mydb_20240115.sql.gz && echo "OK"
# 試恢復單庫
gunzip -c /backup/mysql/20240115/mydb_20240115.sql.gz | mysql -u root -p mydb_test

進階擴展建議

並行備份：大庫多表用 mydumper (多線程) 替代 mysqldump。
增量備份：配合 Binlog (GTID) + mysqlbinlog 實時增量，RPO 降至秒級。
對象存儲直傳：aws s3 cp / ossutil cp 替換 rsync，無需維護備份服務器。
監控告警：腳本返回碼非 0 → 推送鉤釘/企業微信/Postmark；備份文件大小異常 (如 < 1KB) → 告警。
加密備份：gpg --symmetric --cipher-algo AES256 或 openssl enc -aes-256-cbc 加密後再上傳。