自动备份数据库的脚本代码怎么写

wen 实用脚本 2026-07-12 2

本文目录导读：

自动备份数据库的脚本代码怎么写

为什么需要自动备份数据库脚本？
自动备份脚本的核心设计原则
通用备份脚本架构解析
实战：Shell脚本自动备份MySQL/MariaDB
实战：Python脚本自动备份PostgreSQL
实战：Windows批处理备份SQL Server
自动化调度配置
备份脚本的常见问题与问答（Q&A）
企业级备份建议与延伸

自动备份数据库的脚本代码怎么写？超详细实战指南（附Shell/Python/Windows批处理示例）

目录导读

为什么需要自动备份数据库脚本？
自动备份脚本的核心设计原则
通用备份脚本架构解析
实战：Shell脚本自动备份MySQL/MariaDB
实战：Python脚本自动备份PostgreSQL
实战：Windows批处理备份SQL Server
自动化调度配置（cron、任务计划程序）
备份脚本的常见问题与问答（Q&A）
企业级备份建议与延伸

为什么需要自动备份数据库脚本？

数据库是任何系统的核心资产,自动备份脚本能确保：

数据零丢失：避免手动操作遗漏
定时稳定性：严格按计划执行备份任务
空间管理：自动清理旧备份文件
恢复验证：脚本可附带测试恢复逻辑

自动备份脚本的核心设计原则

优秀的备份脚本应满足5个关键要素：

原则	说明
原子性	备份失败时不影响生产库
隔离性	使用独立用户与权限
持久性	写入磁盘后再进行下一个操作
可追溯	日志记录每次备份状态
自动化清理	超过保留天数的自动删除

通用备份脚本架构解析

#!/bin/bash
# 通用备份脚本框架
BACKUP_DIR="/data/backup/db"
DB_USER="backup_user"
DB_PASS="YourSecurePass123!"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="$BACKUP_DIR/backup_$DATE.log"
RETENTION_DAYS=30
# 步骤1：创建备份目录
mkdir -p $BACKUP_DIR
# 步骤2：执行数据库备份命令
# 步骤3：校验备份文件完整性
# 步骤4：压缩备份文件
# 步骤5：删除过期备份
# 步骤6：记录日志

实战：Shell脚本自动备份MySQL/MariaDB

代码示例（已兼容最新MySQL 8.0+）

#!/bin/bash
# mysql_auto_backup.sh
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASS="YourStrongPassword"
BACKUP_DIR="/data/mysql_backups"
DATE=$(date +%F_%H%M)
LOG_FILE="$BACKUP_DIR/logs/backup_$DATE.log"
MAX_BACKUPS=10
# 创建日志目录
mkdir -p "$BACKUP_DIR/logs"
# 获取所有数据库列表
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|sys)")
# 循环备份每个库
for DB in $DATABASES; do
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
    echo "$(date) 开始备份 $DB" >> $LOG_FILE
    # 使用mysqldump生成压缩备份
    mysqldump -h $DB_HOST -P $DB_PORT -u $DB_USER -p$DB_PASS \
        --single-transaction --routines --triggers --events \
        --quick $DB | gzip > $BACKUP_FILE
    # 校验备份文件
    if [ $? -eq 0 ] && [ -s $BACKUP_FILE ]; then
        echo "$(date) $DB 备份成功，文件大小：$(du -h $BACKUP_FILE | cut -f1)" >> $LOG_FILE
    else
        echo "$(date) 错误：$DB 备份失败" >> $LOG_FILE
        # 可增加邮件告警逻辑
    fi
done
# 清理过期备份（保留最近MAX_BACKUPS份）
ls -t $BACKUP_DIR/*.sql.gz | tail -n +$((MAX_BACKUPS+1)) | xargs -r rm -f
echo "$(date) 备份任务完成，已清理超过 $MAX_BACKUPS 份的旧备份" >> $LOG_FILE

关键优化点：

使用--single-transaction避免锁表
排除系统表（MySQL 8.0默认sys库）
自动清理旧备份防止磁盘占满

实战：Python脚本自动备份PostgreSQL

#!/usr/bin/env python3
# pg_backup.py
import os
import subprocess
import datetime
import shutil
DB_CONFIG = {
    'host': 'localhost',
    'port': '5432',
    'user': 'backup_user',
    'password': 'Secret2024!',
    'database': 'mydb'
}
BACKUP_DIR = '/data/pg_backups'
RETENTION_DAYS = 14
def backup_pg():
    # 生成带时间戳的文件名
    timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_file = f"{BACKUP_DIR}/mydb_{timestamp}.dump"
    # 构建pg_dump命令
    cmd = [
        'pg_dump',
        '-h', DB_CONFIG['host'],
        '-p', DB_CONFIG['port'],
        '-U', DB_CONFIG['user'],
        '-F', 'c',  # 自定义格式，支持压缩
        '-b',       # 包含大对象
        '-v',       # 详细输出
        '-f', backup_file,
        DB_CONFIG['database']
    ]
    # 设置环境变量密码
    env = os.environ.copy()
    env['PGPASSWORD'] = DB_CONFIG['password']
    try:
        result = subprocess.run(cmd, env=env, capture_output=True, check=True)
        print(f"备份成功：{backup_file}")
        # 验证文件完整性
        verify_cmd = ['pg_restore', '-l', backup_file]
        verify = subprocess.run(verify_cmd, capture_output=True)
        if verify.returncode == 0:
            print("备份文件验证通过")
    except subprocess.CalledProcessError as e:
        print(f"备份失败：{e.stderr.decode()}")
        raise
def clean_old_backups():
    """删除超过保留天数的备份"""
    now = datetime.datetime.now()
    for f in os.listdir(BACKUP_DIR):
        fpath = os.path.join(BACKUP_DIR, f)
        if f.endswith('.dump'):
            mtime = datetime.datetime.fromtimestamp(os.path.getmtime(fpath))
            if (now - mtime).days > RETENTION_DAYS:
                os.remove(fpath)
                print(f"已清理过期备份：{f}")
if __name__ == '__main__':
    os.makedirs(BACKUP_DIR, exist_ok=True)
    backup_pg()
    clean_old_backups()

为什么选Python？

更强的异常处理
支持跨平台运行
易于集成邮件通知

实战：Windows批处理备份SQL Server

@echo off
REM sqlserver_backup.bat
set DB_HOST=localhost
set DB_NAME=MyDatabase
set BACKUP_DIR=C:\Backups\SQL
set DATE=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%
set LOG_FILE=%BACKUP_DIR%\backup_%DATE%.log
REM 创建备份目录
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
REM 执行SQLCMD备份（需安装SQL Server Command Line Tools）
sqlcmd -S %DB_HOST% -d %DB_NAME% -E -Q "BACKUP DATABASE [%DB_NAME%] TO DISK='%BACKUP_DIR%\%DB_NAME%_%DATE%.bak' WITH COMPRESSION, STATS=10" >> %LOG_FILE% 2>&1
REM 删除7天前的备份
forfiles -p "%BACKUP_DIR%" -m *.bak -d -7 -c "cmd /c del @file" >> %LOG_FILE% 2>&1
echo 备份完成，日志路径：%LOG_FILE%

特别提示：

-E表示使用Windows身份验证
生产环境建议将COMPRESSION改为MAXTRANSFERSIZE=4194304优化性能

自动化调度配置

Linux (cron)

# 每天凌晨2点执行备份
0 2 * * * /bin/bash /scripts/mysql_auto_backup.sh
# 查看当前任务
crontab -l

Windows (任务计划程序)

打开“任务计划程序” → 创建任务
触发器：每天02:00
操作：启动程序 → 选择sqlserver_backup.bat
勾选“不管用户是否登录都要运行”

备份脚本的常见问题与问答（Q&A）

Q1：备份脚本执行时出现“Access denied”错误怎么办？
A：确保数据库用户有正确的权限，MySQL需授予SELECT, LOCK TABLES, SHOW VIEW, TRIGGER；PostgreSQL需CONNECT, SELECT, USAGE ON SCHEMA，建议创建专门备份用户：

-- MySQL
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON *.* TO 'backup_user'@'localhost';

Q2：备份文件越来越大，如何限制空间？
A：在脚本中加入保留策略，推荐使用“滚动备份”：保留最近7天每日备份，加上每月完整备份，也可采用增量备份减少存储。

Q3：备份过程中数据库有写入操作会损坏数据吗？
A：使用mysqldump --single-transaction或pg_dump默认使用快照隔离，不会阻塞写入，SQL Server使用WITH CHECKSUM验证完整性。

Q4：如何验证备份文件是否可恢复？
A：在脚本末尾增加测试恢复到临时数据库的逻辑，

# 在安全环境恢复测试
mysql -u test_user test_restore_db < backup.sql 2>/dev/null && echo "恢复测试通过"

Q5：脚本中密码硬编码不安全，怎么办？
A：使用环境变量或加密文件：

export MYSQL_PWD=$(cat /etc/mysql_backup_pass)
mysqldump -u backup_user mydb > backup.sql

企业级备份建议与延伸

备份频率：核心业务建议每小时增量备份 + 每日全量
异地存储：脚本可增加SCP或AWS S3上传步骤
监控告警：集成Prometheus或邮件通知（示例：mail -s "备份失败" admin@example.com）
文档化：每个脚本头部写明版本、作者、修改记录

最后提醒：定期在测试环境演练恢复流程，确保备份文件真正可用。

文章总结：本文从实战角度出发，提供了Shell、Python、Windows批处理三种主流数据库的自动备份脚本方案，覆盖MySQL、PostgreSQL、SQL Server，并详细介绍了自动化调度与常见问题，建议根据实际环境选择对应脚本，并严格测试后再投入生产。