IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

怎样做数据库备份脚本

wen 实用脚本 24

从零构建自动化安全防线

📚 目录导读

  1. 为什么需要数据库备份脚本？
  2. 备份脚本的核心原理：逻辑备份与物理备份的区别
  3. 实战：编写MySQL备份脚本（含自动压缩与删除历史）
  4. 实战：编写PostgreSQL备份脚本
  5. 实战：编写SQL Server备份脚本
  6. 高级技巧：备份脚本的加密、异地传输与监控警报
  7. 常见问题问答（FAQ）
  8. 最佳实践建议

引言：为什么需要数据库备份脚本？

想象一下：你的电商网站数据库突然因硬件故障崩溃，而最后一次全量备份是7天前，这意味着7天的订单、用户数据全部丢失——这不仅是数据灾难，更是业务灾难，据统计，90%以上的企业数据丢失事件都与备份策略不当有关。

怎样做数据库备份脚本

手动备份虽然简单（例如使用mysqldump执行一次命令），但存在 四大致命缺陷：

  • 容易遗忘,无法保证频率
  • 无法自动处理过期备份的清理
  • 缺乏错误通知机制
  • 跨服务器操作时难以统一管理

编写数据库备份脚本是将备份工作“自动化、标准化、可监控化”的核心手段，它是每一位DBA和运维人员必须掌握的技能。

备份脚本的核心原理：逻辑备份 vs 物理备份

在编写脚本前,必须明确两种备份类型：

类型 逻辑备份 物理备份
原理 导出SQL语句（INSERT、CREATE TABLE） 直接复制数据库文件（.ibd、.frm、.mdf等）
工具示例 mysqldump, pg_dump, SQL Server Management Studio Percona XtraBackup, pg_basebackup, VSS快照
优点 跨版本兼容、可编辑、文件小 恢复速度快、无需重建索引
缺点 恢复慢，大数据量时耗时 依赖存储引擎、文件锁策略

编写脚本的建议：对于数据量小于50GB的小型数据库，逻辑备份脚本够用；大型生产库推荐物理备份脚本（如使用XtraBackup）。

实战：编写MySQL备份脚本（含自动压缩与删除历史）

以下是一个完整、可直接使用的MySQL备份脚本（Linux/Unix环境）：

#!/bin/bash
# ==========================================
# MySQL Database Backup Script
# Version: 2.0
# 功能：全量备份、gzip压缩、保留最近30天备份
# ==========================================
# 配置参数
DB_USER="backup_user"
DB_PASS="your_secure_password"
DB_NAME="your_database"
BACKUP_DIR="/data/backup/mysql"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
# 创建备份目录
mkdir -p "${BACKUP_DIR}"
# 执行备份并压缩
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Starting backup of ${DB_NAME}..."
mysqldump --single-transaction --routines --triggers --events \
          -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" \
          | gzip > "${BACKUP_FILE}"
# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "Backup successful: ${BACKUP_FILE}"
    echo "File size: $(du -h ${BACKUP_FILE} | cut -f1)"
else
    echo "ERROR: Backup failed!" >&2
    exit 1
fi
# 删除超过保留天数的旧备份
find "${BACKUP_DIR}" -name "*.sql.gz" -type f -mtime +${RETENTION_DAYS} -delete
echo "Old backups older than ${RETENTION_DAYS} days removed."
# 记录备份日志
echo "${DATE} | ${DB_NAME} | ${BACKUP_FILE} | $(du -h ${BACKUP_FILE} | cut -f1)" >> "${BACKUP_DIR}/backup_history.log"

📌 脚本关键点解析：

  • --single-transaction：使用事务保证一致性，不锁定表
  • --routines --triggers --events：完整导出存储过程、触发器、事件
  • gzip压缩：通常可节省70%-80%空间
  • find... -delete：自动清理过期备份，避免磁盘爆满

实战：编写PostgreSQL备份脚本

PostgreSQL有专属的pg_dump和pg_dumpall工具，以下是一个自动压缩并保留7天备份的脚本：

#!/bin/bash
# PostgreSQL Backup Script
PG_HOST="localhost"
PG_USER="postgres"
BACKUP_DIR="/data/backup/pgsql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/postgres_full_${DATE}.dump"
# 使用自定义格式（可压缩+并行）
pg_dump --host="${PG_HOST}" --username="${PG_USER}" \
        --format=custom --compress=9 --file="${BACKUP_FILE}"
# 检查备份文件完整性
pg_restore --list "${BACKUP_FILE}" > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "Backup verified: ${BACKUP_FILE}"
else
    echo "ERROR: Backup file corrupted!" | mail -s "Backup Alert" admin@example.com
    exit 1
fi
# 清理过期备份（保留7天）
find "${BACKUP_DIR}" -name "*.dump" -mtime +7 -delete

这里使用--format=custom，这是PostgreSQL推荐的备份格式，支持压缩、并行恢复和选择性恢复。

实战：编写SQL Server备份脚本

Windows环境下可以使用PowerShell或SQL代理作业,以下是一个PowerShell备份脚本：

# SQL Server Backup Script (PowerShell)
$serverInstance = "localhost\SQLEXPRESS"
$databaseName = "YourDatabase"
$backupPath = "D:\SQLBackups\"
$date = Get-Date -Format "yyyyMMdd_HHmmss"
$backupFile = $backupPath + $databaseName + "_" + $date + ".bak"
# 使用SMO对象执行备份
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
$backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
$backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$backup.Database = $databaseName
$backup.Devices.AddDevice($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$backup.SqlBackup($server)
# 删除7天前的备份
Get-ChildItem $backupPath -Filter "*.bak" | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } | Remove-Item

该脚本直接调用SQL Server管理对象（SMO），不需要安装额外工具。

高级技巧：加密、异地传输与监控警报

🔐 备份加密

敏感数据备份必须加密,推荐使用 openssl：

# 在原有压缩文件基础上加密
openssl enc -aes-256-cbc -salt -in backup.sql.gz -out backup.sql.gz.enc -pass pass:yourSecretKey

📡 异地备份（scp/s3）

将本地备份自动同步到远程服务器或对象存储（如阿里云OSS、AWS S3）：

# 使用sftp/scp
scp ${BACKUP_FILE} user@remote-backup:/backup/mysql/
# 或使用aws cli上传到S3
aws s3 cp ${BACKUP_FILE} s3://my-bucket/backups/

🔔 失败告警

在脚本中添加通知机制：

if [ $? -ne 0 ]; then
    curl -s -X POST "https://api.telegram.org/bot<TOKEN>/sendMessage" \
         -d chat_id=<YOUR_CHAT_ID> -d text="❌ MySQL备份失败：$DB_NAME at $(date)"
fi

常见问题问答（FAQ）

Q1：备份脚本应该多久运行一次？
A：关键业务数据库建议每天全量备份+每6小时增量备份（需要额外脚本实现binlog解析），如果数据量小，每天一次全量即可。

Q2：备份时锁表了怎么办？
A：MySQL加--single-transaction（InnoDB）或--lock-tables=false；PostgreSQL的pg_dump默认使用MVCC，不锁表；SQL Server使用VSS快照。

Q3：备份文件越来越大怎么办？
A：一方面增加保留天数内的压缩；另一方面考虑增量备份策略，或使用物理备份工具（如XtraBackup）只备份变化的数据块。

Q4：如何验证备份文件是可恢复的？
A：在脚本中加入验证步骤（如MySQL用mysql --force < backup.sql测试部分数据，或PostgreSQL用pg_restore --list），强烈建议定期做恢复演练。

Q5：误删了生产数据，用备份恢复需要多久？
A：逻辑备份恢复时间通常为备份时间的1.5-3倍，50GB的数据库可能需要2-4小时，因此建议保留最近的全量备份+后续的binlog/归档日志，实现最小数据丢失。

最佳实践建议

  1. 遵循3-2-1规则：3份数据副本，2种不同存储介质，1份异地存储。
  2. 脚本统一管理：使用Git管理备份脚本版本，配合Ansible或SaltStack分发到所有数据库服务器。
  3. 添加时间戳和日志：每条备份记录必须包含开始时间、结束时间、文件大小、校验和。
  4. 压力测试恢复流程：每个季度至少进行一次完整的备份文件异地恢复模拟。
  5. 监控磁盘空间：在脚本开始前检查备份目录剩余空间，不足则提前告警。

最好的备份脚本不是写得最漂亮的,而是能稳定运行、及时告警、可快速恢复的，备份不是备份动作本身，而是你能够成功恢复数据的能力，动手编写你的第一个自动化备份脚本吧！

上一篇脚本如何恢复数据库数据

下一篇脚本如何全量备份数据

相关文章

抱歉，评论功能暂时关闭!