数据库备份与恢复脚本

1
#!/bin/bash
2

3
# 配置：数据库参数（可按需修改）
4
DB_TYPE="mysql"  # mysql 或 postgresql
5
DB_HOST="localhost"
6
DB_PORT="3306"   # 或 PostgreSQL 默认 5432
7
DB_USER="root"
8
DB_PASS="password"
9
DB_NAME="myapp"
10
BACKUP_DIR="$HOME/backups"
11
DATE=$(date +%Y%m%d_%H%M%S)
12

13
# 创建备份目录
14
mkdir -p "$BACKUP_DIR"
15

16
# 根据数据库类型执行备份
17
if [ "$DB_TYPE" = "mysql" ]; then
18
    # MySQL 备份
19
    mysqldump -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql"
20
elif [ "$DB_TYPE" = "postgresql" ]; then
21
    # PostgreSQL 备份
22
    PGPASSWORD="$DB_PASS" pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql"
23
fi
24

25
# 检查备份是否成功
26
if [ $? -eq 0 ]; then
27
    echo "数据库备份成功：${DB_NAME}_${DATE}.sql"
28

29
    # 压缩备份文件
30
    gzip "$BACKUP_DIR/${DB_NAME}_${DATE}.sql"
31

32
    # 可选：删除 7 天前的备份
33
    find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete
34
else
35
    echo "数据库备份失败！"
36
    exit 1
37
fi

1
#!/bin/bash
2

3
# 配置：数据库参数（可按需修改）
4
DB_TYPE="mysql"  # mysql 或 postgresql
5
DB_HOST="localhost"
6
DB_PORT="3306"   # 或 PostgreSQL 默认 5432
7
DB_USER="root"
8
DB_PASS="password"
9
DB_NAME="myapp"
10
BACKUP_DIR="$HOME/backups"
11
BACKUP_FILE="$1"  # 从命令行传入备份文件名
12

13
# 检查是否提供了备份文件
14
if [ -z "$BACKUP_FILE" ]; then
15
    echo "用法: $0 < 备份文件路径 >"
16
    echo "可用的备份文件:"
17
    ls -la "$BACKUP_DIR"/*.sql.gz
18
    exit 1
19
fi
20

21
# 确认操作
22
echo "即将恢复数据库 $DB_NAME 从备份文件 $BACKUP_FILE"
23
read -p "确认操作? [y/N] " -n 1 -r
24
echo
25
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
26
    echo "恢复操作已取消"
27
    exit 1
28
fi
29

30
# 检查备份文件是否存在
31
if [ ! -f "$BACKUP_FILE" ]; then
32
    echo "备份文件不存在：$BACKUP_FILE"
33
    exit 1
34
fi
35

36
# 解压缩备份文件（如果需要）
37
if [[ "$BACKUP_FILE" == *.gz ]]; then
38
    gunzip -c "$BACKUP_FILE" > "$BACKUP_FILE.tmp"
39
    BACKUP_FILE="$BACKUP_FILE.tmp"
40
fi
41

42
# 根据数据库类型执行恢复
43
if [ "$DB_TYPE" = "mysql" ]; then
44
    # MySQL 恢复
45
    mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS "$DB_NAME" < "$BACKUP_FILE"
46
elif [ "$DB_TYPE" = "postgresql" ]; then
47
    # PostgreSQL 恢复
48
    PGPASSWORD="$DB_PASS" psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME < "$BACKUP_FILE"
49
fi
50

51
# 检查恢复是否成功
52
if [ $? -eq 0 ]; then
53
    echo "数据库恢复成功！"
54

55
    # 清理临时文件
56
    if [ -f "$BACKUP_FILE.tmp" ]; then
57
        rm "$BACKUP_FILE.tmp"
58
    fi
59
else
60
    echo "数据库恢复失败！"
61
    exit 1
62
fi