MySQLdump 常用参数说明 - 实践

mysqldump 是 MySQL 官方提供的逻辑备份工具，用于将数据库导出为 SQL 文件，支持全库、单库、单表、结构、数据等灵活备份方式。

系统介绍 mysqldump 的常用参数。
特别是 如何备份存储过程中的注释，在数据恢复时，将存储过程和函数的注释全部恢复

1.mysqldump 常用参数详解（按功能分类）

一、基础备份参数

参数	缩写	作用
--all-databases	-A	备份所有数据库
--databases	-B	备份指定的多个数据库（会包含 CREATE DATABASE 语句）
--databases db1 db2		备份 db1 和 db2
db_name		只备份指定数据库（不加 -B）

mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --databases db1 db2 > db1_db2.sql
mysqldump -u root -p db_name > db.sql

---

二、结构与数据控制

参数	缩写	作用
--no-data	-d	只导出表结构（CREATE TABLE），不导出 INSERT 语句
--no-create-info	-t	只导出数据（INSERT），不导出 CREATE TABLE
--routines	-R	导出存储过程和函数
--triggers		导出触发器（默认开启）
--events	-E	导出事件调度器
--skip-triggers		不导出触发器
--comments		强制保留注释

# 只导出结构
mysqldump -u root -p --no-data db_name > schema.sql
# 只导出数据
mysqldump -u root -p --no-create-info db_name > data.sql
# 导出结构 + 数据 + 存储过程 + 事件 ，有注释信息
mysqldump -u root -p --routines --events --comments db_name > full.sql

---

三、性能与一致性控制

参数	作用
--single-transaction	使用 REPEATABLE READ 事务隔离，保证一致性，不锁表（适用于 InnoDB）
--lock-tables	备份前锁定所有表（MyISAM 需要，InnoDB 不推荐）
--lock-all-tables	锁定所有数据库的所有表（类似 FLUSH TABLES WITH READ LOCK）
--quick	逐行读取，避免将整个结果集加载到内存（默认开启）
--extended-insert	使用批量插入（INSERT INTO tbl VALUES (),(),()），加快导入速度（默认开启）
--net-buffer-length	设置网络传输缓冲区大小，大值可提升速度

# 推荐 InnoDB 备份方式
mysqldump -u root -p --single-transaction --routines db_name > backup.sql

---

四、GTID 与复制相关

参数	作用
--set-gtid-purged=OFF	不导出 SET @@GLOBAL.GTID_PURGED（避免导入时冲突）
--set-gtid-purged=AUTO	自动判断是否导出（默认）
--set-gtid-purged=ON	强制导出 GTID 信息（用于搭建从库）
--master-data=1	添加 CHANGE MASTER TO 语句，用于主从复制（=1 为 CHANGE 语句，=2 为注释）

# 用于搭建从库
mysqldump -u root -p --master-data=2 --single-transaction db_name > slave.sql
# 避免 GTID 冲突（开发环境推荐）
mysqldump -u root -p --set-gtid-purged=OFF db_name > backup.sql

---

五、输出格式控制

参数	作用
--compact	精简输出（去除注释、多余换行等）
--compatible=name	兼容其他数据库模式（如 postgresql, oracle）
--hex-blob	使用十六进制表示 BLOB 字段（避免乱码）
--no-autocommit	每个 INSERT 前加 SET autocommit=0;，提升导入速度
--order-by-primary	按主键排序导出数据（可读性好，但慢）

# 导出时禁用 autocommit，加快导入
mysqldump -u root -p --no-autocommit --extended-insert db_name > fast.sql

---

六、过滤与选择性备份

参数	作用
--ignore-table=db.table	忽略某个表
--tables	只备份指定表（即使前面有 -B）
--where="condition"	只导出满足条件的数据

# 忽略日志表
mysqldump -u root -p --ignore-table=db.log_table db_name > backup.sql
# 只导出部分数据
mysqldump -u root -p --where="created_at > '2025-01-01'" db_name user_table > recent_users.sql

---

2. 推荐备份命令示例

1. 生产环境完整备份（InnoDB）

mysqldump \
--single-transaction \
--routines \
--triggers \
--events \
--set-gtid-purged=OFF \
--no-autocommit \
-u root -p \
db_name > backup_$(date +%Y%m%d).sql

2. 只备份结构

mysqldump -u root -p --no-data --routines db_name > schema.sql

3. 只备份数据

mysqldump -u root -p --no-create-info --extended-insert db_name > data.sql

---

4. 只备份存储过程和函数

mysqldump -u root -p --no-create-info --no-data  --set-gtid-purged=OFF --commentsdb_name db_name > routine.sql

存储过程恢复，包括注释信息！！！

mysql -uroot -p  db_name < routine.sql --comments

一定有 --comments 参数，否则注释就丢失了。

3. 总结

目的	推荐参数组合
全库备份	--all-databases --single-transaction --routines --events
单库备份	--single-transaction --routines --set-gtid-purged=OFF
只导结构	--no-data --routines
只导数据	--no-create-info --extended-insert
避免 GTID 冲突	--set-gtid-purged=OFF
快速导入	--no-autocommit --extended-insert
备份注释信息	--comments

---