MySQLdump逻辑备份原创

# 0. 先说唯一一个不能漏的参数：`--single-transaction`

备份 InnoDB 表时，不加 --single-transaction 拿到的备份很可能是不一致的——这是 mysqldump 用法里唯一一个「漏了就会在恢复时才发现」的参数。

版本说明

本文写于 2022-03。
mysqldump 常用参数未变，经 2026-07 复核仍适用。

不加它时，mysqldump 逐表导出，表与表之间没有统一的时间点：导 A 表时是 10:00 的数据，导到 B 表已经 10:05，中间发生的事务让两张表对不上。恢复出来就是外键悬空、父子记录状态矛盾。

加上之后，mysqldump 在一个 REPEATABLE READ 事务里完成整次导出，拿到的是一致性快照，且不阻塞业务写入：

mysqldump -u root -p --single-transaction --set-gtid-purged=OFF \
  --databases database_name > database_name.sql

1
2

几条边界：

只对 InnoDB 有效。库里还有 MyISAM 表时它保护不了那些表，那种情况需要 --lock-all-tables（会全库只读，业务停摆）。
不能和 --lock-all-tables 同用（互斥）。
导出期间不要执行 DDL。ALTER/TRUNCATE/DROP 会破坏快照，可能导致备份文件内容错乱且不报错。
要用备份搭从库时，加 --source-data=2（8.0.26 前叫 --master-data=2）把 binlog 位点写进备份文件的注释里，否则拿到备份也不知道该从哪个位点开始复制。

# mysqldump 逻辑备份

# 全库备份

mysqldump -hlocalhost -uroot -P6003 -p --single-transaction \
  --extended-insert=false --set-gtid-purged=OFF --skip-tz-utc \
  --databases database_name > database_name.sql

1
2
3

# 只导出表结构不导出数据

mysqldump --opt -d databasename -u root -p > xxx.sql

# 导出数据不导出结构

/usr/local/mysql/bin/mysqldump  -S /data/db/mysql3388/mysql3388.sock -uroot -p dbname -t tblename --set-gtid-purged=OFF  --skip-tz-utc  > test.sql

# 指定参数备份

mysqldump -udba_root -p -hlocalhost -P6633 -S /data/db/mysql6633/mysql6633.sock \
--skip-tz-utc --extended-insert=false --complete-insert  --set-gtid-purged=OFF \
-n -t  mybase mytable --where "status <> 1 and bad_status <> 1"  > aaaa.sql

1
2
3

-t, --no-create-info Don't write table creation info. 导出数据不导出结构
-C, --compress Use compression in server/client protocol. 压缩
--complete-insert 生成完整的insert语句（带字段名和字段对应的值）
--extended-insert=false 导出的表，每行一个insert语句。
--extended-insert=true 导出的表，一个很长的insert语句。 --set-gtid-purged=OFF 不增加 GLOBAL.GTID_PURGED 变量 --no-create-db, -n 只导出数据，而不导出库结构（不添加CREATE DATABASE 语句）。 --no-create-info, -t 只导出数据，而不导出表结构（不添加CREATE TABLE 语句）。
--no-data, -d 不导出任何数据，只导出数据库表结构。
据库采用北京时间东八区，mysqldump 导出的文件当中显示的 timestamp 时间值相对于通过数据库查询显示的时间倒退了8个小时。如果不带上 --skip-tz-utc 参数，那timestamp字段的数据都会相差8小时，造成数据不一致

--skip-tz-utc 的含义就是当 mysqldump 导出数据时，不使用格林威治时间，而使用当前 mysql 服务器的时区进行导出，这样导出的数据中显示的 timestamp 时间值也和表中查询出来的时间值相同。

# 恢复

导出只是一半，恢复方式决定了备份有没有用。

# 备份文件里带 CREATE DATABASE（用了 --databases）时，直接导入即可
mysql -u root -p < database_name.sql

# 备份文件里没有建库语句时，必须显式指定目标库
mysql -u root -p target_db < table_only.sql

1
2
3
4
5

大文件导入前临时关掉这几项能快很多倍，导完必须改回来：

SET SESSION sql_log_bin = 0;          -- 恢复过程不写 binlog（不需要传到从库时）
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;

1
2
3

# 验证

1. 备份文件本身完整——mysqldump 正常结束时文件末尾一定有这行：

tail -1 database_name.sql

预期输出：

-- Dump completed on 2026-08-24 15:02:11

没有这一行说明导出中途失败了（磁盘满、连接断、被 OOM 杀掉），文件不可用。这是最容易被跳过、也最容易出事的一步——很多「备份一直在跑」的场景，其实每天存下来的都是半截文件。把这条检查写进备份脚本的退出判断里。

2. 内容量级合理：

ls -lh database_name.sql
grep -c '^INSERT INTO\|^CREATE TABLE' database_name.sql

1
2

3. 真的恢复一次。没有恢复演练过的备份不算备份——定期挑一个备份导进临时实例，比对表数量与行数：

SELECT COUNT(*) FROM information_schema.TABLES WHERE table_schema = 'database_name';

# 坑与边界

-p 后面直接跟密码会出现在 ps 输出里，同机器上任何用户都能看到。用交互输入，或写进 ~/.my.cnf（权限 600）/ mysql_config_editor 生成的 .mylogin.cnf。
--set-gtid-purged=OFF 不是无脑加的。它的作用是不把 SET @@GLOBAL.GTID_PURGED 写进备份文件——导入到一个已经有自己复制关系的实例时必须关掉，否则会破坏目标实例的 GTID 状态；但如果这份备份是用来搭新从库的，反而需要保留（默认 AUTO），否则新从库不知道自己已经包含了哪些事务。
--extended-insert=false 会让备份文件大好几倍、导入慢一个数量级。它的价值只在「需要人肉阅读/用 grep 抽取单行」时；常规全量备份应该用默认的 true（一条 INSERT 带多行）。
mysqldump 是单线程的，几十 GB 以上的库导出会非常慢。大库用 mydumper（多线程）或 MySQL Shell 的 util.dumpInstance()，物理备份用 xtrabackup。
--skip-tz-utc 会让备份绑定到导出时的服务器时区。跨时区恢复时 TIMESTAMP 列会整体偏移。同一时区内迁移用它没问题，跨时区场景反而应该保留默认的 UTC 行为。
默认不导出存储过程、函数、事件和触发器全集：触发器默认会导（--triggers 默认开），但存储过程/函数需要 --routines、事件需要 --events。只做全库备份而没加这两个参数，恢复后会发现定时任务和存储过程全没了。
不要用 mysqldump 做「实时同步」。基于时间戳列的增量导出同步不了 DELETE，原理性缺陷，详见单表数据同步方案选型。

#数据迁移 #MySQL

上次更新: 9/11/2026

← MySQL使用SQL语句查重去重 MySQL 基于 GTID 主从复制：跳过异常事务的正确姿势→

MySQLdump逻辑备份原创

# 0. 先说唯一一个不能漏的参数：--single-transaction

# mysqldump 逻辑备份

# 全库备份

# 只导出表结构不导出数据

# 导出数据不导出结构

# 指定参数备份

# 恢复

# 验证

# 坑与边界

# 0. 先说唯一一个不能漏的参数：`--single-transaction`