数据丢了才想起备份？我用 mysqldump + crontab 搭了一套 MySQL 自动备份，几个坑复盘

AI摘要

文章讲述作者因误操作导致数据丢失后，通过mysqldump和crontab搭建MySQL自动备份系统的经历。文章详细介绍了mysqldump的基本用法，强调了几个重要参数的作用，如`--single-transaction`保证数据一致性、`--routines`导出存储过程等。此外，还提醒了密码保护的重要性以及如何避免因备份文件过大而耗尽磁盘空间的问题。

起因：一条 DELETE 没有 WHERE

三年前的事了。凌晨两点改一个批量处理脚本，手滑在终端里敲了一条 DELETE FROM orders，回车下去半秒就意识到不对——忘了加 WHERE。

那是个电商测试环境，数据量不大，几万条订单。没有备份。

后来靠 binlog 一点点恢复了。从那天起，我每台服务器上的数据库都有定时备份，无论多简单。

mysqldump 的基本用法，但有些参数没人告诉你为什么加

最基础的备份命令都见过：

mysqldump -uroot -p --all-databases > /backup/all_$(date +%F).sql

这能跑，但生产的备份不能这么写。我现在每条备份命令至少带这几个参数：

mysqldump -uroot -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --all-databases \
  | gzip > /backup/db_$(date +%F_%H%M).sql.gz

一个一个说：

--single-transaction：对于 InnoDB 表，在备份开始前开启一个事务快照，保证备份过程中数据一致性。不加这个参数的话，如果备份期间有人写数据，导出的可能是"半新半旧"的状态。我早期不知道这个，恢复出来的数据外键对不上，排查了两天。

⚠️ 这个参数只对 InnoDB 生效。如果有 MyISAM 表，备份期间相关表会被锁住，写入会阻塞。

--routines：导出存储过程和函数。有一次迁移数据库后发现几个定时清理日志的存储过程丢了，原因就是备份没带这个参数。

--triggers：导出触发器。不加的话触发器不会出现在备份文件里。

--events：导出定时事件（Event Scheduler）。如果你的业务用了 MySQL 的定时任务（比如每天晚上统计报表），不加这个参数丢了很难发现。

--hex-blob：把 BLOB/BINARY 字段转成十六进制导出。不转的话二进制数据可能损坏，特别是包含图片缩略图、加密字段的表。我吃过亏：某张表的 avatar 字段恢复后全是乱码。

第二个坑：不要用纯文本存密码

很多教程直接在命令里写 -pYourPassword。在共享服务器上，ps aux 就能看到进程命令行参数，你的数据库密码就暴露了。

正确做法：用 ~/.my.cnf 配置免密登录。

[mysqldump]
user=root
password=your_password

然后：

chmod 600 ~/.my.cnf
mysqldump --defaults-extra-file=~/.my.cnf --single-transaction --all-databases | gzip > backup.sql.gz

mysqldump 读取 .my.cnf 时不需要在命令行暴露密码，ps 也看不到。

还有一个细节：.my.cnf 里如果有 [client] 段，所有 MySQL 客户端工具（mysql、mysqldump、mysqlcheck）都会读取。如果想隔离，用 [mysqldump] 段单独配。

第三个坑：超大数据库备份把磁盘撑爆了

生产环境一张日志表 30GB，mysqldump 导出的 SQL 文件 25GB，gzip 后仍然 3GB。一个月的备份就把磁盘吃光了。

后来我改成按库分别备份 + 定期清理：

#!/bin/bash
BACKUP_DIR="/backup/mysql"
KEEP_DAYS=7

# 跳过系统库，只备份业务库
for db in $(mysql -e "SHOW DATABASES;" | grep -Ev "Database|information_schema|performance_schema|mysql|sys"); do
    mysqldump --defaults-extra-file=~/.my.cnf \
        --single-transaction --routines --triggers --events --hex-blob \
        "$db" | gzip > "$BACKUP_DIR/${db}_$(date +%F).sql.gz"
done

# 删除 7 天前的备份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

这个脚本我放在 /etc/cron.daily/ 下，每天凌晨自动跑。

另外对于超大表还有一个选择：--where 按条件增量导出，或者改用 mysqlpump（并行导出，MySQL 5.7+ 自带），速度比 mysqldump 快不少。

第四个坑：备份了但从来没验证过

我有一次数据库崩溃，信心满满地去恢复备份——发现最近三天的备份文件都是 20KB。原因是磁盘满了，mysqldump 写文件失败，但 crontab 的脚本没做错误检查，静默失败了。

现在每个备份脚本最后都加校验：

# 检查备份文件是否大于 1MB（小于说明可能失败了）
BACKUP_FILE="$BACKUP_DIR/${db}_$(date +%F).sql.gz"
if [ -f "$BACKUP_FILE" ] && [ $(stat -c%s "$BACKUP_FILE") -gt 1048576 ]; then
    echo "[OK] $BACKUP_FILE ($(du -h "$BACKUP_FILE" | cut -f1))"
else
    echo "[FAIL] Backup too small or missing: $BACKUP_FILE" >&2
fi

另外至少每个月手动恢复一次备份到测试库验证完整性。恢复命令：

gunzip < backup.sql.gz | mysql -uroot -p target_db

crontab 定时配置

放到 crontab 里自动跑：

# 每天凌晨 2 点全量备份
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

我习惯把日志重定向到文件，出问题的时候 grep 一下就知道哪天开始失败的。

异地备份

本地备份只防误删和软件故障。机房着火、硬盘物理损坏、勒索病毒——这些场景本地备份没用。

我现在把备份文件同步到阿里云 OSS，脚本最后加一行：

# 同步到 OSS（用 ossutil）
ossutil cp "$BACKUP_FILE" oss://my-backup-bucket/mysql/ -u

小项目也可以用 rsync 推到另一台服务器：

rsync -avz -e "ssh -p 22" /backup/mysql/ user@backup-server:/backup/mysql/

踩坑总结

--single-transaction 必须加——不加的话备份期间的数据一致性没保障
密码不要暴露在命令行——用 ~/.my.cnf + chmod 600
按时清理旧备份——不然磁盘迟早撑满，新备份也写不进去
备份完要校验大小——静默失败的备份比没有备份更危险，你误以为有备份
异地一份——本地备份和数据库在同一台机器上，硬盘坏了全都没了

数据库备份这件事，做好了没人夸你，做坏了一次就够了。我那次手滑 DELETE 之后，每个项目的 README 里备份方案都是第一行就写的。