四川网站建设培训学校,使用element做的网站,网站服务器内网打不开网页,做网站app怎么赚钱吗1需求背景
应用侧的同学需要对数据进行导出和导入#xff0c;于是跑来找 DBA 咨询问题#xff1a;MySQL 如何导入大批量的数据#xff1f;
应用侧目前的方式#xff1a; mysqldump 工具 select outfile 语句 图形化管理工具#xff08;MySQL Workbench、Navicat 、DBe…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)
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COMMENT订单表导出文件 包含数据结构和数据的 备份文件 mysqldump、mydumper、Util.dumpTables 只包含数据的 数据文件 select outfile、Util.exportTable
导出导入命令
导出导入mysqldumpsource 或 mysql xxx.sqlmydumpermyloaderselect outfileload dataUtil.dumpTablesUtil.loadDumpUtil.exportTableUtil.importTable
3方案测试
测试首先考虑的是 提升导入效率并新增了 MySQL Shell 的使用。
mysqldump
单表导出备份文件
mysqldump --default-character-setutf8mb4 --master-data2 --single-transaction --set-gtid-purgedoff --hex-blob --tables test t_order_info--master-data2 参数会在备份期间对所有表加锁 FLUSH TABLES WITH READ LOCK并执行 SHOW MASTER STATUS 语句以获取二进制日志信息。因此在备份期间可能会影响数据库的并发性能。如果您不需要进行主从复制则可以考虑不使用 --master-data2 参数。 --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)
) ENGINEInnODB AUTO_INCREMENT10129913 DEFAULT CHARSETutf8m 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 语句中包含 列名称新表的列有增加的时候。
单表导出备份数据只导出数据。
mysqldump --default-character-setutf8mb4 --master-data2 --single-transaction --set-gtid-purgedoff --hex-blob --no-create-info --skip-add-drop-table --skip-add-locks --tables dbname tablename// 部分数据导出追加参数
--wherecreate_time2023-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-setutf8mb4 --single-transaction --set-gtid-purgedOFF --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-purgedOFF --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 --verbose3 --compress --no-schemas --rows1000000 -T test.t_order_info -o /backup// 导出时支持部分导出追加参数--wherecreate_time2023-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 --verbose3 -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 --verbose3 --wherecreate_time2023-01-02 --no-schemas --rows1000000 --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_time2023-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_time2023-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总结
可以通过数据大小进行选用:
导出导入优点推荐度效率mysqldumpsource xxx.sql MySQL xxx.sql原生可远程⭐⭐⭐ 数据量10Gmydumpermyloader多线程⭐⭐⭐ 数据量50GSELECT OUTFILELOAD DATA最灵活⭐⭐ 数据量20GUtil.dumpTablesUtil.loadDump原生多线程⭐⭐⭐ 数据量50GUtil.exportTableUtil.importTable原生单线程⭐ 数据量20G MySQL 导入时需要避免数据丢失。 前 3 种都支持 WHERE 过滤mydumper 是最快的。SELECT OUTFILE 最常用因为支持自定义导出部分列。 前 2 种因为是备份工具所以有 FTWRL 锁。 Util.dumpTables 不支持增量到已有数据的表中因为包含了库表的元数据信息像 mydumper。 Util.exportTable 备份是单线程导入是多线程不推荐的原因是导入容易出错多次导入可解决。 使用建议按照数据量选择全表备份最快用 Util.dumpTables部分备份用 SELECT OUTFILE。 测试之后再使用导出和导入均需要进行数据验证。
文章来源于爱可生开源社区 作者陈伟
