1 需求背景
应用侧的同学需要对数据进行导出和导入，于是跑来找 DBA 咨询问题： MySQL 如何导入大批量的数据？
应用侧目前的方式：
DBA 听了觉得挺好的呀！
DBA 想了，我的数据库我做主。通知应用侧，目前先使用之前熟悉的方式进行，测试之后给建议。
Tips：为了防止导入时出现大事务，造成主从延迟。
2 方案准备
待测方案：mysqldump、mydumper、select outfile 语句、 Util.dumpTables 、 Util.exportTable。
环境配置信息
| 配置项 | 说明 |
|---|---|
| MySQL 版本 | 5.7.39 |
| 磁盘随机读写 | 100 MiB/sec |
| 测试表名 | test.t_order_info |
| 行数 | 1000W |
| 字段数 | 6 |
建表语句
CREATE
TABLE
`t_order_info` (
`ID`
bigint(
20)
unsigned
NOT
NULL AUTO_INCREMENT
COMMENT
'自增主键ID',
`order_no`
varchar(
64)
NOT
NULL
DEFAULT
'0000'
COMMENT
'订单编号',
`order_status`
varchar(
2)
NOT
NULL
DEFAULT
'01'
COMMENT
'订单状态: 00-异常、01-待处理、02-进行中、03-已完成',
`flag`
tinyint(
4)
NOT
NULL
DEFAULT
'1'
COMMENT
'删除标识: 1-正常、0-逻辑删除',
`create_time` datetime
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
COMMENT
'创建时间',
`modify_time` datetime
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
COMMENT
'更新时间',
PRIMARY
KEY (
`ID`),
UNIQUE
KEY
`IDX_ORDER_NO` (
`order_no`)
)
ENGINE=
InnoDB AUTO_INCREMENT=
1
DEFAULT
CHARSET=utf8mb4
COMMENT=
'订单表'
导出文件
导出导入命令
| 导出 | 导入 |
|---|---|
| mysqldump | source 或 mysql< xxx.sql |
| mydumper | myloader |
| select outfile | load data |
| Util.dumpTables | Util.loadDump |
| Util.exportTable | Util.importTable |
3 方案测试
测试首先考虑的是 提升导入效率，并新增了 MySQL Shell 的使用。
mysqldump
单表导出（备份文件）
mysqldump --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=off --hex-blob --tables test t_order_info
--master-data=2 参数会在备份期间对所有表加锁
FLUSH TABLES WITH READ LOCK，并执行
SHOW MASTER STATUS 语句以获取二进制日志信息。因此，在备份期间可能会影响数据库的并发性能。如果您不需要进行主从复制，则可以考虑不使用
--master-data=2 参数。--single-transaction 参数用于在备份期间“使用事务来确保数据一致性”，从而避免在备份期间锁定表。[必须有]备份文件
文件内容。
-- Table stricture for table `t_order_info`
--
DROP
TABLE
IF
EXISTS
`t_order_info`;
/*!40101 SET @saved_cs_client= @@character_set_client */;
/*!49101 SET character_set_client = utf8 */;
CREATE
TABLE
`t_order_info` (
`ID`
bigint(
2)
unsigned
NOT
NULL AUTO_INCREMENT
COMMENT
'自增主键ID',
`order_no`
varchar(
64)
NOT
NULL
DEFAULT
`0000`
COMMENT
'订单编号'，
`order_status`
varchar(
2)
NOT
NULL
DEFAULT
'01'
COMMENT
'订单状态: 80-异常、81-待处理、2-进行中、03-已完成',
`flag`
tinyint(
4)
NOT
NULL
DEFAULT
'1'
COMMENT
'删除标识: 1-正常、0-逻辑删除',
`create_time` datetime
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
COMMENT
'创建时间',
`modify_time` datetime
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
ON
UPDATE
CURRENT_TIMESTAMP
COMMENT
'更新时间',
PRIMARY
KEY (
`ID`),
UNIOUE
KEY
`IDX_ORDER_NO` (
`order no`)
)
ENGINE=
InnODB AUTO_INCREMENT=
10129913
DEFAULT
CHARSET=utf8m
COMMENT=
'订单表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t_order_info`
--
LOCK
TABLES
`t_order_info` WRITE;
/*!40000 ALTER TABLE `t_order_info` DISABLE KEYS */;
文件内容解释：
没有建库语句， 因为是单表备份。
有删除表，建立表的语句， 小心导入目标库时，删除表的语句，造成数据误删。
INSERT 语句没有字段名称， 导入时表结构要一致。
导入过程中有
lock table write 操作，
导入过程中相关表不可写。
ALTER TABLE t_order_info DISABLE KEYS 此语句将禁用该表的所有非索引，
这可以提高插入大量数据时的性能。 对应的文件末尾有
ALTER TABLE t_order_info
ENABLE KEYS;
用途，可以将备份文件中的数据导入自定义库，“文件内容解释”部分遇到的问题可以使用下面参数解决。
--no-create-info 不包含建表语句（可以手动创建
create table tablename like dbname.tablename;）
--skip-add-drop-database 不包含删库语句
--skip-add-drop-table 不包含删表语句
--skip-add-locks INSERT 语句前不包含
LOCK TABLES t_order_info WRITE;
--complete-insert INSERT 语句中包含 列名称（新表的列有增加的时候）。
单表导出备份数据（只导出数据）。
mys
qldump --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=off --hex-blob --no-create-info --skip-add-drop-table --skip-add-locks --tables dbname tablename
// 部分数据导出追加参数
--where="create_time>'2023-01-02'"
导出单库中的某表为 CSV。
// 可选不导出表结构，
--no-create-info --skip-add-drop-database --skip-add-drop-table
/data/mysql/3306/base/bin/mysqldump -uadmin -p123456 -P3306 -h127.0.0.1 --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --triggers --routines --events --hex-blob --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' -T /data/mysql/3306/tmp test
//其中 test 后面也可以指定表名，不指定就是全库。
test t_order_info t_order_info01
其中 --single-transaction --set-gtid-purged=OFF --triggers --routines --events --hex-blob
为了防止提示，可选
小结
1G 的备份文件，测试结果如下：
-
使用 mysql< xxx.sql 导入，耗时 5 分钟。 -
使用用 source xxx.sql 导入， 耗时 10 分钟。
推荐第一种，都是单线程。
mydumper
多线程导出
mydumper -u admin -p 123456 -P 3306 -h 127.0.0.1 -t 8 --trx-consistency-only -G -E -R --skip-tz-utc --verbose=3 --compress --no-schemas --rows=1000000 -T test.t_order_info -o /backup
// 导出时支持部分导出追加参数
--
where=
"create_time>'2023-01-02'"
// 文件输出
test01.t_order_info.00000.dat
# 包含 CSV 数据
test01.t_order_info.00000.sql
# 包含 LOAD DATA 语句
// 导入命令
LOAD DATA LOCAL INFILE
'/data/mysql/3306/tmp/test01.t_order_info.00005.dat' REPLACE INTO TABLE `t_order_info` CHARACTER SET binary FIELDS TERMINATED BY
',' ENCLOSED BY
'"' ESCAPED BY
'\\' LINES STARTING BY
'' TERMINATED BY
'\n' (`ID`,`order_no`,`order_status`,`flag`,`create_time`,`modify_time`);
myloader -u admin -p 123456 -P 3306 -h 127.0.0.1 --
enable-binlog -t 8 --verbose=3 -B
test -d /backup
// 导入主库时需要添加
--
enable-binlog
// 库名可以自定义
-B
test
小结
耗时 2 分钟，建议如下：
--where 过滤。
mydumper -u admin -p 123456 -P 3306 -h 127.0.0.1 -t 8 --trx-consistency-only -G -E -R --skip-tz-utc --verbose=3 --
where=
"create_time>'2023-01-02'" --no-schemas --rows=1000000 --load-data --fields-terminated-by
',' --fields-enclosed-by
'"' --lines-terminated-by
'\n' -T test.t_order_info -o /backup
导入命令同上，且可以按需手动进行
LOAD DATA。
SELECT OUTFILE 语句
Tips：适合于单表数据的导出，不支持多表。
导出命令，耗时 15 秒。
SELECT *
from test01.t_order_info
INTO
OUTFILE
"/data/mysql/3306/tmp/t_order_info0630_full.csv"
CHARACTER
SET utf8mb4
FIELDS
TERMINATED
BY
','
OPTIONALLY
ENCLOSED
BY
'\'' LINES TERMINATED BY '\n
';
// 带列名导出，导入时需添加 IGNORE 1 LINES;
SELECT * INTO OUTFILE "/data/mysql/3306/tmp/t_order_info0630_full.csv" CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',
' OPTIONALLY ENCLOSED BY '\
''
LINES
TERMINATED
BY
'\n'
from (
select
'id',
'order_no',
'order_status',
'flag',
'create_time',
'modify_time'
union
all
select *
from test01.t_order_info) b;
导入命令，耗时 3 分钟。
mysql -uadmin -P3306 -h127.0.0.1 -p123456
--local-infile
load
data
local
infile
'/data/mysql/3306/tmp/t_order_info0630_full.csv'
into
table test.t_order_info
CHARACTER
SET utf8mb4
fields
terminated
by
','
OPTIONALLY
ENCLOSED
BY
'\'' lines terminated by '\n
';
小结
MySQL_Shell > dumpTables
单表导出，耗时 4 秒。
util.dumpTables(
"test", [
"t_order_info"],
"/backup")
部分导出。
util.dumpTables(
"test", [
"t_order_info"],
"/backup", {
"where" : {
"test.t_order_info":
"create_time>'2023-01-02'"}})
导入，耗时 3 分钟。
util.loadDump(
"/backup")
注意：不支持部分导入，不支持跨数据库版本。
因为导入时最大支持 2 个参数，可以将导出的部分数据全部导入到新的库中。
导入命令：
util.loadDump("/backup",{schema: "test_new"})
小结
SELECT OUTFILE 同效，导入时，比
LOAD DATA 快（默认 4 线程）。注意：
部分导出功能需要较新的 MySQL Shell 版本，如 8.0.33。 LOAD DATA 单线程导入 耗时 1h20min。
MySQL_Shell > exportTable
单表导出，耗时 10 秒。
util.exportTable(
"test.t_order_info",
"/backup/t_order_info.csv", {
defaultCharacterSet:
"utf8mb4",
fieldsOptionallyEnclosed:
true,
fieldsTerminatedBy:
",",
linesTerminatedBy:
"\n",
fieldsEnclosedBy:
'"',
defaultCharacterSet:
"utf8mb4",
showProgress:
true,
dialect:
"csv"})
部分导出。
util.exportTable(
"test.t_order_info",
"/backup/t_order_info.csv", {
dialect:
"csv",
defaultCharacterSet:
"utf8mb4",
fieldsOptionallyEnclosed:
true,
fieldsTerminatedBy:
",",
linesTerminatedBy:
"\n",
fieldsEnclosedBy:
'"',
showProgress:
true,
where:
"create_time>'2023-01-02'" } )
导入，耗时 10 分钟。
util.importTable(
"/backup/t_order_info.csv", {
"characterSet":
"utf8mb4",
"dialect":
"csv",
"fieldsEnclosedBy":
"\"",
"fieldsOptionallyEnclosed":
true,
"fieldsTerminatedBy":
",",
"linesTerminatedBy":
"\n",
"schema":
"test",
"table":
"t_order_info" })
部分导入（不推荐使用）。
util.importTable(
"/backup/t_order_info.csv", {
"characterSet":
"utf8mb4",
"dialect":
"csv",
"fieldsEnclosedBy":
"\"",
"fieldsOptionallyEnclosed":
true,
"fieldsTerminatedBy":
",",
"linesTerminatedBy":
"\n",
"schema":
"test100",
"table":
"t_order_info" })util.importTable(
"/backup/t_order_info0630.csv", {
"characterSet":
"utf8mb4",
"dialect":
"csv",
"fieldsEnclosedBy":
"\"",
"fieldsOptionallyEnclosed":
true,
"fieldsTerminatedBy":
",",
"linesTerminatedBy":
"\n",
"schema":
"test",
"table":
"t_order_info" })
有报错
MySQL Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction @ file bytes range [450000493, 500000518) 需要重复执行一次，才能保证数据完整。
根据报错提示可以使用以下命令导入：
LOAD DATA LOCAL INFILE
'/backup/t_order_info0630.csv' INTO TABLE `
test`.`t_order_info` CHARACTER SET
'utf8mb4' FIELDS TERMINATED BY
',' OPTIONALLY ENCLOSED BY
'\"' ESCAPED BY
'\\' LINES STARTING BY
'' TERMINATED BY
'\n';
MySQL 5.7 也推荐直接使用
LOAD DATA。
小结
SELECT OUTFILE 同效。导入时，比
LOAD DATA 快（默认 8 线程）。4 总结
可以通过数据大小进行选用:
| 导出 | 导入 | 优点 | 推荐度（效率） |
|---|---|---|---|
| mysqldump | source xxx.sql MySQL< xxx.sql | 原生，可远程 | ⭐⭐⭐ 数据量<10G |
| mydumper | myloader | 多线程 | ⭐⭐⭐ 数据量>50G |
| SELECT OUTFILE | LOAD DATA | 最灵活 | ⭐⭐ 数据量<20G |
| Util.dumpTables | Util.loadDump | 原生，多线程 | ⭐⭐⭐ 数据量<50G |
| Util.exportTable | Util.importTable | 原生，单线程 | ⭐ 数据量<20G |
MySQL< 导入时，需要避免数据丢失。WHERE 过滤，mydumper 是最快的。
SELECT OUTFILE 最常用（因为支持自定义导出部分列）。Util.dumpTables 不支持增量到已有数据的表中，因为包含了库表的元数据信息，像 mydumper。Util.exportTable 备份是单线程，导入是多线程，不推荐的原因是导入容易出错（多次导入可解决）。Util.dumpTables，部分备份用
SELECT OUTFILE。
文章来源于爱可生开源社区 ，作者陈伟
