📚 课程目录第2章逻辑备份实战

2. 逻辑备份实战：mysqldump 核心参数详解、单库/单表备份、条件导出与数据一致性

逻辑备份，说白了就是用 SQL 语句把数据“倒”出来。MySQL 里最常用的工具就是 mysqldump。很多人觉得它就是个导出命令，跑完就完事了。但我在生产环境见过太多次因为参数没选对，导致备份失败或者恢复不了的案例。今天咱们就把 mysqldump 的核心参数掰开揉碎，讲清楚。

先列一张速查表，这些参数我几乎每天都会用到。

我的习惯：每次写备份脚本，我都会把 --routines --events --triggers 这三个参数写进去。因为默认情况下，mysqldump 不会导出存储过程和事件。我曾经帮一个同事排查恢复失败的问题，折腾了半天，最后发现是存储过程没导出来。

备份一个库，命令很简单：

mysqldump -u root -p --single-transaction --routines --events mydb > mydb.sql

备份一张表呢？在库名后面加上表名就行：

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

多张表用空格隔开：

mysqldump -u root -p --single-transaction mydb users orders payments > core_tables.sql

注意：单表备份时，--routines 和 --events 参数不会生效。因为存储过程和事件是库级别的对象，不是表级别的。如果你需要导出某个库的全部存储过程，建议单独对整个库做一次结构备份。

有时候你不需要全量数据，只想导出最近一周的订单。这时候 --where 就派上用场了。

mysqldump -u root -p --single-transaction mydb orders \
  --where="created_at >= '2024-01-01' AND status = 'completed'" \
  > orders_2024.sql

嗯，这里要注意：--where 后面的条件要写 SQL 的 WHERE 子句格式，不需要写 WHERE 关键字。mysqldump 会自动拼接到 SELECT 语句后面。

我遇到过什么坑呢？有一次，一个同事用 --where 导出数据，条件里写了中文，结果导出来的文件乱码了。原因很简单——终端编码和数据库编码不一致。解决方案：

这是本章最核心的部分。数据一致性，说白了就是备份出来的数据，在时间点上是一致的。你不能导出到一半，别人改了数据，结果表 A 和表 B 的数据对不上。

对于 InnoDB 引擎，--single-transaction 参数会开启一个可重复读（REPEATABLE READ）的事务。mysqldump 会利用 MVCC（多版本并发控制）机制，读取一个一致性快照。这意味着：

对于 MyISAM 引擎，它不支持事务。所以 --single-transaction 对它无效。这时候只能用 --lock-tables，它会锁定所有要备份的表，阻止写入。嗯，这会导致业务短暂不可写，所以 MyISAM 表建议在业务低峰期备份。

  一句话总结：InnoDB 用 --single-transaction，MyISAM 用 --lock-tables。如果你的库是混合引擎，我建议优先用 --single-transaction，然后单独处理 MyISAM 表。

下面这张图，帮你理清 mysqldump 的核心逻辑。

坑一：忘记加 --single-transaction

我曾经接手一个项目，备份脚本里没加这个参数。结果每次备份，业务都会报死锁。为什么？因为默认情况下，mysqldump 会对每张表加读锁，锁表期间其他事务写不了。加上 --single-transaction 后，问题立刻消失。

坑二：大表备份导致磁盘爆满

有一次，一个 200GB 的库，备份文件直接写到了 180GB。服务器磁盘只有 300GB，差点撑爆。后来我改用 --compact 参数，去掉了注释和多余的空行，文件缩小了 30%。如果还不够，可以考虑用 gzip 压缩：

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

坑三：字符集不一致导致乱码

这个前面提过。我的解决方案是：在备份脚本里显式指定字符集：

mysqldump -u root -p --single-transaction --default-character-set=utf8mb4 mydb > mydb.sql