Ubuntu MySQL 自动备份到阿里云 OSS

-- 创建用户（根据实际情况修改密码）
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'YourSecurePassword';

-- 授予所有数据库的备份权限
GRANT ALL PRIVILEGES ON *.* TO 'backup'@'localhost' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户是否创建成功
SELECT user, host FROM mysql.user WHERE user='backup';

-- 退出
EXIT;

#!/bin/bash

cd /tmp

# ============ 配置区（根据实际情况修改） ============
BUCKET="oss://your-bucket-name"
OSS_PATH="your-backup-folder"
BACKUP_DIR="/backup-data"
DATE=$(date +%Y%m%d_%H%M%S)

#阿里云oss保留文件个数
KEEP_COUNT=10

#本地保留备份数量
LOCAL_KEEP_COUNT=1


DB_HOST="127.0.0.1"
DB_USER="backup"
DB_PASS='YourSecurePassword'
DB_LIST=("database1" "database2")

# ============ 参数解析 ============
show_help() {
    echo "用法: $0 <参数>"
    echo ""
    echo "必须指定以下参数之一:"
    echo "  --all            MySQL备份 + 上传阿里云"
    echo "  --backup-only    只执行 MySQL 备份"
    echo "  --upload-only    只上传到阿里云"
    echo "  --help           显示帮助"
    exit 1
}

if [ $# -eq 0 ]; then
    echo "错误：必须指定参数！"
    show_help
fi

DO_BACKUP=false
DO_UPLOAD=false

case $1 in
    --all)
        DO_BACKUP=true
        DO_UPLOAD=true
        ;;
    --backup-only)
        DO_BACKUP=true
        ;;
    --upload-only)
        DO_UPLOAD=true
        ;;
    --help)
        show_help
        ;;
    *)
        echo "未知参数: $1"
        show_help
        ;;
esac

# ============ 清理本地旧备份 ============
clean_local_backup() {
    echo "$(date): 清理本地旧备份，保留最近 ${LOCAL_KEEP_COUNT} 份..."
    for DB_NAME in "${DB_LIST[@]}"; do
        ls -t "$BACKUP_DIR"/${DB_NAME}_*.sql.gz 2>/dev/null | tail -n +$((LOCAL_KEEP_COUNT + 1)) | xargs -r rm -f
    done
    ls -t "$BACKUP_DIR"/configs_*.tar.gz 2>/dev/null | tail -n +$((LOCAL_KEEP_COUNT + 1)) | xargs -r rm -f
}

# ============ 执行备份 ============
if [ "$DO_BACKUP" = true ]; then
    mkdir -p "$BACKUP_DIR"

    echo "$(date): 开始备份数据库..."
    for DB_NAME in "${DB_LIST[@]}"; do
        echo "$(date): 正在备份: $DB_NAME"
        mysqldump -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" --single-transaction "$DB_NAME" 2>/dev/null | gzip > "$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz"

        if [ ${PIPESTATUS[0]} -eq 0 ] && [ -s "$BACKUP_DIR/${DB_NAME}_$DATE.sql.gz" ]; then
            echo "$(date): $DB_NAME 备份成功"
        else
            echo "$(date): $DB_NAME 备份失败！"
        fi
    done

    echo "$(date): 备份配置文件..."
    tar czf "$BACKUP_DIR/configs_$DATE.tar.gz" /etc/nginx /opt/app/application.yml 2>/dev/null

    clean_local_backup
fi

# ============ 执行上传 ============
if [ "$DO_UPLOAD" = true ]; then
    FILE_COUNT=$(find "$BACKUP_DIR" -type f 2>/dev/null | wc -l)
    if [ "$FILE_COUNT" -eq 0 ]; then
        echo "$(date): 错误：备份目录为空！"
        exit 1
    fi

    echo "$(date): 上传到阿里云 OSS..."
    find "$BACKUP_DIR" -type f | while read file; do
        FILENAME=$(basename "$file")
        echo "上传: $FILENAME"
        ossutil cp "$file" "$BUCKET/$OSS_PATH/backup_$DATE/$FILENAME"
    done

    echo "$(date): 清理 OSS 旧备份..."
    ossutil ls "$BUCKET/$OSS_PATH/" -d 2>/dev/null | grep "backup_" | awk '{print $NF}' | sort -r | tail -n +$((KEEP_COUNT + 1)) | while read dir; do
        echo "删除: $dir"
        ossutil rm -r "$dir" -f
    done
fi

echo "$(date): 完成！"