MySQL数据导出实战：mysqldump与SELECT INTO OUTFILE参数详解与避坑指南-开发者社区

你是不是也有过这种经历：客户临时要一份数据报表，你打开终端敲了一条mysqldump命令，等了大半天，导出来的文件发过去，对方打开一看——乱码，或者数据缺了一半，又或者干脆是个0字节的空文件。MySQL 导出数据这件事，看起来就是敲一条命令的事，但实际用起来真的有一堆细节，参数选错了，锁表锁到业务报警；字符集没指定，导出即乱码；文件权限没配好，直接给你报个Access denied。

我在实际工作中接手过好几个数据迁移和备份的项目，MySQL 导出数据的场景太常见了：全库备份、单表导出、跨环境迁移、生成报表、做数据归档，每一种场景对应的最佳姿势都不一样。这篇博文就把我这几年折腾 MySQL 导出数据的经验梳理一遍，重点聊聊mysqldump和SELECT INTO OUTFILE这两条主路线的参数细节、实操步骤，以及那些只有在生产环境里踩过坑才知道的避坑技巧。适合刚接触数据库的初级开发，也适合被数据导出折磨过的运维和 DBA。

1. 先想清楚：你导出数据到底要干什么

很多人拿到导出任务就直接敲命令，其实这是最大的坑。导出数据这个需求，目标不同，工具和参数的选择天差地别，选错了不是低效的问题，是直接出错的问题。

1.1 四个最常见的导出场景

我总结下来，日常遇到的 MySQL 导出数据需求基本可以归成四类。

第一类是备份恢复。这个场景要求导出的文件必须完整、一致，最好还能把存储过程、触发器、视图一起带出来。恢复的时候要能原样还原，不能丢东西。这种需求最适合用mysqldump，而且要用--single-transaction这样的参数来保证一致性。

第二类是数据迁移。从测试环境搬到生产环境，或者从阿里云迁到自建机房。迁移场景最大的痛点是版本差异和字符集差异，源库是 MySQL 5.7，目标库是 8.0，导出来的文件如果带了版本特有的语法，导入就会报错。还有就是 GTID 的问题，这个细节我在后面详细说。

第三类是给业务方导报表。运营同事要一份用户明细，财务要一份订单流水，这种需求通常只要某些字段、某些行，而且要 CSV 或 Excel 格式，还要用 Excel 能直接打开不乱码的编码。这种场景用SELECT INTO OUTFILE最合适，或者干脆用图形客户端导出。

第四类是数据归档清理。把一年前的历史订单导出来存到冷存储，然后从线上库里删掉。这种场景看重的往往是压缩率和导出速度，导出的文件一般不马上用，但将来可能要追溯。

1.2 方案选型对比：三条主流路线

针对上面四类场景，MySQL 导出数据的工具我归纳成三条路线，各有各的适用场景。

方案	优点	缺点	典型场景
`mysqldump`命令行	功能全，支持结构+数据、存储过程、触发器；可在线备份；SQL 文件通用性好	大数据量下速度慢；默认参数可能有坑；恢复时需手动建库	备份恢复、迁移、结构同步
`SELECT INTO OUTFILE`	导出 CSV 速度快；格式可控；适合大数据量单表	需要 FILE 权限；受`secure-file-priv`限制；只导数据不导结构	报表导出、单表大数据引出
图形客户端（如 Navicat、Workbench）	界面化操作，上手快；支持 Excel/CSV 导出	大数据量容易卡死；自动化能力弱；传输层效率一般	临时取数、少量数据快速导出

我会在下面的内容里重点讲mysqldump和SELECT INTO OUTFILE这两条命令行路线，因为它们在自动化脚本里最常用，也最容易出问题。图形客户端说白了就是把命令行的功能包装了一下，理解了底层原理再去用图形界面，心里就有底了。

2. mysqldump 参数详解：不是一条命令就完事

mysqldump是 MySQL 自带的逻辑备份工具，也是最主流的导出方式。但很多人只记住了mysqldump -u root -p 数据库名 > backup.sql，这是最基础最原始的用法，离生产可用还差很远。

2.1 一致性参数：--single-transaction 到底要不要加

这是我最先要强调的参数。InnoDB 存储引擎支持事务，加--single-transaction参数后，mysqldump会基于一个快照执行导出，整个过程不会锁表，也不会阻塞业务读写。不加这个参数的话，默认会使用LOCK TABLES的方式，意味着导出过程中整张表被锁住，线上业务写入会直接卡住或者报锁等待超时。

我接手过一个导出任务，业务方反馈凌晨导出备份期间订单支付超时，排查到最后就是备份脚本没加--single-transaction，导致核心业务表被锁了十几分钟。加了之后，没再出过问题。

注意：--single-transaction只对 InnoDB 表生效。如果库里还有 MyISAM 表，这部分表仍然会被锁。所以生产环境建议业务表都用 InnoDB。

2.2 导出范围参数：--databases、--tables 和 --routines

mysqldump的导出范围控制有几个细节值得单独说。

如果直接写数据库名，比如mysqldump -u root -p mydb > mydb.sql，导出文件里不会有CREATE DATABASE语句。恢复的时候需要先手动建库，再导入。如果你希望文件里带上建库语句，就要加--databases：

mysqldump -u root -p --databases mydb > mydb.sql

加了--databases之后，文件里会包含CREATE DATABASE IF NOT EXISTS和USE mydb，恢复的时候直接mysql < mydb.sql就行。

只导出部分表，用--tables：

mysqldump -u root -p mydb user order --tables > part_tables.sql

注意这里--tables的位置和写法，表名直接跟在数据库名后面，多个表用空格分隔。

存储过程和函数默认是不会被导出的！必须显式加--routines参数，触发器要加--triggers参数。很多人在做迁移的时候漏了这个，结果导入到新环境发现存储过程全没了，又得手动补脚本。

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

2.3 版本兼容参数：--set-gtid-purged=OFF 为什么重要

这是个非常隐蔽但致命的坑。如果你的 MySQL 开启了 GTID 模式（5.6 以后的版本很多都开了），mysqldump默认会往导出文件里写入SET @@GLOBAL.GTID_PURGED='xxxx'这样的语句。恢复的时候，如果目标库 GTID 状态跟源库不一致，就会报错GTID_PURGED can only be set when GTID_EXECUTED is empty，导致导入直接失败。

我自己做迁移的时候就遇到过一次，从一台 GTID 模式的机器导出，导入到另一台刚初始化好的 MySQL，结果报了这个错。排查了半天，就是少了--set-gtid-purged=OFF。

mysqldump -u root -p --single-transaction --set-gtid-purged=OFF mydb > backup.sql

如果你不需要迁移 GTID 相关信息，或者目标库没有开启 GTID，建议一律加上这个参数，省心。

2.4 字符集参数：乱码问题的根源在这里

导出的文件打开乱码，十有八九是字符集没指定。MySQL 有多个层次的字符集：服务器层、数据库层、表级别、客户端连接层。mysqldump导出的时候，如果没有明确指定字符集，就会用默认的character_set_client来编码输出的 SQL 文件。如果数据库里存的是utf8mb4，但客户端用的是latin1，导出来的文件中文必乱。

我的习惯是统一显式指定：

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

导出的文件开头还会有一段SET NAMES utf8mb4，这样导入的时候也会按utf8mb4来解释文件内容，能够最大程度避免中文乱码问题。

2.5 完整参考命令汇总

基于以上参数，我整理了几套常用模板，你可以直接拿来用。

全库备份（含存储过程、触发器，不锁 InnoDB 表）：

mysqldump -u root -p --single-transaction --routines --triggers --set-gtid-purged=OFF --default-character-set=utf8mb4 --databases mydb > /data/backup/mydb_$(date +%Y%m%d).sql

单表导出（不含结构，只要数据）：

mysqldump -u root -p --single-transaction --no-create-info --default-character-set=utf8mb4 mydb user > /data/backup/user_data.sql

只要结构不要数据：

mysqldump -u root -p --no-data mydb > /data/backup/mydb_structure.sql

3. 实操环节：三种典型场景的完整落地过程

有了参数基础，这一节直接进入具体操作。我用三个最常见的场景来完整走一遍流程，每一步怎么操作、中间会遇到什么坑、怎么处理，都写清楚。

3.1 场景一：全库备份和恢复（含存储过程）

假设有一个生产库shop_db，需要完整备份到本地，然后恢复到测试环境验证。

第一步：检查备份目录和权限。先确认用来存备份文件的目录有足够的磁盘空间，避免导出到一半磁盘满了，产出残缺文件。

df -h /data/backup

第二步：执行导出。带上生产环境必备的参数组合。

mysqldump -u root -p --single-transaction --routines --triggers --set-gtid-purged=OFF --default-character-set=utf8mb4 --databases shop_db > /data/backup/shop_db_$(date +%Y%m%d_%H%M%S).sql

命令行会提示输入密码，建议使用包含密码或密钥的管理工具，或者在脚本里通过配置文件读取，不要直接明文写在命令行里。

第三步：验证导出文件。导出完成后不要急着走人，先检查文件大小是否合理：

ls -lh /data/backup/shop_db_*.sql

再用head看一下文件开头是否有乱码：

head -n 50 /data/backup/shop_db_*.sql

如果看到SET NAMES utf8mb4且没有奇怪的乱码符号，基本没有字符集问题。

第四步：恢复到测试环境。先在目标 MySQL 实例上确认当前没有同名库冲突，然后执行：

mysql -u root -p < /data/backup/shop_db_20250101_120000.sql

导入完成后，检查表数量和关键数据条数是否跟源库一致：

USE shop_db; SHOW TABLES; SELECT COUNT(*) FROM orders;

3.2 场景二：单表导出 CSV（SELECT INTO OUTFILE）

运营要一份用户表的所有数据，用 CSV 格式，方便在 Excel 里做透视表。这个场景用SELECT INTO OUTFILE最合适。

第一步：确认导出目录。MySQL 出于安全考虑，对SELECT INTO OUTFILE写了哪些路径有严格限制，叫secure-file-priv。先查一下：

SHOW VARIABLES LIKE 'secure_file_priv';

这个参数有三种情况：

值为空字符串''，表示不限制导出目录。
值为某个具体路径，比如/var/lib/mysql-files/，表示只能导出到这个目录。
值为NULL，表示禁止一切导出。

我遇到过最典型的就是secure_file_priv指向/var/lib/mysql-files/，而你写的路径是/tmp/，结果报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement。

第二步：赋权限。SELECT INTO OUTFILE需要FILE权限，普通账号没有的话要授权：

GRANT FILE ON *.* TO 'exporter'@'%'; FLUSH PRIVILEGES;

注意：FILE权限是全局权限，能让你把数据写到服务器上的文件，属于敏感权限，不要随便乱授。只给到需要做导出的专用账号就好。

第三步：执行导出。确认目录权限后，执行：

SELECT user_id, user_name, email, created_at INTO OUTFILE '/var/lib/mysql-files/user_export.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM users WHERE created_at >= '2024-01-01';

这里说一下关键点：

FIELDS TERMINATED BY ','：字段之间用逗号分隔。
OPTIONALLY ENCLOSED BY '"'：字符串类型的字段用双引号包起来，这样如果字段值里本身有逗号，Excel 打开时不会错位。
LINES TERMINATED BY '\n'：行结束符用换行。如果你导出的文件要用 Excel 在 Windows 上打开，\r\n会更兼容，但一般\n也没问题。

第四步：取文件。因为是写到了 MySQL 所在服务器的目录里，如果你的客户端跟服务器不在同一台机器，还要用别的办法把文件拿回来，比如用scp。这个常见但经常被忽略的步骤，很多新手会直接在自己电脑上找文件，找不到就以为导出失败了。

3.3 场景三：大数据量分批导出

生产环境一张订单表几亿条数据，直接mysqldump整个表会非常慢，而且生成的 SQL 文件巨大，恢复也慢。我的经验是分两种处理方式。

方式一：mysqldump 走流式 + 压缩。

mysqldump -u root -p --single-transaction --quick mydb huge_table | gzip > /data/backup/huge_table.sql.gz

--quick参数让mysqldump逐行读取而不是一次性加载到内存，配合管道交给gzip压缩，IO 压力小很多，备份文件也能压缩到原来的五分之一甚至更小。恢复的时候先解压再导入：

gunzip < /data/backup/huge_table.sql.gz | mysql -u root -p mydb

方式二：按条件分批导 CSV。如果只是想导出一部分数据做分析，可以按主键或者时间范围分批执行SELECT INTO OUTFILE，每次限制条数。比如：

SELECT * INTO OUTFILE '/var/lib/mysql-files/order_1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM orders WHERE order_id BETWEEN 1 AND 100000;

然后再导出下一个区间BETWEEN 100001 AND 200000，以此类推。这样单次压力小，生成的文件也好管理。

4. 常见问题与排查技巧实录

导数据这事，我踩过的坑不少，也帮别人排查过不少。这一节我把高频问题整理成一个速查表，再挑几个深入聊一聊。

4.1 常见问题速查表

现象	可能原因	解决方案
导出文件中文乱码	客户端/连接字符集不一致	加`--default-character-set=utf8mb4`
导入时报`GTID_PURGED`错误	GTID 模式冲突	导出时加`--set-gtid-purged=OFF`
导出时业务卡顿/锁表	没加`--single-transaction`	InnoDB 表务必添加该参数
`OUTFILE`报 1290 错误	`secure_file_priv`限制路径	导出到允许的目录或调整参数
`ACCESS DENIED`错误	缺`FILE`权限或库表权限	合理授权专用账号
导入时文件太大卡死	SQL 文件体积大、无压缩	使用`gzip`压缩，用管道直接导入
存储过程/触发器不见了	忘记加`--routines`和`--triggers`	导出时显式带上两个参数
导出文件为 0 字节	磁盘满或命令执行失败	先查磁盘空间，再查错误输出
导入报`Unknown table`错误	导出时只导了数据没导结构	确认使用`--no-create-info`或分开执行建表脚本

4.2 乱码问题：一层一层排查

乱码是导出数据遇到最多的反馈。有一次同事找我，说导出的 CSV 用 Excel 打开中文全是问号，但我用文本编辑器看文件内容是正常的。这就不是 MySQL 的问题了，是编码保存的问题。

分两层看：

如果文本编辑器打开导出的 SQL 或 CSV 文件，中文就是正常的，说明 MySQL 侧没问题，问题在 Excel。Excel 默认用 ANSI 编码打开 CSV，而文件是 UTF-8 编码，就会出现乱码。解决方案是在导出 CSV 的时候，在文件开头加一个 BOM。MySQL 的SELECT INTO OUTFILE做不到直接加 BOM，但你可以导出后在服务器上处理一下，或者干脆用mysqldump导出后转码。
如果文本编辑器打开本身就是乱码，说明导出时的字符集就错了。检查命令里有没有加--default-character-set=utf8mb4，以及数据库表本身的字符集是不是utf8mb4。

另外一个很多人不知道的小细节：Excel 打开 UTF-8 无 BOM 的 CSV 会乱码，但用 WPS 就不会。如果你的客户群体用的是 Excel，导出 CSV 后最好预处理一下加 BOM。

4.3 权限问题：FILE 权限和目录的三角关系

SELECT INTO OUTFILE的报错里面，Access denied和secure-file-priv限制是最常见的两道坎。前者是账号没权限，后者是路径不合法。

排查思路是三步：

确认账号是否有FILE权限：SHOW GRANTS FOR 'exporter'@'%';
确认服务器secure_file_priv的值：SHOW VARIABLES LIKE 'secure_file_priv';
确认导出目录是否存在、MySQL 进程是否有写权限：ls -ld /var/lib/mysql-files/

如果secure_file_priv设置了值，就把导出路径写成它指定的路径，别想着绕过。

4.4 导入时报字段类型冲突

这个场景常见于用 SQL 文件做数据迁移或者归档恢复。导出的时候没注意，恢复的时候发现报错，比如Data too long for column或者Incorrect integer value。

原因通常是源库和目标库的表结构不一致，比如源库varchar(200)，目标库建的是varchar(50)；或者源库字段是int，目标库是varchar。这种问题没有捷径，只能在导入前先对比两边的表结构：

mysqldump -u root -p --no-data mydb > mydb_structure.sql

先只导入结构，再用SHOW CREATE TABLE逐个核对，确认类型一致后再导数据。这也是为什么我建议导出备份的时候，结构和数据放在同一个文件里，恢复的时候先还原结构，再填充数据，顺序上天然规避了大部分类型冲突。

4.5 外键约束导致导入失败

如果你导出的表之间存在外键关系，导入的时候可能会遇到Cannot add or update a child row这样的错误。原因是数据导入顺序不对，子表的数据先插入了，但父表对应的记录还不存在。

应对方法有两个：

导出的时候不导出外键检查语句，恢复前先禁用外键检查：

SET FOREIGN_KEY_CHECKS = 0;

导入完成后再恢复SET FOREIGN_KEY_CHECKS = 1;。

先导父表数据，再导子表数据。对于单个mysqldump文件，如果用--databases导出，表数据的顺序通常能保证合理，但如果手动分开导出多张表，就要特别注意导入顺序。

5. 一些额外的实用经验和扩充思路

这篇最后，我再分享几条纯个人经验，都是项目里真刀真枪验证过的。

5.1 大表的定时增量导出

之前做一个数据分析平台，每天要把 MySQL 的增量数据导到数仓，数据量单日千万级。我没有用mysqldump，而是基于主键或更新时间字段做的增量导出，配合SELECT INTO OUTFILE按天分区导出 CSV。核心思路是记录上一次导出的最大主键 ID，下次从那个 ID 继续。用一个表或者文件记录断点，比每次全量导出高效太多。

简单的断点记录方式：

-- 查看当前最大主键 SELECT MAX(order_id) FROM orders;

然后下次导出：

SELECT ... INTO OUTFILE '/var/lib/mysql-files/order_inc.csv' ... FROM orders WHERE order_id > 上次的断点值;

5.2 压缩和分卷的组合拳

备份文件太大，单文件不好传输，我的习惯是先压缩再分卷。比如一个 50GB 的 SQL 文件，直接传很慢，用管道压缩后可能只有几个 GB，再按 2GB 一个分卷：

mysqldump -u root -p --single-transaction mydb | gzip | split -b 2G - /data/backup/mydb_backup.sql.gz.

恢复的时候先把分卷合并再解压：

cat /data/backup/mydb_backup.sql.gz.* | gunzip | mysql -u root -p mydb

这个方法在跨机房传输大备份文件的时候特别稳，实测比较省时省力。

5.3 定期做演练

这是我在一次惨痛经历后养成的习惯：备份不是导出来就行，一定要定期在别的实例上做一次恢复演练。上次遇到备份文件生成得很正常，但恢复的时候报错，原因是源库的版本带了特定补丁，导出的 SQL 在目标库版本上不兼容。从那以后，我每个月都会拿最新的备份在测试环境跑一遍完整恢复，验证文件可用的同时也能暴露版本差异问题。

数据导出和数据备份看起来是一件事，但导出只是开始，恢复得了导出的内容才算真正闭环。希望这篇基于我个人经验写的内容能帮你少走一些弯路。