线上 MySQL 备份与恢复实操：mysqldump 定时压缩、按日期保留和导入演练

一、问题场景

刚接手一台云服务器上的 MySQL，最怕遇到的不是慢查询，而是有人误删表、误清字段。数据库没备份，误删一次就全完。对运维新手来说，先做逻辑备份最直观：备份出来是 SQL 文件，能看、能传、能按库恢复，也方便在测试环境演练。这里我记录一套日常可用的流程，重点是能恢复。

二、操作步骤

1. 准备备份目录。

mkdir -p /backup/mysql
chmod 700 /backup/mysql

2. 手动备份一个库。

mysqldump -uroot -p --single-transaction --routines --databases appdb | gzip > /backup/mysql/appdb_$(date +%F_%H%M).sql.gz

执行后会提示输入密码，把 appdb 换成你的库名。--single-transaction 对 InnoDB 表会基于一致性快照导出，不会长时间锁表；--routines 会把存储过程和函数一起导出；--databases 会带上 CREATE DATABASE 和 USE 语句。文件名里加日期和时分，避免同一天多次备份互相覆盖。这里我踩过坑：一开始只导出 SQL，不做 gzip，几个 GB 的文件很占盘，传输也慢。如果数据量不大，逻辑备份足够灵活；如果库特别大，再考虑更专业的方案，但先把基础备份跑起来。

3. 写一个定时备份脚本。

脚本里不要交互输密码，也不建议把密码直接写在命令行。可以先创建 /root/.my.cnf：

cat > /root/.my.cnf <<'EOF'
[mysqldump]
user=backup
password=改你的密码
EOF
chmod 600 /root/.my.cnf

再写备份脚本：

cat > /usr/local/bin/mysql_backup.sh <<'EOF'
#!/bin/bash
BACKUP_DIR=/backup/mysql
DATE=$(date +%F_%H%M)
mkdir -p "$BACKUP_DIR"
mysqldump --defaults-extra-file=/root/.my.cnf \
  --single-transaction --routines --databases appdb \
  | gzip > "$BACKUP_DIR/appdb_$DATE.sql.gz"
find "$BACKUP_DIR" -name 'appdb_*.sql.gz' -mtime +7 -delete
EOF
chmod +x /usr/local/bin/mysql_backup.sh

然后执行 crontab -e，加入下面一行，让任务每天凌晨 2 点运行：

0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

思路很简单：每天低峰期跑一次，文件按日期命名，保留 7 天，用 find -mtime 清理旧备份。日志重定向到文件，第二天检查有没有报错。

4. 恢复前先在测试库演练。

先确认备份文件不是 0 字节：

ls -lh /backup/mysql/appdb_*.sql.gz

解压：

gunzip /backup/mysql/appdb_2026-06-01_0200.sql.gz

恢复前不要直接在生产库导入，先在测试库或测试机演练。因为加了 --databases，SQL 文件里会有原库名，直接导入可能覆盖同名库。可以先导入一台测试 MySQL：

mysql -h 10.0.0.20 -uroot -p < /backup/mysql/appdb_2026-06-01_0200.sql

导入前也可以快速看一下文件头部，确认里面有建表和数据语句，避免拿到空文件。导入后确认表数量、关键表行数、存储过程都在，再安排生产恢复。如果只需要恢复一张表，建议从 SQL 文件里提取这张表的建表和 INSERT 语句，不要整库覆盖。

5. 备份文件不要只放本机。

scp /backup/mysql/appdb_*.sql.gz user@backup-server:/backup/mysql/

也可以用 rsync 或对象存储。备份文件不要只放在同一台服务器上，否则系统盘坏了，备份也跟着没。至少同步到另一台机器，重要业务再保留异地一份。如果用的是云服务器，也可以上传到对象存储，但一定要确认之后还能下载回来。

三、注意事项

1. --single-transaction 主要适合 InnoDB。如果库里还有 MyISAM 表，它不能保证完全一致，必要时要考虑 --lock-tables 或维护窗口。
2. 定时任务不是配置完就不管了。要定期检查日志、文件大小和最近一次备份时间，0 字节的备份文件等于没备份。
3. 恢复演练最好用另一台 MySQL 或临时实例，不要在生产机上直接试。尤其使用 --databases 时，SQL 里自带库名，容易误覆盖。
4. find -mtime +7 -delete 使用前，先去掉 -delete 执行一遍，确认匹配到的是旧备份，避免路径写错删错目录。
5. 备份密码文件权限要收紧，/root/.my.cnf 建议 600，备份目录建议 700，避免其他用户读取。

四、小结

MySQL 备份这件事，关键不是命令有多复杂，而是备份能持续生成、能异地保存、能在需要时恢复回来。mysqldump 对中小规模库足够好用，配合 gzip、日期命名、定时任务和 find 清理，就能形成一套基础方案。上线前记得做一次恢复演练，真正出事时才不会慌。