mysqldump备份实战：从单库导出到定时自动备份

知识分享

2026-10-10 18:00

11 阅读

mysqldump 是 MySQL 自带的逻辑备份工具，适合数据量不大、需要跨版本迁移或单独导出某张表的场景。
它导出的是 SQL 语句文件，恢复时用 mysql 命令导入即可。
本文按零基础可执行的方式，把单库备份、全库备份、压缩、定时任务和恢复验证走一遍。

先确认环境和备份目标

操作前先确认三件事，避免命令跑了一半才发现条件不满足。

确认服务器已安装 MySQL 客户端，命令行能执行 mysqldump --version。如果提示 command not found，需要先安装 mysql-client 或 mariadb-client。
确认有一个具备读取权限的数据库账号。生产环境不建议直接用 root，可以单独建备份账号。
确认磁盘剩余空间。逻辑备份文件大小通常接近数据量，压缩后会小一些，但导出过程中会临时占用空间。

创建备份专用账号的示例：

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY '你的强密码';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

如果只备份单个库，把 *.* 换成 库名.* 即可。
授权后先用该账号手动登录一次，确认能正常连接。

单库备份与常用参数

最简单的单库备份命令如下：

mysqldump -u backup_user -p 库名 > /data/backup/库名_$(date +%F).sql

回车后会提示输入密码。
执行完成后用 ls -lh 查看文件大小，再用 head -n 20 看文件开头是否有 CREATE TABLE 等语句，确认不是空文件或报错文本。

实际使用中建议加上几个参数：

mysqldump -u backup_user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  库名 > /data/backup/库名_$(date +%F).sql

--single-transaction：对 InnoDB 表开启一致性快照，减少锁表影响，适合在线业务。
--routines 和 --triggers：把存储过程和触发器一起导出，否则恢复后会丢失。
--events：导出事件调度器中的任务。
--default-character-set=utf8mb4：避免中文乱码，字符集要和库保持一致。

注意：--single-transaction 只对 InnoDB 有效。
如果库里有 MyISAM 表，导出期间仍可能锁表，需要评估业务低峰期执行。

全库备份、压缩与保留策略

需要备份所有数据库时，用 --all-databases：

mysqldump -u backup_user -p \
  --single-transaction \
  --routines --triggers --events \
  --all-databases | gzip > /data/backup/all_$(date +%F).sql.gz

管道加 gzip 可以边导出边压缩，节省磁盘。
恢复时先解压：

gunzip -c /data/backup/all_2024-01-01.sql.gz | mysql -u root -p

备份文件不能无限堆积。
可以用 find 清理 7 天前的文件：

find /data/backup -name "*.sql.gz" -mtime +7 -delete

建议把这条清理命令和备份命令写在同一个脚本里，避免手动维护。

用定时任务实现自动备份

手动执行只能应急，长期要靠 cron。
新建脚本 /data/backup/backup.sh：

#!/bin/bash
BACKUP_DIR=/data/backup
DATE=$(date +%F_%H%M)
mysqldump -u backup_user -p'你的密码' \
  --single-transaction --routines --triggers --events \
  --all-databases | gzip > $BACKUP_DIR/all_$DATE.sql.gz
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

赋予执行权限并加入定时任务：

chmod +x /data/backup/backup.sh
crontab -e

在 crontab 中追加一行，表示每天凌晨 3 点执行：

0 3 * * * /bin/bash /data/backup/backup.sh

保存后可以用 crontab -l 查看是否写入成功。
第二天检查 /data/backup 目录是否出现新文件，确认任务真正生效。

安全提醒：脚本里直接写密码会留在文件中，建议把密码写入 ~/.my.cnf 并设置 chmod 600，命令中就不再需要 -p 参数。

恢复验证与常见报错

备份没验证过，等于没有备份。
定期在测试环境导入一次，确认文件可用。

单库恢复：

mysql -u root -p 库名 < /data/backup/库名_2024-01-01.sql

全库恢复：

gunzip -c /data/backup/all_2024-01-01.sql.gz | mysql -u root -p

恢复前先确认目标库存在，或者 SQL 文件里包含建库语句。
全库备份默认包含 CREATE DATABASE，单库备份不一定包含。

几个高频报错：

Access denied for user：账号权限不足或密码错误，检查授权语句和 .my.cnf 配置。
Got error 28 from storage engine：磁盘空间不足，清理旧备份或扩容。
导出文件很小且开头是报错信息：通常是没有加 --result-file 或重定向写错，命令报错被写进了 SQL 文件。
恢复时提示表已存在：目标库不是空库。要么先删表，要么在导入命令中让 SQL 覆盖。

备份文件建议同时保留本地和异地各一份，只存本机的话，磁盘损坏时备份会一起丢失。

几个实际使用中的疑问

mysqldump 和物理备份怎么选？ InnoDB 数据量在几 GB 以内、需要跨版本迁移或单表导出时，mysqldump 更方便。
数据量到几十 GB 以上，导出和恢复时间会明显变长，可以考虑 XtraBackup 这类物理备份工具。

备份期间业务会卡吗？ 使用 --single-transaction 且表都是 InnoDB 时，一般不会长时间锁表。
但导出会消耗 CPU 和 IO，建议放在业务低峰期。

需要备份 mysql 系统库吗？ 用 --all-databases 会包含系统库，恢复时可能覆盖用户权限表。
如果只想备份业务数据，建议指定库名，避免误覆盖。

怎么确认备份文件是完整的？ 检查文件大小是否合理、能否用 gzip -t 通过完整性校验、以及能否在测试库成功导入。
三个条件都满足，才算一次有效备份。

把备份命令、压缩、清理和恢复验证串成固定流程后，mysqldump 备份实战的稳定性主要取决于两点：定时任务是否真的执行，以及备份文件是否被验证过。
建议每隔一段时间抽查一次恢复结果，再根据数据增长调整保留天数和备份频率。

MySQL索引优化实战：从慢查询到执行计划，新手也能照着做

MySQL binlog配置：从开启到验证的完整步骤