最近在整理一批历史数据迁移方案，正好把MySQL里几种常用的数据导入导出方式从头到尾梳理了一遍。LOAD DATA INFILE、SELECT INTO OUTFILE、mysqldump这三个工具，平时各自用起来都不难，但真正要在生产环境里高效配合、避免踩坑，确实有不少门道。这篇文章就围绕这三板斧展开，讲讲它们各自的适用场景、核心参数、实操细节，以及我在实际项目中遇到的典型问题和解决思路。

不管你是刚接触MySQL的开发者，还是已经在业务系统里摸爬滚打了一阵子的DBA，这篇文章都能给你一个相对完整的参考。特别是如果你经常需要批量导数据、做数据备份、或者在不同环境之间迁移数据，这几个工具用熟了，能省下大量时间，也能少走不少弯路。

1. 三个工具怎么选：先搞懂各自的定位

很多人一上来就纠结"到底用哪个"，其实这三个工具根本不是竞争关系，而是各自解决不同层面的问题。

先看mysqldump。它是MySQL自带的逻辑备份工具，本质是通过SQL语句把表结构和数据以文本形式导出来，生成一个.sql文件。这个文件可以用来重建整个库或表，适合场景是：数据库备份、跨版本迁移、小数据量的快速导出。但因为它走的是SQL语句逐条执行，数据量一旦上了千万级，速度就会明显变慢，恢复过程也会比较久。

再看SELECT INTO OUTFILE。它做的事情简单直接：把查询结果直接写到服务器文件系统上，生成一个纯文本文件，比如CSV或者自定义分隔符的文本。速度快，格式可控，适合把某些表或某些条件的数据导出给外部系统使用。

最后是LOAD DATA INFILE。它和OUTFILE是配套的，负责把文本文件高效导入MySQL表。相比逐条INSERT，它的速度能快10到100倍，核心原因是它在MySQL内部做了批量解析和写入优化，少了大量SQL解析和网络交互的开销。

一句话定位：mysqldump管备份和整体迁移，OUTFILE管数据导出给外部，INFILE管外部数据批量灌进MySQL。实际项目中经常是三者配合使用，比如先用OUTFILE从源库导出，再通过INFILE导入目标库，中间用mysqldump做整体结构备份兜底。

1.1 我的选型判断逻辑

在实际项目里，我一般会先回答三个问题再做决定：数据量有多大？是一次性迁移还是周期性同步？目标端是MySQL还是外部系统？

• 数据量在百万级以下，且需要完整保留表结构和约束，直接mysqldump最省事。
• 数据量在千万级以上，目标是另一个MySQL实例，推荐SELECT INTO OUTFILE导出 +LOAD DATA INFILE导入，速度差距非常明显。
• 数据要给非MySQL系统（比如数据分析平台、报表工具），就用OUTFILE导出CSV或TSV格式。
• 如果只是临时抽几行数据，mysqldump --where就够用了，没必要上OUTFILE。

这套逻辑听起来简单，但现实中很多人栽跟头，就是因为选错了工具。比如拿mysqldump去导几千万行的日志表，跑了一晚上没跑完，最后不得不中断换方案。这个教训我在早期踩过，后来就养成了先评估数据量再选工具的习惯。

2. LOAD DATA INFILE：批量导入的核心实操

LOAD DATA INFILE是我个人最推荐的批量数据导入方式。它的核心优势有两个：一是快，二是灵活。快的原因在于它绕开了逐条SQL执行的开销，直接在存储引擎层面写入；灵活则体现在它支持丰富的字段映射、格式控制和表达式处理。

2.1 基本语法与参数详解

先看一个典型的导入例子：

LOAD DATA INFILE '/tmp/user_data.csv' INTO TABLE user_info FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, name, age, email)

这段SQL做了几件事：

• INFILE指定了文件路径，注意这里是服务器本机路径，不是客户端路径。
• FIELDS TERMINATED BY ','表示字段之间用逗号分隔，这是CSV最常见的形式。
• ENCLOSED BY '"'表示字段值本身可能用双引号包裹，比如"张三"，导入时会自动剥离引号。
• LINES TERMINATED BY '\n'指定行结束符。这里有个坑：如果是Windows下生成的文件，行结束符通常是\r\n，最好先转换或者指定为'\r\n'。
• IGNORE 1 LINES跳过第一行，通常用于跳过表头。
• 最后括号里指定了字段列表，文件中的字段顺序可以和表结构不一致，甚至可以只导入部分字段。

我还经常用到一个技巧，利用SET子句在导入时做转换。比如数据文件里有一个@birthday字段，但目标表需要的是age，可以这样写：

LOAD DATA INFILE '/tmp/user_data.csv' INTO TABLE user_info FIELDS TERMINATED BY ',' IGNORE 1 LINES (id, name, @birthday) SET age = TIMESTAMPDIFF(YEAR, @birthday, CURDATE());

这种写法在数据清洗中是神器，能省掉"先导入临时表、再UPDATE"的中间步骤。

2.2 性能优化：为什么快，还能更快

LOAD DATA INFILE之所以比逐条INSERT快，核心在于它减少了三个开销：SQL解析开销、日志写入开销、网络传输开销。导入过程是MySQL服务端直接从本地文件读取数据，不需要把每行数据包装成SQL语句再解析一遍。

不过默认配置下，它还不是最快的。想要压榨性能，有几个关键设置：

[mysqld] local_infile = ON innodb_buffer_pool_size = 8G innodb_flush_log_at_trx_commit = 2 innodb_autoextend_increment = 64M max_allowed_packet = 128M

其中innodb_flush_log_at_trx_commit这个参数要特别说明。默认值是1，意味着每次事务提交都要同步刷新redo log到磁盘，对数据安全最有利，但性能也最差。导入大批量数据时，我会临时把它改为2（每秒刷一次），或者干脆在导入期间用0，导入完成后再改回来。这个操作牺牲的是一点崩溃恢复的完整性，换来的是导入速度成倍提升。

还有几个顺手就能做的优化：

• 导入前关掉唯一键检查：SET UNIQUE_CHECKS = 0;，导入后恢复。
• 导入前关掉外键检查：SET FOREIGN_KEY_CHECKS = 0;，导入后恢复。
• 如果是MyISAM表，可以先ALTER TABLE ... DISABLE KEYS，导入后再ENABLE KEYS。InnoDB不支持这个操作，但可以通过关掉unique_checks达到类似效果。
• 尽量加大FIELDS TERMINATED BY和LINES TERMINATED BY的效率，比如用\t比用复杂字符串快一点点，不过这种差距在实际使用中基本感知不到。

注意：关闭FOREIGN_KEY_CHECKS和UNIQUE_CHECKS只在导入期间临时操作，导入完务必恢复。我见过有同事导完数据忘记恢复，导致后续业务写入出现诡异的主键冲突错误，排查了很长时间。

2.3 LOCAL模式与secure_file_priv

LOAD DATA INFILE有一个变体：LOAD DATA LOCAL INFILE，区别在于文件是从客户端本机读取，而不是服务器本机。两种模式的适用场景完全不同：

• 不带LOCAL：文件必须在MySQL服务器上，由服务器进程直接读取。速度快，但受secure_file_priv参数限制。
• 带LOCAL：文件在客户端机器上，客户端先把文件内容发给服务器。更方便，但速度会慢一些，而且文件大小受max_allowed_packet限制。

secure_file_priv这个系统变量值得单独说说。它有三个取值：

MySQL 5.7以上的版本默认在Linux下secure_file_priv可能为空字符串或指定目录，Windows安装包默认往往是NULL。如果你执行LOAD DATA INFILE报错说被拒绝，十有八九就是这个参数的问题。解决办法是在my.cnf里显式设置：

[mysqld] secure_file_priv = /var/lib/mysql-files

然后把数据文件放到这个目录下面再执行导入。

2.4 字符集问题

字符集是个大水坑。我踩过最典型的坑是：数据文件用了UTF-8编码，但MySQL连接用的是latin1，导入后中文全部变成乱码。解决方案是导入前显式指定字符集：

SET NAMES utf8mb4; LOAD DATA INFILE '/tmp/user_data.csv' INTO TABLE user_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',';

CHARACTER SET子句告诉MySQL如何解析文件中的字节流，SET NAMES则确保连接层的字符集正确。两者都得对上，缺一个都可能出乱码。

3. SELECT INTO OUTFILE：导出数据的正确姿势

SELECT INTO OUTFILE和LOAD DATA INFILE是一对，一个是导出，一个是导入，语法结构也非常相似。它把查询结果直接写到服务器文件系统，格式完全由你控制。

3.1 基础用法与格式控制

SELECT id, name, age, email INTO OUTFILE '/tmp/user_export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM user_info WHERE create_time >= '2024-01-01';

这里有两个细节值得注意：

第一，OUTFILE的字段分隔符、包裹符和行结束符，需要和后续将要使用的INFILE严格对应。你要是导的时候用了FIELDS TERMINATED BY '\t'，导的时候忘了记录，回头导入时凭感觉猜分隔符，很容易出问题。我的习惯是每次都把导出参数写进注释或者文件名里，避免后续对接时靠猜。

第二，NULL值在导出文件中默认显示为\N，这个跟LOAD DATA INFILE的默认解析规则是对应的。如果你希望其他系统能友好识别，可以通过IFNULL把NULL转换成空字符串或指定值：

SELECT IFNULL(name, ''), IFNULL(age, 0) INTO OUTFILE '/tmp/user_export.csv' FIELDS TERMINATED BY ',' FROM user_info;

3.2 导出目录权限与安全限制

跟LOAD DATA INFILE相同，OUTFILE也受secure_file_priv约束。更让人头疼的是操作系统层面的写权限。MySQL服务的运行用户（通常是mysql用户）必须有目标目录的写权限，否则会报Can't create/write to file错误。

我的排查步骤是：

1. 确认secure_file_priv允许该目录。
2. 确认目录存在，且MySQL服务用户有写权限。
3. 确认磁盘空间充足，导出大表前用df -h看一眼。
4. 确认文件不存在，因为OUTFILE不会覆盖现有文件，如果文件已存在会报错。

注意：如果你导出时报File 'xxx' already exists，别惊讶，这是MySQL故意设计的，目的就是防止意外覆盖数据文件。解决办法很简单，换一个不存在的文件名，或者先手动删除旧文件再执行导出。

3.3 配合LOAD DATA INFILE做极速迁移

这是我个人最推荐的MySQL到MySQL大规模迁移方案。整体流程分三步：

第一步，在源库导出数据：

mysql -u root -h source_host -p -e " SELECT * FROM big_table INTO OUTFILE '/var/lib/mysql-files/big_table.dat' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"

注意，这里导出的时候不需要ENCLOSED BY，因为默认情况下文本字段里的特殊字符（比如分隔符本身）会被自动转义，转义符默认是反斜杠\。这样后续导入的时候，只要两边都使用默认转义规则，就能完美还原。

第二步，把文件传到目标服务器：

scp root@source_host:/var/lib/mysql-files/big_table.dat /var/lib/mysql-files/

第三步，在目标库导入：

mysql -u root -h target_host -p -e " SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; LOAD DATA INFILE '/var/lib/mysql-files/big_table.dat' INTO TABLE big_table FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'; SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1;"

在这个流程里，我用FIELDS TERMINATED BY '\t'而不是逗号，目的是减少文本字段里出现分隔符的概率。即便出现了，默认的反斜杠转义也能保证数据完整。这套方案实测导入千万级数据只要几分钟，而用mysqldump恢复可能需要半小时以上。

4. mysqldump：备份与恢复中的参数细节

mysqldump是MySQL生态里使用率最高的工具，但大多数人只会用最简单的mysqldump -u root -p dbname > backup.sql，这样操作没问题，但在生产环境其实风险不小。

4.1 从基础到进阶的参数组合

基础备份：

# 备份单个库 mysqldump -u root -p --single-transaction --routines --triggers --events dbname > dbname.sql # 备份多个指定库 mysqldump -u root -p --databases db1 db2 > dbs.sql # 备份所有库 mysqldump -u root -p --all-databases > all.sql

--single-transaction这个参数对InnoDB表特别重要。它会在备份开始时开启一个一致性快照事务，这样备份过程中其他会话的写入不会污染备份结果，同时也不需要锁表。所以只要你的表都是InnoDB，就应该加上--single-transaction。

但要注意，--single-transaction对MyISAM表是无效的，因为MyISAM不支持事务。如果库里有MyISAM表，备份期间这些表还是会被锁住。所以生产环境最好的做法是：把核心业务表都换成InnoDB，备份策略会简单很多。

恢复时用：

mysql -u root -p dbname < backup.sql

或者登录MySQL后用source命令：

SOURCE /path/to/backup.sql

4.2 部分数据导出的常用参数

mysqldump不只是用于备份，我也经常用它按条件导出部分数据。最常用的参数是--where：

mysqldump -u root -p dbname user_info \ --where="create_time >= '2024-01-01' AND create_time < '2024-02-01'" \ > user_info_january.sql

这个操作在按时间切片归档数据的时候非常实用。还有几个常用的控制参数：

• --no-data：只导表结构，常用在环境初始化或增量迁移时。
• --no-create-info：只导数据，不导表结构，常用于已有表结构后补数据。
• --compact：去掉注释和额外信息，导出文件更小。
• --skip-add-drop-table：不加DROP TABLE IF EXISTS语句，避免恢复时误删已有表。
• --ignore-table=dbname.table：备份时跳过某些表，比如日志表、中间表等。

4.3 常见备份方案的取舍

并行备份是我最近用下来比较有效的一个方案。单一大mysqldump备份全库，在数据量大的情况下，瓶颈主要在导出过程中的单线程写入。拆成多库并行备份，能显著压缩耗时。比如一个实例有10个库，我会写脚本并行备份每个库到独立文件：

for db in $(mysql -u root -p -e "SHOW DATABASES;" | grep -v Database | grep -v information_schema); do mysqldump -u root -p --single-transaction "$db" > "$db.sql" & done wait

注意这个脚本里我排除了information_schema等系统库，否则会导出一些不该动的东西。

另外，导出后顺手压缩是基本操作：

mysqldump -u root -p --single-transaction --routines dbname | gzip > dbname_$(date +%F).sql.gz

恢复时解压导入：

gunzip < dbname_$(date +%F).sql.gz | mysql -u root -p dbname

对于大库，gzip压缩比例通常在3到7倍，能显著减少磁盘占用和网络传输时间。

5. 排查实录：这些坑我都替你踩过

工具再好用，实操中一定会遇到问题。我把这几年遇到的高频问题整理成一个速查表，以后遇到类似报错可以直接对照解决。

5.1 常见报错速查表

5.2 我印象最深的一次排障

有一次做跨环境数据迁移，用LOAD DATA LOCAL INFILE导入一个300MB的CSV文件，总是报ERROR 2068。当时第一反应是服务端参数问题，检查了一圈发现local_infile是ON，客户端也是对的，但就是报错。最后发现是客户端连接串里少了--local-infile=1这个选项。这个问题的坑在于：客户端库的连接选项和服务端配置是两回事，缺一不可。

还有一次是导出1亿行的日志表，OUTFILE导出一半报磁盘写满。后来优化成按日期分批导出，每个批次一个文件，文件名带上日期标识，既解决了磁盘压力，又方便了后续按时间范围独立导入。

5.3 避坑经验小结

根据我的经验，使用这几个工具时最值得注意的细节有以下几点：

• 修改my.cnf后记得确认配置是否生效。用SHOW VARIABLES LIKE 'secure_file_priv';查看当前值，别等报错再去查。
• 大文件导入前先做小样本验证。拿head -100 data.csv > test.csv生成一个迷你文件，先跑通流程、确认字段映射正确，再导入全量数据。
• 尽量用事务包裹LOAD DATA INFILE和OUTFILE操作，至少对InnoDB表是这样。如果导入中途失败，可以回滚或重导，不会留下半截数据。
• 导入和导出时的FIELDS TERMINATED BY、ENCLOSED BY、LINES TERMINATED BY、ESCAPED BY四个参数必须保持一致。我建议把整条导出SQL记录到文件里，导入时照着抄，别靠记忆。

6. 工具之外的性能策略：从单表到分表

如果你的数据量已经大到单表几千万甚至上亿行，光靠调整工具参数已经不够了，这时候需要从表结构层面配合。

6.1 分区表的导入导出策略

对于按时间范围分区的表，导入数据时有一个技巧：先禁用分区，导入数据，然后重新启用分区。这样可以避免每插入一行就去检查分区对应关系，导入完成后统一做一次分区校验。

ALTER TABLE log_table DISABLE PARTITION; -- 执行LOAD DATA INFILE ALTER TABLE log_table ENABLE PARTITION;

不过这个操作不是所有版本都完美支持，导入完成后建议执行CHECK TABLE验证数据完整性。

另一种更稳妥的做法是按分区导出、按分区导入。比如按月分区，就按月份用OUTFILE导出，文件名带上月份，目标端按同样的分区规则用INFILE分批导入。这样既不锁库，又能精准控制每批数据量。

6.2 从全量到增量：结合定时任务

对于周期性同步场景，我更推荐把工具和增量标识结合起来。比如源库的业务表有update_time字段，那就用OUTFILE导出最近5分钟更新的数据，再通过LOAD DATA INFILE灌进目标库。整个流程用脚本调度，每5分钟跑一次，几乎可以做到准实时同步。

mysql -u root -p source_db -e " SELECT * FROM business_table WHERE update_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) INTO OUTFILE '/var/lib/mysql-files/incremental_$(date +%s).dat' FIELDS TERMINATED BY '\t';"

这种方案的优点是不需要引入额外的同步组件，轻量直接。缺点是需要业务表本身有可靠的增量标识。如果没有，就得退回到全量比对或者考虑其他同步工具了。

我个人的体会是：工具只是手段，最重要的是对数据特性和业务目标有清晰的判断。项目初期我特别喜欢追求工具的高级参数和技巧，后来发现稳定、可复现、易维护的方案，往往才是生产环境里跑得最久的方案。这三个工具组合起来的全流程，我现在用了很多次，已经成了我处理数据迁移的标配：mysqldump导出结构做兜底，OUTFILE导出数据，INFILE导入目标库，最后再用mysqldump校验一次整体数据量。

最后再分享一个实用小技巧：如果你想验证导入前后数据量是否一致，不用一行行数。导入前用wc -l统计文件行数，导入后用SELECT COUNT(*)对比。如果文件没有空行和特殊字符干扰，这两个数字应该完全对得上。我在自动化脚本里就是这么干的，简单粗暴却非常有效。