MySQL 备份与恢复：mysqldump 与定时备份

数据库里往往是整台服务器上最不可替代的东西。硬盘可以重装、程序可以重新部署，但业务数据一旦丢失就很难找回。本文介绍在 Ubuntu/Debian 服务器（VPS）上，用 mysqldump 做逻辑备份并自动化的完整做法。

用 mysqldump 导出

mysqldump 随 MySQL/MariaDB 客户端一起安装，它把库表导出成一份 SQL 文本，恢复时重新执行即可。

# 导出单个数据库
mysqldump -u root -p mydb > mydb.sql

# 导出全部数据库
mysqldump -u root -p --all-databases > all.sql

# 只导出某个表
mysqldump -u root -p mydb users > users.sql

加上 --single-transaction

在线备份最怕导到一半数据还在变，导出结果自相矛盾。对 InnoDB 表，加 --single-transaction 会在一个事务快照里读取，不锁表即可拿到一致视图：

mysqldump -u root -p --single-transaction --quick mydb > mydb.sql

--quick 让 mysqldump 逐行取数据、不在内存里攒整表，适合大表。注意：--single-transaction 只对 InnoDB 有效；若仍有 MyISAM 表，一致性无法保证。

恢复数据

恢复就是把 SQL 重新喂给 mysql。目标库要先建好：

mysqladmin -u root -p create mydb
mysql -u root -p mydb < mydb.sql

用 --all-databases 导出的备份自带建库语句，直接 mysql -u root -p < all.sql 即可。

压缩省空间

SQL 文本压缩比很高，用管道边导边压，不留中间大文件：

mysqldump -u root -p --single-transaction mydb | gzip > mydb.sql.gz

# 恢复时解压回灌
gunzip < mydb.sql.gz | mysql -u root -p mydb

用 cron 每日备份

把命令写成脚本 /usr/local/bin/db-backup.sh，用日期命名、并删除 N 天前的旧备份：

#!/bin/bash
DIR=/var/backups/mysql
mkdir -p "$DIR"
DATE=$(date +%F)
mysqldump -u root -p'你的密码' --single-transaction --all-databases \
  | gzip > "$DIR/all-$DATE.sql.gz"
# 只保留最近 7 天
find "$DIR" -name '*.sql.gz' -mtime +7 -delete

赋权后加入 crontab，每天凌晨 3 点执行：

chmod 700 /usr/local/bin/db-backup.sh
crontab -e
# 加入下面一行
0 3 * * * /usr/local/bin/db-backup.sh

密码写在脚本里有风险，更稳妥的做法是配置 /.my.cnf（权限设为 600）保存凭据，脚本里就不必写明文密码。

异地保存

备份和数据库在同一台机器上，机器坏了就一起没了。务必把备份复制到别处，例如用 rsync 或 scp 推到另一台服务器，或同步到对象存储：

rsync -avz /var/backups/mysql/ user@backup-host:/backups/db/

物理备份简述

mysqldump 属于逻辑备份，恢复靠重放 SQL，数据量特别大时导出和恢复都慢。此时可考虑物理备份：直接复制数据文件。开源工具 Percona XtraBackup 支持 InnoDB 的热备份（不停机），备份/恢复速度快，适合几十 GB 以上的库；代价是配置更复杂、跨大版本迁移不如逻辑备份灵活。

小结

给数据库建备份的关键是「自动 + 异地 + 可验证」：用 mysqldump --single-transaction 拿一致快照，gzip 压缩，cron 每日跑并保留 N 天，再 rsync 到异地。别忘了定期真正演练一次恢复——没验证过的备份等于没有备份。库特别大时再上 XtraBackup 物理备份。