1需求背景
应用侧的同学需要对数据进行导出和导入，于是跑来找 DBA 咨询问题：MySQL 如何导入大批量的数据？
应用侧目前的方式：
-
mysqldump 工具
-
select outfile 语句
-
图形化管理工具（MySQL Workbench、Navicat 、DBeaver）
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、mydumper、Util.dumpTables）
-
只包含数据的 数据文件 （select outfile、Util.exportTable）
导出导入命令
| 导出 | 导入 |
|---|---|
| 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 TABLEt_order_infoENABLE KEYS;
用途，可以将备份文件中的数据导入自定义库，“文件内容解释”部分遇到的问题可以使用下面参数解决。
-
--no-create-info不包含建表语句（可以手动创建create table tablename like dbname.tablename;） -
--skip-add-drop-database不包含删库语句 -
--skip-add-drop-table不包含删表语句 -
--skip-add-locksINSERT 语句前不包含LOCK TABLES t_order_info WRITE; -
--complete-insertINSERT 语句中包含 列名称（新表的列有增加的时候）。
单表导出备份数据（只导出数据）。
mysqldump --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
-
版本 0.14.4
多线程导出
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 分钟，建议如下：
-
在数据量大于 50G 的场景中，更推荐 mydumper。
-
补充场景，支持导出 CSV，也支持
--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';
小结
-
支持跨表导入。A 表的数据可以导入 B 表，因为备份文件中只有数据。
-
可自定义导出部分列，导出导入速度较快，最常用。
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"})
小结
-
支持跨库导入，A 库的数据可以导入 B 库。表名需要一致。不支持增量到已有数据的表中。
-
导出时和
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。
小结
-
支持跨库导入，A 库的数据可以导入 B 库，表名需要一致。
-
导出时和
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<导入时，需要避免数据丢失。 -
前 3 种都支持
WHERE过滤，mydumper 是最快的。SELECT OUTFILE最常用（因为支持自定义导出部分列）。 -
前 2 种因为是备份工具，所以有 FTWRL 锁。
-
Util.dumpTables不支持增量到已有数据的表中，因为包含了库表的元数据信息，像 mydumper。 -
Util.exportTable备份是单线程，导入是多线程，不推荐的原因是导入容易出错（多次导入可解决）。 -
使用建议：按照数据量选择，全表备份最快用
Util.dumpTables，部分备份用SELECT OUTFILE。 -
测试之后再使用，导出和导入均需要进行数据验证。
文章来源于爱可生开源社区 ，作者陈伟
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
