您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

百万级MySQL的数据量,该如何快速的完成数据迁移?

时间:2020-07-18 12:51:24  来源:  作者:

背景

上个月跟朋友一起做了个微信小程序,趁着5.20节日的热度,两个礼拜内迅速积累了一百多万用户,我们在小程序页面增加了收集formid的埋点,用于给微信用户发送模板消息通知。

这个小程序一开始的后端逻辑是用douchat框架写的,使用框架自带的dc_mp_fans表存储微信端授权登录的用户信息,使用dc_mp_tempmsg表存储formid。截止到目前,收集到的数据超过380万,很大一部分formid都已经成功使用给用户发送过模板通知,起到了较好的二次推广的效果。

随着数据量的增大,之前使用的服务器空间开始有点不够用,最近新写了一个专门用于做小程序后台开发的框架,于是想把原来的数据迁移到新系统的数据库。买了一台4核8G的机器,开始做数据迁移。下面对迁移过程做一个简单的记录。关注公众号互联网架构师,回复关键字2T,获取最新架构视频

百万级MySQL的数据量,该如何快速的完成数据迁移?

 

方案选择

平常开发中,我们比较经常使用的数据备份迁移方式是用mysqldump工具导出一个sql文件,再在新数据库中导入sql来完成数据迁移。试验发现,通过mysqldump导出百万级量的数据库成一个sql文件,大概耗时几分钟,导出的sql文件大小在1G左右,然后再把这个1G的sql文件通过scp命令复制到另一台服务器,大概也需要耗时几分钟。在新服务器的数据库中通过source命令来导入数据,我跑了一晚上都没有把数据导入进来,cpu跑满。

  • 脚本迁移

直接通过命令行操作数据库进行数据的导出和导入是比较便捷的方式,但是数据量较大的情况下往往会比较耗时,对服务器性能要求也比较高。如果对数据迁移时间要求不是很高,可以尝试写脚本来迁移数据。虽然没有实际尝试,但是我想过大概有两种脚本方案。

第一种方式,在迁移目标服务器跑一个迁移脚本,远程连接源数据服务器的数据库,通过设置查询条件,分块读取源数据,并在读取完之后写入目标数据库。这种迁移方式效率可能会比较低,数据导出和导入相当于是一个同步的过程,需要等到读取完了才能写入。如果查询条件设计得合理,也可以通过多线程的方式启动多个迁移脚本,达到并行迁移的效果。

第二种方式,可以结合redis搭建一个“生产+消费”的迁移方案。源数据服务器可以作为数据生产者,在源数据服务器上跑一个多线程脚本,并行读取数据库里面的数据,并把数据写入到redis队列。目标服务器作为一个消费者,在目标服务器上也跑一个多线程脚本,远程连接redis,并行读取redis队列里面的数据,并把读取到的数据写入到目标数据库。这种方式相对于第一种方式,是一种异步方案,数据导入和数据导出可以同时进行,通过redis做数据的中转站,效率会有较大的提升。关注公众号互联网架构师,回复关键字2T,获取最新架构视频

可以使用Go语言来写迁移脚本,利用其原生的并发特性,可以达到并行迁移数据的目的,提升迁移效率。

  • 文件迁移

第一种迁移方案效率太低,第二种迁移方案编码代价较高,通过对比和在网上找的资料分析,我最终选择了通过mysql的select data into outfile file.txt、load data infile file.txt into table的命令,以导入导出文件的形式完成了百万级数据的迁移。

迁移过程

在源数据库中导出数据文件

  select * from dc_mp_fans into outfile '/data/fans.txt';    

复制数据文件到目标服务器

zip fans.zip /data/fans.txt
scp fans.zip root@ip:/data/    

在目标数据库导入文件

unzip /data/fans.zip
load data infile '/data/fans.txt' into table wxa_fans(id,Appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@d

按照这么几个步骤操作,几分钟内就完成了一个百万级数据表的跨服务器迁移工作。

注意项

mysql安全项设置

在mysql执行load data infile和into outfile命令都需要在mysql开启了secure_file_priv选项, 可以通过show global variables like '%secure%';查看mysql是否开启了此选项,默认值Null标识不允许执行导入导出命令。通过vim /etc/my.cnf修改mysql配置项,将secure_file_priv的值设置为空:

[mysqld]
  secure_file_priv=''

则可通过命令导入导出数据文件。

导入导出的数据表字段不对应

上面示例的从源数据库的dc_mp_fans表迁移数据到目标数据库的wxa_fans表,两个数据表的字段分别为:- dc_mp_fans

百万级MySQL的数据量,该如何快速的完成数据迁移?

 

wxa_fans

百万级MySQL的数据量,该如何快速的完成数据迁移?

 

在导入数据的时候,可以通过设置字段名来匹配目标字段的数据,可以通过@dummy丢弃掉不需要的目标字段数据。

总结

结合本次数据迁移经历,总结起来就是:小数据量可以使用mysqldump命令进行导入导出,这种方式简单便捷。- 数据量较大,且有足够的迁移耐心时,可以选择自己写脚本,选择合适的并行方案迁移数据,这种方式编码成本较高。- 数据量较大,且希望能在短时间内完成数据迁移时,可以通过mysql导入导出文件的方式来迁移,这种方式效率较高。



Tags:MySQL 数据迁移   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
背景上个月跟朋友一起做了个微信小程序,趁着5.20节日的热度,两个礼拜内迅速积累了一百多万用户,我们在小程序页面增加了收集formid的埋点,用于给微信用户发送模板消息通知。这个...【详细内容】
2020-07-18  Tags: MySQL 数据迁移  点击:(152)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条