mysqldump 使用指南：MySQL 逻辑备份、导出与恢复实战

mysqldump 是 MySQL 自带的逻辑备份工具，输出 SQL 文件，适合数据库迁移、日常备份、数据回滚。属于逻辑备份，和物理备份（xtrabackup、mysqlpump）有适用场景区别。InnoDB 优先使用 --single-transaction 实现无锁一致性备份。

一、基础语法

mysqldump [可选参数] > 备份文件.sql

连接参数说明：

• -u：数据库用户名
• -p：密码，推荐回车后交互式输入，不要直接明文写在命令行
• -h：数据库主机地址
• -P：端口号（大写 P）
• --default-character-set=utf8mb4：指定字符集，避免中文乱码

二、导出备份常用命令

1. 导出单个数据库（生产 InnoDB 推荐）

mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --routines --triggers dbname > dbname.sql

参数解释：

• --single-transaction：基于 InnoDB 事务快照备份，不加锁，线上业务首选
• --routines (-R)：导出存储过程、自定义函数
• --triggers：导出触发器（新版本默认开启）

2. 一次性导出多个数据库

mysqldump -uroot -p --databases db1 db2 > dbs.sql

带上 --databases 会自动包含 CREATE DATABASE 和 USE 语句，恢复时不需要手动新建数据库。

3. 导出实例全部数据库

mysqldump -uroot -p --all-databases > all.sql

4. 只导出库内指定几张表

mysqldump -uroot -p dbname table1 table2 > tables.sql

5. 仅导出表结构，不导出数据（-d）

mysqldump -uroot -p -d dbname > schema.sql

6. 只导出数据，不要建表语句（-t）

mysqldump -uroot -p -t dbname > data.sql

7. 按条件导出部分数据（–where）

mysqldump -uroot -p dbname user --where="id < 1000" > user_part.sql

8. 导出并压缩备份（节省磁盘空间）

mysqldump -uroot -p --single-transaction dbname | gzip > dbname.sql.gz

三、导入恢复 SQL 文件

两种方式任选其一：

方式 1：Shell 直接导入

mysql -uroot -p dbname < dbname.sql

⚠️ 重要提醒：如果备份命令使用了 --databases / --all-databases，导入时不需要指定数据库名，直接执行：

mysql -uroot -p < dbs.sql

方式 2：登录 MySQL 客户端 source 导入

mysql -uroot -p
use dbname;
source /path/dbname.sql;

四、高频参数速查表

表格

五、常见踩坑总结

1. 账号权限：备份账号需要 SELECT, SHOW VIEW, TRIGGER, LOCK TABLES 权限；
2. GTID 报错：从开启 GTID 的实例导出，导入其他实例报错，增加 --set-gtid-purged=OFF；
3. MyISAM 引擎：不支持事务，--single-transaction 无效，备份会锁表；
4. 大数据量限制：mysqldump 属于单线程逻辑导出，速度慢，TB 级库不适合；
5. 明文密码风险：-p123456 会把密码留在命令行历史，尽量交互式输入；
6. 版本兼容问题：高版本 MySQL 导出的 SQL，导入低版本实例容易出现语法不兼容报错。

六、Linux 自动备份脚本示例

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -uroot -p'你的密码' --single-transaction --routines --triggers dbname | gzip > /backup/dbname_${DATE}.sql.gz

配合 crontab 可以实现定时备份，再增加清理逻辑即可自动删除过期备份。

七、适用场景小结

✅ 适合：小、中体量数据库、跨实例迁移、开发环境同步、按需导出部分表 / 部分数据 ❌ 不适合：TB 级超大库、高并发持续写入场景（优先 xtrabackup 物理备份）

如果你想要，我可以继续补充：

1. Windows bat 自动备份脚本
2. 定时备份 + 自动清理过期备份的完整 crontab 方案
3. 跨服务器管道直传备份（不用落地文件）