当前位置:   article > 正文

技术分享 | MySQL 的几种数据迁移方案_mysql数据迁移

mysql数据迁移

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

建表语句

  1. CREATE TABLE `t_order_info` (
  2.   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  3.   `order_no` varchar(64NOT NULL DEFAULT '0000' COMMENT '订单编号',
  4.   `order_status` varchar(2NOT NULL DEFAULT '01' COMMENT '订单状态: 00-异常、01-待处理、02-进行中、03-已完成',
  5.   `flag` tinyint(4NOT NULL DEFAULT '1' COMMENT '删除标识: 1-正常、0-逻辑删除',
  6.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  7.   `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  8.   PRIMARY KEY (`ID`),
  9.   UNIQUE KEY `IDX_ORDER_NO` (`order_no`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='订单表'

导出文件

  • 包含数据结构和数据的 备份文件 (mysqldump、mydumper、Util.dumpTables)

  • 只包含数据的 数据文件 (select outfile、Util.exportTable)

导出导入命令

导出导入
mysqldumpsource 或 mysql< xxx.sql
mydumpermyloader
select outfileload data
Util.dumpTablesUtil.loadDump
Util.exportTableUtil.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 参数用于在备份期间“使用事务来确保数据一致性”,从而避免在备份期间锁定表。[必须有]

备份文件

文件内容。

  1. -- Table stricture for table `t_order_info`
  2. --
  3. DROP TABLE IF EXISTS `t_order_info`;
  4. /*!40101 SET @saved_cs_client= @@character_set_client */;
  5. /*!49101 SET character_set_client = utf8 */;
  6. CREATE TABLE `t_order_info` (
  7.   `ID` bigint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  8.   `order_no` varchar(64NOT NULL DEFAULT `0000` COMMENT '订单编号'
  9.   `order_status` varchar(2NOT NULL DEFAULT '01' COMMENT '订单状态: 80-异常、81-待处理、2-进行中、03-已完成',
  10.   `flag` tinyint(4NOT NULL DEFAULT '1' COMMENT '删除标识: 1-正常、0-逻辑删除',
  11.   `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  12.   `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  13.   PRIMARY KEY (`ID`),
  14.   UNIOUE KEY `IDX_ORDER_NO` (`order no`)
  15. ) ENGINE=InnODB AUTO_INCREMENT=10129913 DEFAULT CHARSET=utf8m COMMENT='订单表';
  16. /*!40101 SET character_set_client = @saved_cs_client */;
  17. --
  18. -- Dumping data for table `t_order_info`
  19. --
  20. LOCK TABLES `t_order_info` WRITE;
  21. /*!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 语句中包含 列名称(新表的列有增加的时候)。

单表导出备份数据(只导出数据)。

  1. 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
  2. // 部分数据导出追加参数
  3. --where="create_time>'2023-01-02'"

导出单库中的某表为 CSV。

  1. // 可选不导出表结构,
  2. --no-create-info --skip-add-drop-database --skip-add-drop-table
  3. /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
  4. //其中 test 后面也可以指定表名,不指定就是全库。
  5. test t_order_info t_order_info01
  6. 其中 --single-transaction --set-gtid-purged=OFF --triggers --routines --events --hex-blob
  7. 为了防止提示,可选

小结

1G 的备份文件,测试结果如下:

  1. 使用 mysql< xxx.sql 导入,耗时 5 分钟。

  2. 使用用 source xxx.sql 导入, 耗时 10 分钟。

推荐第一种,都是单线程。

mydumper

  • 版本 0.14.4

多线程导出

  1. 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
  2.   
  3. // 导出时支持部分导出追加参数
  4. --where="create_time>'2023-01-02'"
  5.   
  6. // 文件输出
  7. test01.t_order_info.00000.dat # 包含 CSV 数据
  8. test01.t_order_info.00000.sql # 包含 LOAD DATA 语句
  9. // 导入命令
  10. 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`);
  • 多线程导入

  1. myloader -u admin -p 123456 -P 3306 -h 127.0.0.1 --enable-binlog -t 8 --verbose=3 -B test -d /backup
  2.   
  3. //  导入主库时需要添加 
  4. --enable-binlog
  5. // 库名可以自定义
  6. -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 秒。

  1. 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';
  2.   
  3. //  带列名导出,导入时需添加 IGNORE 1 LINES;
  4. 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 分钟。

  1. mysql -uadmin -P3306  -h127.0.0.1 -p123456  --local-infile
  2. 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 线程)。

注意:

  1. 部分导出功能需要较新的 MySQL Shell 版本,如 8.0.33。

  2. LOAD DATA 单线程导入 耗时 1h20min。

MySQL_Shell > exportTable

单表导出,耗时 10 秒。

util.exportTable("test.t_order_info",   "/backup/t_order_info.csv", {defaultCharacterSet"utf8mb4", fieldsOptionallyEnclosedtrue, fieldsTerminatedBy",", linesTerminatedBy"\n", fieldsEnclosedBy'"', defaultCharacterSet"utf8mb4", showProgresstrue, dialect"csv"}) 

部分导出。

util.exportTable("test.t_order_info",   "/backup/t_order_info.csv",   {     dialect"csv",     defaultCharacterSet"utf8mb4",     fieldsOptionallyEnclosedtrue,     fieldsTerminatedBy",",     linesTerminatedBy"\n",     fieldsEnclosedBy'"',     showProgresstrue,     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总结

可以通过数据大小进行选用:

导出导入优点推荐度(效率)
mysqldumpsource xxx.sql
MySQL< xxx.sql
原生,可远程⭐⭐⭐
数据量<10G
mydumpermyloader多线程⭐⭐⭐
数据量>50G
SELECT OUTFILELOAD  DATA最灵活⭐⭐
数据量<20G
Util.dumpTablesUtil.loadDump原生,多线程⭐⭐⭐
数据量<50G
Util.exportTableUtil.importTable原生,单线程
数据量<20G
  • MySQL< 导入时,需要避免数据丢失。

  • 前 3 种都支持 WHERE 过滤,mydumper 是最快的。SELECT OUTFILE 最常用(因为支持自定义导出部分列)。

  • 前 2 种因为是备份工具,所以有 FTWRL 锁。

  • Util.dumpTables 不支持增量到已有数据的表中,因为包含了库表的元数据信息,像 mydumper。

  • Util.exportTable 备份是单线程,导入是多线程,不推荐的原因是导入容易出错(多次导入可解决)。

  • 使用建议:按照数据量选择,全表备份最快用 Util.dumpTables,部分备份用 SELECT OUTFILE

  • 测试之后再使用,导出和导入均需要进行数据验证。

文章来源于爱可生开源社区 ,作者陈伟

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/知新_RL/article/detail/647854
推荐阅读
相关标签
  

闽ICP备14008679号