ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 技术分享 | MySQL 的几种数据迁移方案

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

MySQL 作者：Linksla 时间：2024-02-05 09:54:27 0 删除 编辑

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 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 的备份文件，测试结果如下：

  1. 使用  mysql< xxx.sql 导入，耗时 5 分钟。
  2. 使用用  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 线程）。

注意：

  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", 
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。
  • 测试之后再使用，导出和导入均需要进行数据验证。


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


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70013542/viewspace-3006295/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 企业数字化转型过程中，IT运维发挥着怎样的价值？
下一篇： Linux Shell神器：解决你不会写脚本的烦恼
请登录后发表评论 登录
全部评论
Linksla

注册时间：2022-02-09

  • 博文量
    416
  • 访问量
    280300

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593