数据库备份脚本#

脚本说明#

数据库备份脚本用于备份MySQL、PostgreSQL、MongoDB等数据库，支持全量备份、增量备份、压缩等功能。

脚本代码#

#!/bin/bash

# 数据库备份脚本
# 功能：备份MySQL、PostgreSQL、MongoDB等数据库
# 作者：System Admin
# 日期：2026-01-01

set -euo pipefail

# 配置变量
BACKUP_DIR="/backup/database"
LOG_FILE="/var/log/db_backup.log"
RETENTION_DAYS=7
COMPRESS=true
ENCRYPT=false
ENCRYPTION_KEY=""
DB_TYPE="mysql"
DB_HOST="localhost"
DB_PORT=3306
DB_USER=""
DB_PASS=""
DB_NAME=""

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# 日志函数
log() {
    local level=$1
    shift
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $@" | tee -a "$LOG_FILE"
}

log_info() {
    log "INFO" "$@"
}

log_error() {
    log "ERROR" "$@"
}

# 创建备份目录
create_backup_dir() {
    if [ ! -d "$BACKUP_DIR" ]; then
        mkdir -p "$BACKUP_DIR"
        log_info "创建备份目录: $BACKUP_DIR"
    fi
}

# 备份MySQL数据库
backup_mysql() {
    local db_name=$1
    local backup_file=$2
    
    log_info "备份MySQL数据库: $db_name"
    
    # 检查mysqldump
    if ! command -v mysqldump &> /dev/null; then
        log_error "mysqldump未安装"
        return 1
    fi
    
    # 执行备份
    if [ "$COMPRESS" = true ]; then
        mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
            --single-transaction --quick --lock-tables=false \
            "$db_name" | gzip > "$backup_file.gz"
    else
        mysqldump -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
            --single-transaction --quick --lock-tables=false \
            "$db_name" > "$backup_file"
    fi
    
    # 加密备份
    if [ "$ENCRYPT" = true ] && [ -n "$ENCRYPTION_KEY" ]; then
        if [ "$COMPRESS" = true ]; then
            openssl enc -aes-256-cbc -salt -in "$backup_file.gz" -out "$backup_file.gz.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file.gz"
        else
            openssl enc -aes-256-cbc -salt -in "$backup_file" -out "$backup_file.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file"
        fi
    fi
    
    log_info "MySQL数据库备份完成: $backup_file"
}

# 备份PostgreSQL数据库
backup_postgresql() {
    local db_name=$1
    local backup_file=$2
    
    log_info "备份PostgreSQL数据库: $db_name"
    
    # 检查pg_dump
    if ! command -v pg_dump &> /dev/null; then
        log_error "pg_dump未安装"
        return 1
    fi
    
    # 执行备份
    if [ "$COMPRESS" = true ]; then
        pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" | gzip > "$backup_file.gz"
    else
        pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" > "$backup_file"
    fi
    
    # 加密备份
    if [ "$ENCRYPT" = true ] && [ -n "$ENCRYPTION_KEY" ]; then
        if [ "$COMPRESS" = true ]; then
            openssl enc -aes-256-cbc -salt -in "$backup_file.gz" -out "$backup_file.gz.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file.gz"
        else
            openssl enc -aes-256-cbc -salt -in "$backup_file" -out "$backup_file.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file"
        fi
    fi
    
    log_info "PostgreSQL数据库备份完成: $backup_file"
}

# 备份MongoDB数据库
backup_mongodb() {
    local db_name=$1
    local backup_file=$2
    
    log_info "备份MongoDB数据库: $db_name"
    
    # 检查mongodump
    if ! command -v mongodump &> /dev/null; then
        log_error "mongodump未安装"
        return 1
    fi
    
    # 创建临时目录
    local temp_dir=$(mktemp -d)
    
    # 执行备份
    mongodump -h "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
        -d "$db_name" -o "$temp_dir"
    
    # 打包备份
    if [ "$COMPRESS" = true ]; then
        tar -czf "$backup_file.gz" -C "$temp_dir" .
    else
        tar -cf "$backup_file" -C "$temp_dir" .
    fi
    
    # 清理临时目录
    rm -rf "$temp_dir"
    
    # 加密备份
    if [ "$ENCRYPT" = true ] && [ -n "$ENCRYPTION_KEY" ]; then
        if [ "$COMPRESS" = true ]; then
            openssl enc -aes-256-cbc -salt -in "$backup_file.gz" -out "$backup_file.gz.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file.gz"
        else
            openssl enc -aes-256-cbc -salt -in "$backup_file" -out "$backup_file.enc" -k "$ENCRYPTION_KEY"
            rm -f "$backup_file"
        fi
    fi
    
    log_info "MongoDB数据库备份完成: $backup_file"
}

# 备份所有数据库
backup_all_databases() {
    log_info "备份所有数据库"
    
    local timestamp=$(date +%Y%m%d_%H%M%S)
    
    case $DB_TYPE in
        mysql)
            # 获取所有数据库
            local databases=$(mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" \
                -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|mysql")
            
            for db in $databases; do
                local backup_file="$BACKUP_DIR/${DB_TYPE}_${db}_${timestamp}.sql"
                backup_mysql "$db" "$backup_file"
            done
            ;;
        postgresql)
            # 获取所有数据库
            local databases=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -l -t | grep -v "template\|postgres")
            
            for db in $databases; do
                local backup_file="$BACKUP_DIR/${DB_TYPE}_${db}_${timestamp}.sql"
                backup_postgresql "$db" "$backup_file"
            done
            ;;
        mongodb)
            # 获取所有数据库
            local databases=$(mongo --host "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
                --eval "db.getMongo().getDBNames()" --quiet | grep -v "admin\|local\|config")
            
            for db in $databases; do
                local backup_file="$BACKUP_DIR/${DB_TYPE}_${db}_${timestamp}.tar"
                backup_mongodb "$db" "$backup_file"
            done
            ;;
        *)
            log_error "不支持的数据库类型: $DB_TYPE"
            return 1
            ;;
    esac
}

# 备份指定数据库
backup_database() {
    local db_name=$1
    
    log_info "备份指定数据库: $db_name"
    
    local timestamp=$(date +%Y%m%d_%H%M%S)
    local backup_file="$BACKUP_DIR/${DB_TYPE}_${db_name}_${timestamp}.sql"
    
    case $DB_TYPE in
        mysql)
            backup_mysql "$db_name" "$backup_file"
            ;;
        postgresql)
            backup_postgresql "$db_name" "$backup_file"
            ;;
        mongodb)
            backup_file="$BACKUP_DIR/${DB_TYPE}_${db_name}_${timestamp}.tar"
            backup_mongodb "$db_name" "$backup_file"
            ;;
        *)
            log_error "不支持的数据库类型: $DB_TYPE"
            return 1
            ;;
    esac
}

# 清理旧备份
cleanup_old_backups() {
    log_info "清理 $RETENTION_DAYS 天前的备份"
    
    find "$BACKUP_DIR" -type f -mtime +$RETENTION_DAYS -delete
    
    log_info "旧备份清理完成"
}

# 列出备份
list_backups() {
    log_info "列出备份文件"
    
    echo "备份文件列表"
    echo "=========="
    
    ls -lh "$BACKUP_DIR" | grep -E "\.(sql|tar)(\.gz)?(\.enc)?$"
}

# 恢复数据库
restore_database() {
    local backup_file=$1
    local db_name=$2
    
    log_info "恢复数据库: $db_name"
    
    if [ ! -f "$backup_file" ]; then
        log_error "备份文件不存在: $backup_file"
        return 1
    fi
    
    # 解密备份
    if [[ "$backup_file" == *.enc ]]; then
        local decrypted_file="${backup_file%.enc}"
        openssl enc -d -aes-256-cbc -in "$backup_file" -out "$decrypted_file" -k "$ENCRYPTION_KEY"
        backup_file="$decrypted_file"
    fi
    
    # 解压备份
    if [[ "$backup_file" == *.gz ]]; then
        local decompressed_file="${backup_file%.gz}"
        gunzip -c "$backup_file" > "$decompressed_file"
        backup_file="$decompressed_file"
    fi
    
    # 恢复数据库
    case $DB_TYPE in
        mysql)
            mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" -p"$DB_PASS" "$db_name" < "$backup_file"
            ;;
        postgresql)
            psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db_name" < "$backup_file"
            ;;
        mongodb)
            local temp_dir=$(mktemp -d)
            tar -xf "$backup_file" -C "$temp_dir"
            mongorestore -h "$DB_HOST" --port "$DB_PORT" -u "$DB_USER" -p "$DB_PASS" \
                -d "$db_name" "$temp_dir/$db_name"
            rm -rf "$temp_dir"
            ;;
        *)
            log_error "不支持的数据库类型: $DB_TYPE"
            return 1
            ;;
    esac
    
    log_info "数据库恢复完成: $db_name"
}

# 显示帮助
show_help() {
    echo "用法: $0 [选项] <命令> [参数]"
    echo ""
    echo "选项:"
    echo "  -t <类型>        数据库类型（mysql/postgresql/mongodb）"
    echo "  -h <主机>        数据库主机（默认: localhost）"
    echo "  -p <端口>        数据库端口（默认: 3306）"
    echo "  -u <用户>        数据库用户"
    echo "  -P <密码>        数据库密码"
    echo "  -d <目录>        备份目录（默认: /backup/database）"
    echo "  -r <天数>        保留天数（默认: 7）"
    echo "  -c               压缩备份"
    echo "  -e <密钥>        加密备份"
    echo "  -h               显示帮助信息"
    echo ""
    echo "命令:"
    echo "  all                       备份所有数据库"
    echo "  backup <数据库名>         备份指定数据库"
    echo "  restore <备份文件> <数据库名>  恢复数据库"
    echo "  list                      列出备份文件"
    echo "  cleanup                   清理旧备份"
    echo ""
    echo "示例:"
    echo "  $0 -t mysql -u root -P password backup mydb"
    echo "  $0 -t postgresql -u postgres restore backup.sql mydb"
    echo "  $0 -t mongodb -u admin -P password all"
}

# 主函数
main() {
    # 解析选项
    while getopts "t:h:p:u:P:d:r:ce:H" opt; do
        case $opt in
            t)
                DB_TYPE="$OPTARG"
                log_info "数据库类型: $DB_TYPE"
                ;;
            h)
                DB_HOST="$OPTARG"
                log_info "数据库主机: $DB_HOST"
                ;;
            p)
                DB_PORT="$OPTARG"
                log_info "数据库端口: $DB_PORT"
                ;;
            u)
                DB_USER="$OPTARG"
                log_info "数据库用户: $DB_USER"
                ;;
            P)
                DB_PASS="$OPTARG"
                log_info "数据库密码: ****"
                ;;
            d)
                BACKUP_DIR="$OPTARG"
                log_info "备份目录: $BACKUP_DIR"
                ;;
            r)
                RETENTION_DAYS="$OPTARG"
                log_info "保留天数: $RETENTION_DAYS"
                ;;
            c)
                COMPRESS=true
                log_info "压缩备份"
                ;;
            e)
                ENCRYPT=true
                ENCRYPTION_KEY="$OPTARG"
                log_info "加密备份"
                ;;
            H)
                show_help
                exit 0
                ;;
            *)
                log_error "无效选项: $opt"
                show_help
                exit 1
                ;;
        esac
    done
    
    shift $((OPTIND - 1))
    
    # 创建备份目录
    create_backup_dir
    
    # 检查命令
    if [ $# -eq 0 ]; then
        log_error "缺少命令"
        show_help
        exit 1
    fi
    
    COMMAND=$1
    shift
    
    # 执行命令
    case $COMMAND in
        all)
            backup_all_databases
            cleanup_old_backups
            ;;
        backup)
            if [ $# -eq 0 ]; then
                log_error "缺少数据库名"
                show_help
                exit 1
            fi
            backup_database "$1"
            ;;
        restore)
            if [ $# -lt 2 ]; then
                log_error "缺少参数"
                show_help
                exit 1
            fi
            restore_database "$1" "$2"
            ;;
        list)
            list_backups
            ;;
        cleanup)
            cleanup_old_backups
            ;;
        *)
            log_error "无效的命令: $COMMAND"
            show_help
            exit 1
            ;;
    esac
    
    log_info "数据库备份操作完成"
}

# 执行主函数
main "$@"

使用说明#

添加执行权限：
```
chmod +x db_backup.sh
```

基本用法：

# 备份所有MySQL数据库
./db_backup.sh -t mysql -u root -P password all

# 备份指定数据库
./db_backup.sh -t mysql -u root -P password backup mydb

# 恢复数据库
./db_backup.sh -t mysql -u root -P password restore backup.sql mydb

# 列出备份文件
./db_backup.sh list

高级用法：

# 压缩备份
./db_backup.sh -t mysql -u root -P password -c backup mydb

# 加密备份
./db_backup.sh -t mysql -u root -P password -e "encryption_key" backup mydb

# 备份PostgreSQL数据库
./db_backup.sh -t postgresql -u postgres backup mydb

# 备份MongoDB数据库
./db_backup.sh -t mongodb -u admin -P password all

# 指定保留天数
./db_backup.sh -r 30 cleanup

功能特点#

多数据库支持（MySQL、PostgreSQL、MongoDB）
全量备份
压缩备份
加密备份
自动清理旧备份
数据库恢复
备份列表管理

依赖项#

mysqldump: MySQL备份工具
pg_dump: PostgreSQL备份工具
mongodump: MongoDB备份工具
openssl: 加密工具
gzip: 压缩工具

注意事项#

需要数据库用户有足够权限
加密密钥需要妥善保管
备份文件需要有足够空间
恢复前建议先备份当前数据
大数据库备份可能需要较长时间