📖 本篇简介：数据导入导出是 DBA 和开发最常干的活，也是事故高发区——mysqldump 忘加一致性参数锁了大表、LOAD DATA 字符集乱、secure_file_priv 限制路径、导入时 sql_mode 拒数据。本章梳理数据导入导出中的 11 个高频踩坑。示例基于 MySQL 8.0（本机无服务，命令按官方语法书写）。读完你能安全地导数据、不锁表、不丢对象。
目录
- 坑 1：SELECT INTO OUTFILE 被 secure_file_priv 限制
- 坑 2：LOAD DATA 字符集不匹配乱码
- 坑 3：mysqldump 忘 --single-transaction 锁表
- 坑 4：导出忘 --routines --events 丢对象
- 坑 5：导入时 sql_mode 严格拒绝老数据
- 坑 6：LOAD DATA 行格式分隔符对不上导错列
- 坑 7：用 source 导大文件没进度失败难查
- 坑 8：导出 NULL 变成 N 导不回去
- 坑 9：mysqldump 忘 --set-gtid-purged 主从报错
- 坑 10：大表导出走逻辑备份巨慢
- 坑 11：并行导入主键冲突
- 速查表：导入导出速记
- 注意事项：导数据前的 Checklist
- 自测：你会导数据吗
坑 1：SELECT INTO OUTFILE 被 secure_file_priv 限制
场景 / 错误示范：
-- 想把查询结果导成文件
SELECT * FROM orders INTO OUTFILE '/tmp/orders.csv';
-- 报错：The MySQL server is running with the --secure-file-priv option
-- so it cannot execute this statement
为什么是坑：MySQL 出于安全，用 secure_file_priv 限制了文件导入导出的目录。不设时只允许往指定目录写，随便给个 /tmp 路径直接被拒。开发者不知道有这个限制，反复试路径。
正确做法：
-- 先看允许的目录
SHOW VARIABLES LIKE 'secure_file_priv';
-- 例如结果是 /var/lib/mysql-files/，就只能往这个目录写
SELECT * FROM orders INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
-- 或者干脆用 mysqldump 导出，不受 secure_file_priv 限制
-- 导出带表头和分隔符，方便后续给 Excel/分析工具用
SELECT * FROM orders INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
-- 文件在数据库服务器上，要下载到本地得用 scp，不是在客户端机器直接生成
加分点：SELECT INTO OUTFILE 的文件生成在数据库服务器上，不是客户端机器，路径也是服务器的。跨机器导数据用 mysqldump 更顺。
坑 2：LOAD DATA 字符集不匹配乱码
场景 / 错误示范：
-- 导入一个 utf8mb4 的 csv，没指定字符集
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users
FIELDS TERMINATED BY ',';
-- 中文导进去全是乱码，因为文件是 utf8mb4，连接/表字符集对不上
为什么是坑：LOAD DATA 按"连接字符集"解释文件内容。文件编码、连接字符集、表字符集三者任何一处对不上，中文就乱码。和普通乱码一样，只是发生在导入环节。
正确做法：
-- 导入时显式指定文件字符集
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
CHARACTER SET utf8mb4 -- 明确文件是 utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
-- 三处一致：文件、连接、表都用 utf8mb4
-- 导入后抽样核对几行，中文是否正常
SELECT id, name FROM users LIMIT 5;
-- 发现乱码，先查三处字符集是否一致：
SHOW VARIABLES LIKE 'character_set_database'; -- 库
-- 表：SHOW CREATE TABLE users; 看 CHARSET
-- 文件：导入语句的 CHARACTER SET
加分点：导入前先确认 csv 文件本身的编码（用 file 命令或编辑器看），再在 LOAD DATA 里 CHARACTER SET 对齐。
坑 3：mysqldump 忘 --single-transaction 锁表
场景 / 错误示范：
# 导出一个业务库，开发者直接 mysqldump
mysqldump -uroot -p your_db > db.sql
# 默认对 InnoDB 也可能加锁，导出期间业务写被堵住，大表导几小时=锁几小时
为什么是坑：mysqldump 默认会锁表保证一致性，导出期间业务无法写入。对 InnoDB 应该用 --single-transaction，靠 MVCC 在一个一致性快照里导出，不锁表。忘加这个参数，导出就是线上事故。
正确做法：
# InnoDB 导出标准命令：一致性快照，不锁表
mysqldump -uroot -p --single-transaction --quick your_db > db.sql
# --quick：逐行读大表，不在内存里缓存整表
# MyISAM 才需要 --lock-tables；InnoDB 一律 --single-transaction
-- 导出期间可以确认：业务写应该照常进行（不锁表）
-- 如果发现写被堵，说明没加 --single-transaction
# 对照：错误写法（锁表）vs 正确写法（不锁表）
# 错误：mysqldump -uroot -p your_db > db.sql -- 可能锁表
# 正确：
mysqldump -uroot -p --single-transaction --quick --routines --events your_db > db.sql
# 加了 --single-transaction，导出开始时打一个一致性 MVCC 快照，
# 后续业务的写不阻塞，也不影响导出快照的一致性
加分点：--single-transaction + --quick 是 InnoDB 逻辑导出的标配，少一个都可能锁表或撑爆内存。
坑 4：导出忘 --routines --events 丢对象
场景 / 错误示范：
# 导出库准备迁移，开发者只导了表
mysqldump -uroot -p your_db > db.sql
# 导到新库后，存储过程、事件、触发器全没了，业务报错
为什么是坑：mysqldump 默认只导表结构和数据，不带存储过程、函数、事件。触发器默认带，但 routines/events 要显式加参数。迁移完才发现少对象，是经典遗漏。
正确做法：
# 完整导出：表 + 存储过程 + 事件 + 触发器
mysqldump -uroot -p --single-transaction --quick \
--routines --events --triggers your_db > db.sql
# --routines：存储过程和函数；--events：事件调度
加分点：迁移库用这一套参数；只想导单表时才精简。养成"迁移必带 --routines --events"的肌肉记忆。
坑 5：导入时 sql_mode 严格拒绝老数据
场景 / 错误示范：
# 老库导出的数据，导入到 8.0 严格模式的新库
mysql -uroot -p new_db < db.sql
# 报错：Invalid datetime format / Out of range value
# 老数据里有 '0000-00-00'、超范围数字，被严格模式拒了
为什么是坑：8.0 默认 sql_mode 严格，老数据里的零日期、截断值直接导入失败。开发者以为"导个文件而已"，结果一连串报错。
正确做法：
-- 导入前临时放宽 sql_mode（仅导入会话）
SET SESSION sql_mode = '';
SOURCE /path/db.sql;
-- 导入完成后再评估是否恢复严格模式、清理脏数据
-- 更根本：老数据里的零日期先在导出端清理掉
加分点：跨版本导入，先在测试库导一遍把报错摸清，别在生产库上现导现报错。
坑 6：LOAD DATA 行格式分隔符对不上导错列
场景 / 错误示范：
-- csv 是逗号分隔、字段带引号，开发者只写了 TERMINATED BY
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users
FIELDS TERMINATED BY ',';
-- 没指定 ENCLOSED BY，字段里带逗号/引号的数据被拆错列，数据错位
为什么是坑：csv 的格式细节很多——字段分隔符、字段包围符、转义符、行分隔符。LOAD DATA 的子句要和文件实际格式一一对齐，少写一个就列错位，而且不报错，悄悄导错数据。
正确做法：
-- 先 head 看一眼文件实际格式，再写对应子句
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 跳过表头
-- 导完 SELECT 几行核对，确认列没错位
加分点：导入后立刻抽样核对几行数据，比事后对账发现错位便宜得多。
坑 7：用 source 导大文件没进度失败难查
场景 / 错误示范：
-- 几百 MB 的 sql 文件，在 mysql 客户端里 SOURCE
SOURCE /big/db.sql;
-- 跑了半小时，没进度、没百分比，失败了也不知道错在哪一行
为什么是坑：source 是客户端命令，大文件导入时没有进度提示，中途出错只报一个笼统错误，定位困难。几百 MB 文件这样导，既慢又煎熬。
正确做法：
# 直接重定向导入，能看错误，必要时开 binlog 日志
mysql -uroot -p new_db < db.sql 2> import.err
# 看 import.err 找具体出错语句
# 超大文件用 mysqlpump / 并行导出导入，或分库分表分批导
# 大文件导入常见报错排查顺序：
# 1. 看 err 文件最后几行，定位出错的那条语句
tail -50 import.err
# 2. 常见错：零日期（sql_mode）、列数不匹配、字符集
# 3. 导入期间临时关慢日志/关同步校验，速度更快
mysql -uroot -p -e "SET SESSION sql_mode=''; SET SESSION unique_checks=0; SET SESSION foreign_key_checks=0;"
加分点：大文件导入别用 source，用命令行重定向并把 stderr 落文件，出错好排查。
坑 8：导出 NULL 变成 \N 导不回去
场景 / 错误示范：
-- SELECT INTO OUTFILE 导出，NULL 默认写成 \N
-- 开发者看文件里一堆 \N，导入时没处理，把 \N 当字符串存进去
为什么是坑：LOAD DATA 默认把 \N 解释成 NULL，这是约定。但如果手动编辑过文件、或分隔符/转义没对齐，\N 可能被当普通字符串，列里存了字面 "\N" 而不是 NULL。
正确做法：
-- 导入时确认 NULL 的表示方式一致
LOAD DATA INFILE '/tmp/t.csv' INTO TABLE t
FIELDS TERMINATED BY ','; -- 默认 \N 就是 NULL，别额外处理
-- 导完核对：SELECT * FROM t WHERE col = '\\N'，查出来就是导错了
加分点：NULL 和空串在导出文件里是两种表示（\N 和空字段），导入端要和导出端约定一致。
坑 9：mysqldump 忘 --set-gtid-purged 主从报错
场景 / 错误示范：
# GTID 开启的主库导出，直接导到另一个 GTID 环境
mysqldump -uroot -p your_db > db.sql
# 导入时报 GTID 相关错误，或从库复制出问题
为什么是坑：GTID 环境下，mysqldump 默认会在导出文件里写 SET @@GLOBAL.gtid_purged。这个行为在不同场景下需要的参数不同，忘了指定，导入到有 GTID 的库就冲突。
正确做法：
# GTID 环境导出，明确指定
mysqldump -uroot -p --set-gtid-purged=OFF your_db > db.sql
# 如果是要搭新从库，反而要保留 GTID；按场景选 ON/OFF/AUTO
加分点：GTID 环境导数据，--set-gtid-purged 是必想清楚的参数——搭从库要保留，普通迁移要 OFF。
坑 10：大表导出走逻辑备份巨慢
场景 / 错误示范：
# 几十 GB 的大表，用 mysqldump 逻辑导出
mysqldump -uroot -p big_db > big.sql
# 导了几小时，还占大量 IO，业务受影响
为什么是坑：逻辑备份是逐行 SELECT 再拼成 INSERT，大表上极慢且耗 IO。几十上百 GB 的库，逻辑备份根本不现实。
正确做法：
# 大库用物理备份（xtrabackup），直接拷数据文件，快得多
# xtrabackup --backup --target-dir=/backup
# 逻辑备份只适合中小库或单表导出
# 单表导出用 --where 分片，分批导
mysqldump -uroot -p big_db order_t --where="id<1000000" > part1.sql
# 单表按主键分片导出，避免一次导全表
mysqldump -uroot -p big_db order_t --where="id BETWEEN 1 AND 1000000" > p1.sql
mysqldump -uroot -p big_db order_t --where="id BETWEEN 1000001 AND 2000000" > p2.sql
# 分片导出 + 分片导入，单个文件可控、可断点续传
加分点：备份选型——小库逻辑 mysqldump，大库物理 xtrabackup。混了就是慢事故。
坑 11：并行导入主键冲突
场景 / 错误示范：
# 为了快，开多个线程同时导一张表的数据
# 线程 A 导 id 1-10w，线程 B 导 id 10w-20w
# 但若数据分片不均，或自增主键重叠，导入报 Duplicate entry
为什么是坑：并行导入要保证数据分片不重叠。如果按主键范围切，边界没对齐，两个线程导了同一批主键，互相冲突。
正确做法：
# 严格按主键不重叠的范围切分
# 线程1: WHERE id BETWEEN 1 AND 100000
# 线程2: WHERE id BETWEEN 100001 AND 200000
# 边界要对齐、不重叠
# 或者各导不同的表，避免同一表并行
# 不同表并行，最安全：表之间不冲突
# 终端1: mysql new_db < orders.sql
# 终端2: mysql new_db < users.sql
# 终端3: mysql new_db < products.sql
# 同一表想并行，严格按主键范围切，且导前清空目标表/确认无重叠
加分点：并行导入适合"不同表"或"同一表按主键不重叠范围"。同一张表重叠范围并行，必冲突。
速查表：导入导出速记
| 操作 | 坑点 | 正确做法 |
|---|---|---|
| INTO OUTFILE | 路径被拒 | 看 secure_file_priv 指定目录 |
| LOAD DATA | 中文乱码 | CHARACTER SET utf8mb4 对齐 |
| mysqldump InnoDB | 锁表 | --single-transaction --quick |
| 迁移导出 | 丢过程事件 | 带 --routines --events --triggers |
| 导入严格模式 | 拒老数据 | 会话临时放宽 sql_mode |
| LOAD DATA 格式 | 列错位 | 对齐 TERMINATED/ENCLOSED，抽样核对 |
| source 大文件 | 无进度 | 重定向导入，stderr 落文件 |
| NULL 导文件 | 变成 \N | 默认约定，别当字符串 |
| GTID 导出 | 冲突 | 明确 --set-gtid-purged |
| 大库备份 | 逻辑慢 | 物理 xtrabackup |
| 并行导入 | 主键冲突 | 主键范围不重叠 |
注意事项：导数据前的 Checklist
- [ ] InnoDB 导出加了 --single-transaction 吗？
- [ ] 迁移带了 --routines --events --triggers 吗？
- [ ] LOAD DATA 字符集对齐了吗？
- [ ] 文件分隔符和导入子句对齐了吗？
- [ ] 导入后抽样核对列没错位吗？
- [ ] GTID 环境想清楚 --set-gtid-purged 了吗？
- [ ] 大库是不是该用物理备份？
- [ ] 导入在测试库先跑过一遍报错吗？
一句话：导出不锁表靠 --single-transaction，不丢对象靠 --routines --events，导入不乱码不错位列靠字符集和格式对齐——这三件事做对，导数据基本不出事。
自测：你会导数据吗
题 1：SELECT INTO OUTFILE 写 /tmp 报错，为什么？
<details>
<summary>参考答案</summary>
secure_file_priv 限制了允许的目录，只能往它指定的目录写。先 SHOW VARIABLES 看允许路径，或改用 mysqldump。
</details>
题 2：mysqldump 导出 InnoDB 大表，怎么不锁表？
<details>
<summary>参考答案</summary>
加 --single-transaction（一致性快照 MVCC）+ --quick（逐行读）。不加会锁表，导出期间业务写被堵。
</details>
题 3：mysqldump 迁移库，漏了哪个参数会丢存储过程？
<details>
<summary>参考答案</summary>
--routines（还有 --events）。默认只导表结构和数据，存储过程/函数/事件要显式指定。
</details>
题 4：LOAD DATA 导入中文乱码，怎么定位？
<details>
<summary>参考答案</summary>
三处字符集对齐：文件编码、LOAD DATA 的 CHARACTER SET、表字符集。文件本身先确认编码，导入语句显式 CHARACTER SET utf8mb4。
</details>
题 5：LOAD DATA 导完数据列错位，常见原因？
<details>
<summary>参考答案</summary>
FIELDS/LINES 子句和文件实际格式没对齐，漏了 ENCLOSED BY 等。先 head 看文件格式，导完抽样核对。
</details>
题 6：为什么大文件导入别用 mysql> source？
<details>
<summary>参考答案</summary>
source 无进度、出错难定位。用命令行 mysql < file 2> err.log，stderr 落文件好找错行。
</details>
题 7：GTID 环境导出，--set-gtid-purged 怎么选？
<details>
<summary>参考答案</summary>
搭新从库要保留 GTID；普通迁移用 OFF。不指定在某些 GTID 环境导入会冲突。
</details>
题 8：几十 GB 的库用 mysqldump 备份为什么不合适？
<details>
<summary>参考答案</summary>
逻辑备份逐行导出极慢且耗 IO。大库应该用物理备份 xtrabackup，直接拷数据文件。
</details>
题 9：并行导入同一张表，主键冲突怎么避免？
<details>
<summary>参考答案</summary>
按主键严格不重叠的范围切分数据，边界对齐；或各导不同的表。范围重叠必冲突。
</details>
题 10：导入老数据到 8.0 严格模式新库，批量报 Invalid datetime format，怎么办？
<details>
<summary>参考答案</summary>
老数据有零日期等脏数据被严格模式拒。导入会话临时放宽 sql_mode，导入后再清理脏数据。先在测试库跑一遍摸清报错。
</details>
题 11：导出完怎么快速确认导出文件没漏对象？
<details>
<summary>参考答案</summary>
grep 导出文件里的 CREATE PROCEDURE / CREATE EVENT 语句数量，和 information_schema.routines/events 里的数量对一下。少了就是没加 --routines --events。
</details>
下一篇预告：第 59 章 备份恢复踩坑
文章回复
0 条公开回复