mysqldump常用参数详解与避坑指南：生产备份与数据恢复实战

一、最常用 & 必须理解的核心参数

1️⃣ --single-transaction（InnoDB 必用 ⭐⭐⭐⭐⭐）

mysqldump --single-transaction db_name > db.sql

它做了什么？

• 在导出前开启一个事务
• 保证 一致性快照
• 不加表锁

前提条件

• 只对 InnoDB 生效
• MyISAM 表依然会被锁

生产环境注意事项

❗ 不要和 --lock-tables 一起用
❗ 导出过程中 不要执行 DDL（如 ALTER TABLE）

---

2️⃣ --quick（大表必用 ⭐⭐⭐⭐）

mysqldump --quick ...

作用

• 一行一行读数据
• 不把全表加载到内存

如果不加会怎样？

• 内存暴涨
• 可能引发 OOM（内存溢出）
• 导出过程中断

👉 实际上 --single-transaction 会隐式开启 --quick，但为了代码清晰和意图明确，建议在命令行中明确写上。

---

3️⃣ --lock-tables=false

mysqldump --single-transaction --lock-tables=false

作用

• 禁止显式 LOCK TABLES

适用场景

• 生产库
• 主库
• 高并发环境

---

4️⃣ --master-data（主从 / 灾备必用 ⭐⭐⭐⭐⭐）

mysqldump --master-data=2 --single-transaction

作用

• 在 dump 文件中记录 binlog 的当前位置（位点信息）。

-- CHANGE MASTER TO
-- MASTER_LOG_FILE='mysql-bin.000123‘，
-- MASTER_LOG_POS=456789;

参数值区别

值	行为
1	位点信息以 SQL 语句形式写入（恢复时会执行）
2	位点信息以注释形式写入（推荐）

---

5️⃣ --set-gtid-purged（GTID 环境必懂）

--set-gtid-purged=OFF

三种参数值

值	含义
AUTO	默认值，如果源库开启了GTID，则可能写入GTID信息
ON	强制写入 GTID 信息
OFF	不写入 GTID 信息（最安全）

强烈建议

👉 跨环境恢复（例如从生产库导出的数据恢复到测试库），一律使用 OFF，避免因GTID冲突导致数据无法恢复。

---

二、结构 & 数据控制参数

6️⃣ 只导结构 / 只导数据

--no-data        # 只导出表结构
--no-create-info # 只导出数据，不包含 CREATE TABLE 语句

---

7️⃣ 忽略某些表

--ignore-table=db_name.big_table

👉 必须写 完整库名.表名。

---

8️⃣ 导出存储过程 / 事件

--routines --events --triggers

⚠️ 默认情况下：

• 存储过程 (routines) ❌ 不导出
• 事件 (events) ❌ 不导出
• 触发器 (triggers) ✅ 导出

---

三、性能 & 体积优化参数

9️⃣ --extended-insert（默认开启）

--extended-insert

效果

将多行数据合并到一条 INSERT 语句中。

INSERT INTO t VALUES (...)，(...)，(...);

✅ 优点：显著减小生成的 SQL 文件体积，加快恢复速度。
❌ 缺点：如果其中一行数据有问题，可能导致整条 INSERT 语句失败。

---

🔟 --skip-comments

--skip-comments

• 减少导出文件的体积。
• 使文件内容更紧凑，方便使用 diff 等工具进行比较。

---

1️⃣1️⃣ --compress（远程导出时使用）

--compress

• 仅对客户端与MySQL服务器之间的网络传输进行压缩。
• 不影响最终 dump 文件的大小，文件落地后仍是未压缩的 SQL 文本。

---

四、字符集 & 编码（非常关键）

1️⃣2️⃣ --default-character-set

--default-character-set=utf8mb4

如果不指定可能出现

• 中文乱码
• Emoji 等特殊字符丢失

强烈建议在导出时明确指定与数据库一致的字符集，utf8mb4 是目前最通用的选择。

---

五、恢复相关参数（dump 中的坑）

1️⃣3️⃣ --add-drop-table

--add-drop-table

恢复前会自动在 CREATE TABLE 语句前添加：

DROP TABLE IF EXISTS ...

⚠️ 生产环境慎用，避免在恢复时误删除现有表。

---

1️⃣4️⃣ --databases vs --all-databases

--databases db1 db2
--all-databases

主要区别：
使用这两个参数导出的文件会包含 CREATE DATABASE 和 USE database 语句，方便直接恢复到同名库。而直接指定库名 mysqldump db_name 则不会包含这些语句。

---

六、生产推荐的 mysqldump 模板（直接可用）

✅ 单库备份（GTID 环境）

这是一个兼顾了一致性、安全性和完整性的通用命令模板，非常适合 MySQL数据备份 场景。

mysqldump \
-u backup -p \
--single-transaction \
--quick \
--lock-tables=false \
--master-data=2 \
--set-gtid-purged=OFF \
--routines --events --triggers \
--default-character-set=utf8mb4 \
db_name > db_name.sql

---

✅ 多库备份

mysqldump \
--databases db1 db2 \
--single-transaction \
--set-gtid-purged=OFF \
> multi_db.sql

---

✅ 单表备份

mysqldump db_name table_name > table.sql

---

七、常见事故 & 注意事项（血泪总结）

❌ 忘了加 --single-transaction

👉 导致导出期间数据状态不一致，无法获得一个逻辑时间点上的完整快照。

❌ InnoDB 和 MyISAM 表混用

👉 即使使用了 --single-transaction，其中的 MyISAM 表依然会被锁，影响线上业务。

❌ GTID 参数配置错误

👉 在主从复制环境中，如果源库有 GTID 而导出时未使用 --set-gtid-purged=OFF，可能导致备份文件无法在另一个开启了 GTID 的实例上恢复。

❌ 导出成功 ≠ 一定能恢复成功

👉 一定要进行恢复验证！ 定期将备份文件在测试环境进行恢复演练，是保证备份有效性的关键 运维实践。

---

本文由云栈社区整理发布，旨在提供清晰实用的技术指南。