大型SQL文件导入mysql方案

一. 场景

现有俩个体积较大的单表sql文件,一个为8G,一个为4G,要在一天内完整导入到阿里云的mysql中,需要同时蛮子时间和空间这俩种要求。

二. 思路

搜索了网上一堆的方案,总结了如下几个:
方案一:利用navicat远程导入
方案二:在阿里云ECS安装一个mysql-client,用source方案导入
方案三:购买阿里云DBMS高级版服务,可以导入1G以内ZIP压缩包

三. 尝试

折腾了许久的尝试,终于总结了一下的经验:

3.1 尝试navicat远程导入

操作简单,但是缺点很明显:导入效率低,严重占用本地的IO,影响机器的正常工作,所以立马放弃。

3.2 尝试source方案

3.2.1 实现步骤

STEP1 在测试环境的ECS上安装一个mysql-client
STEP2 修改mysql中的max_allowed_packet参数为10G大小,net_buffer_length参数也根据需求适度调大。
STEP3 因为是俩个表,写俩个脚本太麻烦了,可以利用一个sql脚本聚合实现,所以all.sql 的内容可以如下

source /mydata/sql/a.sql;
source /mydata/sql/b.sql;

STEP4 为避免ssh连接掉线而导致执行关闭,需要写一个shell脚本,通过nohup后台执行。
myshell.sh脚本如下

mysql -h host  -uxxx -pxxx --database=user_database</mydata/sql/all.sql

STEP5 后台执行指令

nohup ./myshell.sh &

3.2.2 结果

测试速度相对快多了,但是由于第二天就需要,所以俩个表接近4000w行的数据绝对不能完成任务,所以方案取消。但是不是否定该方案,其他场景肯定满足。

3.3 尝试DMBS

由于我们数据库是阿里云的RDS,所以我们购买了对应的DBMS服务升级版,可以支持文件上传导入(包含1G内的ZIP)


DBMS功能

3.3.1 压缩文件

压缩单表SQL文件为单独zip文件,压缩下来一个为0.9G,一个为1.2G

3.3.2 拆分文件

第一个sql文件上传后执行很顺利,但是第二个1.2G的zip包需要进行拆分

3.3.3 拆分方案

1.拆分zip压缩包

压缩包拆分

拆分出来的文件,手动改后缀后不满足DBMS的文件规范,失败~
2.高比例压缩文件
利用7z高比例压缩sql为7z后缀,压缩后体积明显小了,只有0.7G的体积,然后通过更改后缀为zip来上传。结果阿里云解析不出这样的格式,失败~
3.使用linux split 命令

split [--help][--version][-<行数>][-b <字节>][-C <字节>][-l <行数>][要切割的文件][输出文件名]

补充说明:
split可将文件切成较小的文件,预设每1000行会切成一个小文件。
-<行数>或-l<行数>  指定每多少行就要切成一个小文件。
-b<字节>  指定每多少字就要切成一个小文件。支持单位:m,k
-C<字节>  与-b参数类似,但切割时尽量维持每行的完整性。

虽然linux可以根据行拆分文件(这也是阿里云工单提供的解决方案),但是这个操作上传上去拆分,在下载下来上传到DBMS,文件体积这么大,来来回回一天过去了,所以放弃~
4.拆分单表sql文件为多份
网上有个SQLDumpSplitter的工具可以拆分表为多份,但是搜索记录中和文章中都是推荐SQLDumpSplitter2的版本,版本太老了,体积较大的sql完全不支持,失败~
但是!我在SQLDumpSplitter2里面看到了软件的官网,发现官网有SQLDumpSplitter3版本,不抱希望的尝试了一下,居然支持大体积文件。成功了!!!
附带下载链接:https://philiplb.de/sqldumpsplitter3/ 太值得推广了。

SQLDumpSplitter3

拆分结果

剩下就是按序上传对应文件即可完成,不得不得夸阿里云这方面做得真的好!
多任务执行

执行效率上面来说,也是可圈可点的:
切片入库的效率

四. 总结

今天针对这个需求,我首先查询了网上的大体方案,然后挑选了几个可执行的方案进行测试。排除了多个方案的情况下,采用了第三方的解决方案来完成这个问题。在阿里云DBMS的支持下,我们又尝试了多种文件的切割方案,最后通过SQLDumpSplitter3+DBMS来实现了,并且效率可观。过程中也发现,常用的client-source方案可以满足自建mysql+效率要求不是极致的场景。
综上,对于大型sql,最好的方案也是先切割(确保顺序性),然后利用一些更高效率的软件执行来实现最终结果,也需要根据时间空间场景灵活选用方案。

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容