人人都会AI编程

18.2 mysqldump 逻辑备份与恢复

更新时间：2026-07-11

mysqldump 是 MySQL 自带的逻辑备份工具，几乎每台安装了 MySQL 的服务器上都能直接使用。它不依赖复杂的第三方组件，备份结果是可读的 SQL 文本文件，这让它在日常运维、数据迁移、小规模恢复中极其实用。

逻辑备份的本质：导出为 SQL 语句

与物理备份（直接复制数据文件）不同，mysqldump 做的是逻辑备份：

  • 它连接到 MySQL 服务端，读取指定的数据库或表中的数据。
  • 将数据和结构定义（DDL）转化为一系列 CREATE TABLE、INSERT 等 SQL 语句。
  • 把这些语句输出到一个文本文件。

恢复时，只需要让 MySQL 执行这个文件里的 SQL，就能重建表结构和写入数据。因为备份结果是纯文本，你可以用任何文本编辑器查看、修改（比如替换表名、过滤数据），也可以用版本管理工具追踪变更。对于百 MB 到几 GB 级别的中小型库，mysqldump 直观、可控，是最常用的备份工具。

优点很明显：

  • 备份文件可读、可编辑，迁移到不同版本甚至不同数据库（如迁移到 TiDB、OceanBase）也相对容易。
  • 可以按库、按表、按条件精准备份，灵活性高。
  • 不需要安装额外工具，运维环境自带。

缺点也必须清楚：

  • 备份大表或整库时，可能锁表时间较长，影响业务。
  • 恢复速度慢，因为恢复时要逐条执行 INSERT，比物理备份恢复慢几个数量级。
  • 不支持增量备份，每次都导出全量数据。

了解这些后，你就能知道什么时候该用 mysqldump，什么时候该换用 XtraBackup 等物理热备份方案。

常用备份命令与参数

日常使用中，你不需要记住几十个参数，但有几个核心参数和组合几乎覆盖了 90% 的场景。

1. 备份整个数据库

mysqldump -u用户名 -p密码 -h主机 -P端口 数据库名 > 备份文件.sql

示例：

mysqldump -uroot -pMyPass123 -h 192.168.1.100 -P 3306 mydb > mydb_full_backup.sql

这条命令会把 mydb 库内所有表的 DDL 和数据导出到 mydb_full_backup.sql。

2. 备份多个数据库

mysqldump -u用户名 -p --databases 库1 库2 库3 > 备份文件.sql

加上 --databases 参数后，备份文件中会包含 CREATE DATABASE 语句，恢复时能自动建库。如果不加，恢复前需要手动创建目标库。

3. 备份所有数据库

mysqldump -u用户名 -p --all-databases > 全量备份.sql

用于整机迁移或全量灾备，注意：会包含系统库（如 mysql、sys 等），恢复时通常只需要业务库，视情况使用。

4. 备份单表或多张表

mysqldump -u用户名 -p 数据库名 表1 表2 > 备份文件.sql

示例：

mysqldump -u用户名 -p mydb orders order_items > orders_backup.sql

只会导出 orders 和 order_items 两张表的结构和数据。适合针对大表单独备份，或者只迁移部分表。

5. 只备份表结构，不要数据

mysqldump -u用户名 -p -d 数据库名 > schema.sql

-d（或 --no-data）让 mysqldump 不导出数据行。常用于导出表结构做版本管理、或者在另一个环境创建相同的表结构。

6. 只备份数据，不要表结构

mysqldump -u用户名 -p -t 数据库名 > data.sql

-t（或 --no-create-info）跳过 CREATE TABLE 语句，适用于将数据导入到已存在的同一表结构中。

7. 对大数据集进行备份，需控制锁和一致性

这是 mysqldump 最需要留意的地方。默认情况下，为了获取一致性快照，它会加 全局读锁（FLUSH TABLES WITH READ LOCK），此时其他连接不能写数据，如果备份时间长业务会中断。通过参数组合可以缓解这个问题。

  • 使用事务保证一致性，不加全局锁（仅适用于 InnoDB 表）
  mysqldump -u用户名 -p --single-transaction 数据库名 > backup.sql

--single-transaction 会在备份开始时启动一个事务，利用 MVCC 读取一致快照，备份期间允许其他事务继续写入，对业务几乎没有影响。这是备份 InnoDB 表的最佳实践。

  • 避免锁表并同时获取 Binlog 位置
  mysqldump -u用户名 -p --single-transaction --master-data=2 数据库名 > backup.sql

--master-data=2 会在备份文件中写入 CHANGE MASTER TO 语句（被注释），记录当前主库的 binlog 文件名和位置。这为后续搭建主从复制或基于时间点的恢复提供了精确的起始位置。用 2 表示写入但注释掉，防止恢复时意外执行。

  • 备份远程服务器，受网络影响时需要压缩
  mysqldump -u用户名 -p --single-transaction 数据库名 | gzip > backup.sql.gz

边备份边压缩，降低网络传输压力和磁盘占用。恢复时用 gunzip < backup.sql.gz | mysql ... 解压导入。

8. 其他实用参数

  • --routines：备份存储过程和函数，默认不导出。
  • --triggers：备份触发器，默认启用。
  • --events：备份定时事件。
  • --hex-blob：以十六进制格式导出二进制字段（BLOB、BINARY 等），避免乱码。
  • --default-character-set=utf8mb4：指定字符集，防止中文乱码。

数据恢复操作

恢复就是让 MySQL 执行备份文件里的 SQL 语句。

基本命令：

mysql -u用户名 -p -h主机 -P端口 数据库名 < 备份文件.sql

如果备份文件里没有包含 CREATE DATABASE 语句，需要先手动创建目标数据库，再导入：

mysql -uroot -p -e "CREATE DATABASE mydb2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -uroot -p mydb2 < mydb_backup.sql

恢复全库备份（包含建库语句）：

mysql -u用户名 -p < all_db_backup.sql

此时无需指定数据库名，因为每个库的 CREATE DATABASE 和 USE 语句都会在文件中。

从压缩备份恢复：

gunzip < backup.sql.gz | mysql -u用户名 -p 数据库名

恢复大备份文件时可能遇到问题：

  • max_allowed_packet 限制：默认 4MB，如果备份中有超长的 INSERT 语句（很多数据行拼接成一条），导入时会报错。可以在客户端登入时指定更大的包大小：
  mysql -u用户名 -p --max-allowed-packet=512M 数据库名 < backup.sql
  • 外键约束问题：主表数据须在子表数据之前恢复，否则外键约束检查会失败。mysqldump 备份时通常会包含 SET FOREIGN_KEY_CHECKS=0;，恢复时会禁用外键检查，导入完成后再恢复，一般不会出问题。
  • 字符集不匹配导致乱码：备份时最好指定字符集（--default-character-set），恢复时也指定相同字符集，或在 my.cnf 中配置客户端字符集。

实际运维中的注意事项

  • 备份前验证可用空间：备份文件可能是实际数据大小的数倍，要确保磁盘有足够空间存放。
  • 定期测试恢复：空有备份文件，不验证可恢复性等于白备。应定期（如每季度）将最新备份恢复到测试环境，确认备份完整有效。
  • 大表备份的时间影响：即使使用 --single-transaction，备份过程中占用磁盘 I/O 和 CPU 也可能影响线上查询性能。对超大表（几百 GB 以上），建议错峰备份或改用物理备份方案。
  • 备份脚本化：将 mysqldump 命令写入 cron 定时任务，配合日期命名文件，并设定自动清理 N 天前的旧备份。一个简单的 crontab 示例：
  0 2 * * * /usr/bin/mysqldump -u backup_user -p'pass' --single-transaction mydb | gzip > /backups/mydb_$(date +\%Y\%m\%d).sql.gz

但密码会暴露在命令历史中，更好的做法是使用 ~/.my.cnf 存储认证信息：

  [client]
  user=backup_user
  password=your_password
  host=localhost

然后脚本中不带 -p，权限设为 600 保证安全。

基于 mysqldump 的迁移与数据抽取

除了备份恢复，mysqldump 还常用于数据迁移：把数据从生产库拉到开发库、从 A 云迁到 B 云。你可以直接通过管道跨服务器传输：

mysqldump -h 源库IP -u用户名 -p --single-transaction 库名 | mysql -h 目标库IP -u用户名 -p 库名

一条命令完成了备份和恢复，无需中转文件。

如果要只迁移表的某部分数据，可以结合 --where 条件过滤：

mysqldump -u用户名 -p 数据库名 表名 --where="create_time >= '2025-01-01'" > recent_data.sql

适合只导出近期的归档数据或者根据主键范围分批迁移。

mysqldump 虽然古老，但它的可靠性、可读性和灵活性让它依然是 MySQL 生态中使用频率最高的工具之一。理解它的参数和限制，能让你 80% 的备份恢复需求都应付得很自然。对于大规模、高频、零锁表的备份需求，下一节会介绍 Percona XtraBackup 的物理热备方案。