如何用脚本生成数据库备份

wen 实用脚本 2026-08-01 2

**
《自动化运维必修课：如何用脚本生成数据库备份，彻底告别手动苦力》

如何用脚本生成数据库备份

目录导读

为什么你需要脚本化备份？——从“人肉定时器”到“无人值守”
前置准备：备份脚本的“地基”三件套（权限、路径、压缩策略）
核心实战：Bash + MySQL 备份脚本逐行拆解（含PostgreSQL变体）
进阶技巧：远程备份、增量备份与加密上传（OSS/S3）
定时调度：Crontab 与 Systemd Timer 的黄金搭配
常见故障问答：锁表怎么办？备份文件损坏如何预警？
从脚本到体系,你的备份“保险丝”够粗吗？

为什么你需要脚本化备份？——从“人肉定时器”到“无人值守”
很多初创团队或传统企业，至今还在用“Navicat导出SQL”或者“phpMyAdmin下载”这种半手动方式备份数据库，一旦遇到凌晨2点的数据库误删，或者硬盘突发坏道，你只能对着昨天下午的备份文件欲哭无泪，脚本化备份的核心理念是：让机器在固定时间点，用预设规则，自动完成“导出-压缩-校验-异地存放”全链路，它不依赖任何图形界面，不占用你的下班时间，甚至能在备份完成后主动推送结果到钉钉/企业微信，根据DBA的统计，采用脚本化备份后，数据恢复点目标（RPO）可以从24小时缩短到5分钟以内（如果是binlog增量）。

前置准备：备份脚本的“地基”三件套
在写第一行命令之前，必须确认三件事，否则脚本跑起来会“埋雷”：

权限最小化：不要在脚本里明文写root密码，建议使用/etc/my.cnf中的[client]段配置独立备份账号（如backup_user），并仅授权SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER权限，PostgreSQL则使用.pgpass文件。
目录与保留周期：建议创建/data/backup/mysql/{daily,weekly,monthly}三层目录，脚本内用$(date +%F_%H%M)动态生成文件名，并配合find命令清理超过7天的旧文件，防止磁盘被备份撑爆。
压缩策略：MySQL单库超过5GB时，若不压缩，备份文件会占满临时空间，脚本中务必使用gzip -9或zstd（压缩率更高，速度更快），注意，压缩流会消耗CPU，建议在业务低峰期执行。

核心实战：Bash + MySQL 备份脚本逐行拆解
以下是一个生产环境验证过的精简版脚本（假设数据库名为mydb）：

#!/bin/bash
# 变量定义
DB_USER="backup_user"
DB_NAME="mydb"
BACKUP_DIR="/data/backup/mysql/daily"
DATE=$(date +%Y%m%d_%H%M)
MYSQL_BIN="$(which mysqldump)"
# 关键参数解析
# --single-transaction：InnoDB表开启事务一致性快照，避免锁表
# --routines --triggers：备份存储过程和触发器
# --hex-blob：防止二进制字段转义出错
$MYSQL_BIN -u"$DB_USER" --single-transaction --routines --triggers --hex-blob "$DB_NAME" \
  | gzip -9 > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
# 校验文件是否生成成功（字节数大于0）
if [ -s "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz" ]; then
  echo "备份成功: $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz" >> /var/log/db_backup.log
  # 可选：删除7天前的旧备份
  find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
else
  echo "备份失败，文件大小为0！" | mail -s "DB Backup ERROR" admin@example.com
  exit 1
fi

PostgreSQL变体：将mysqldump换成pg_dump -Fc（自定义压缩格式），后缀改为.dump，恢复时用pg_restore，注意PostgreSQL切记不要用--single-transaction，它不支持，直接使用默认并发导出即可。

进阶技巧：远程备份、增量备份与加密上传

远程异地备份：脚本末尾追加scp或rsync上传到192.168.1.100:/mnt/backup，若走公网，务必用rsync -avz --partial并配置SSH密钥免密登录。
增量备份：全量脚本只能恢复至昨天，要恢复至“1小时前”，必须借助mysqlbinlog，脚本中在备份完成后，额外执行mysqlbinlog --start-datetime="$(date -d '-1 hour' +'%F %T')" binlog.* > inc.sql。
加密上传：用openssl aes-256-cbc -salt -in dump.sql.gz -out dump.sql.gz.enc -pass pass:你的密钥，上传到阿里云OSS时，可使用ossutil cp命令自动完成Encryption。

定时调度：Crontab 与 Systemd Timer 的黄金搭配
传统做法是crontab -e添加一行：
0 2 * * * /usr/local/sbin/backup_mysql.sh > /dev/null 2>&1
但Crontab只能精确到分钟，无法处理“上次备份未完成则跳过”的场景，更优雅的方案是使用Systemd Timer：

创建/etc/systemd/system/db_backup.service：

[Unit]
Description=Daily DB Backup
[Service]
Type=oneshot
ExecStart=/usr/local/sbin/backup_mysql.sh

创建同目录下的db_backup.timer：
```
[Timer]
OnCalendar=02:00:00
Persistent=true
RandomizedDelaySec=300
[Install]
WantedBy=timers.target
```
启用后,即使服务器在凌晨2点处于关机状态，开机后也会自动补跑（Persistent=true 特性）。

常见故障问答：锁表怎么办？备份文件损坏如何预警？
问：我用了--single-transaction，但MyISAM表还是被锁了，导致业务卡顿？
答：MyISAM不支持事务快照，--single-transaction只对InnoDB有效。解决方案：对MyISAM表改用--lock-tables=false配合SELECT INTO OUTFILE，但更推荐在生产环境直接全库转换InnoDB引擎。

问：怎么提前发现备份文件损坏？
答：脚本里加一条gzip -t测试命令，若返回非0，立刻触发告警，高级做法是恢复备份到Docker测试库，执行pt-table-checksum校验主从一致性。

问：备份大库（200GB）时，磁盘IO被打满怎么办？
答：使用ionice -c2 -n7和nice -n19降低脚本优先级，或改用mydumper工具（支持并行导出多线程）。

从脚本到体系，你的备份“保险丝”够粗吗？
脚本只是自动化备份的第一层保险丝，真正稳健的体系必须包含：每日全量+每小时binlog增量+每月恢复演练+跨机房容灾，当你用脚本解放了双手，请务必把省下来的时间用于“一次随机的恢复演练”——因为从没验证过的备份，等同于没有备份。

（全文完）

本文基于MySQL 8.0、Ubuntu 22.04环境实测，版本差异请灵活调整命令参数。