MySQL数据库自动备份脚本

#!/bin/bash # ========================================== # MySQL数据库自动备份脚本 # 用途：定时备份MySQL数据库，自动压缩，清理旧备份 # 使用方法： # 1. 修改下面的配置项 # 2. bash mysql_backup.sh # 3. 可加入crontab定时执行 # ========================================== # ========== 配置项 ========== DB_USER="root" # 数据库用户名 DB_PASS="your_password" # 数据库密码 DB_HOST="localhost" # 数据库地址 DB_PORT="3306" # 数据库端口 BACKUP_DIR="/data/backup/mysql" # 备份存放目录 RETENTION_DAYS=7 # 保留天数 COMPRESS=true # 是否压缩 # ============================ # 创建备份目录 mkdir -p "$BACKUP_DIR" DATE=$(date '+%Y%m%d_%H%M%S') LOG_FILE="$BACKUP_DIR/backup_$(date '+%Y%m').log" echo "[$(date '+%Y-%m-%d %H:%M:%S')] 开始MySQL备份..." | tee -a "$LOG_FILE" # 获取所有数据库列表（排除系统库） DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -P"$DB_PORT" -e "SHOW DATABASES;" 2>/dev/null | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)") if [ -z "$DATABASES" ]; then echo "[$(date '+%Y-%m-%d %H:%M:%S')] 错误：未找到可备份的数据库，请检查配置" | tee -a "$LOG_FILE" exit 1 fi SUCCESS_COUNT=0 FAIL_COUNT=0 for DB in $DATABASES; do BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql" echo "[$(date '+%Y-%m-%d %H:%M:%S')] 正在备份数据库: $DB" | tee -a "$LOG_FILE" # 执行备份 if mysqldump -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -P"$DB_PORT" --single-transaction --routines --triggers --events "$DB" > "$BACKUP_FILE" 2>>"$LOG_FILE"; then if [ "$COMPRESS" = true ]; then gzip -f "$BACKUP_FILE" BACKUP_FILE="${BACKUP_FILE}.gz" fi FILE_SIZE=$(du -h "$BACKUP_FILE" | cut -f1) echo "[$(date '+%Y-%m-%d %H:%M:%S')] ✓ $DB 备份成功，大小: $FILE_SIZE" | tee -a "$LOG_FILE" SUCCESS_COUNT=$((SUCCESS_COUNT + 1)) else echo "[$(date '+%Y-%m-%d %H:%M:%S')] ✗ $DB 备份失败" | tee -a "$LOG_FILE" FAIL_COUNT=$((FAIL_COUNT + 1)) rm -f "$BACKUP_FILE" fi done # 清理过期备份 echo "[$(date '+%Y-%m-%d %H:%M:%S')] 清理${RETENTION_DAYS}天前的旧备份..." | tee -a "$LOG_FILE" find "$BACKUP_DIR" -name "*.sql" -o -name "*.sql.gz" | xargs -I {} find {} -mtime +$RETENTION_DAYS -delete 2>/dev/null echo "[$(date '+%Y-%m-%d %H:%M:%S')] 备份完成！成功: $SUCCESS_COUNT, 失败: $FAIL_COUNT" | tee -a "$LOG_FILE" echo "========================================" | tee -a "$LOG_FILE"

[{"name": "DB_USER", "label": "\u6570\u636e\u5e93\u7528\u6237\u540d", "default": "root"}, {"name": "DB_PASS", "label": "\u6570\u636e\u5e93\u5bc6\u7801", "default": ""}, {"name": "BACKUP_DIR", "label": "\u5907\u4efd\u76ee\u5f55", "default": "/data/backup/mysql"}, {"name": "RETENTION_DAYS", "label": "\u4fdd\u7559\u5929\u6570", "default": "7"}]