博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
如何在下班前全量导出mysql的10亿数据到U盘?
阅读量:7122 次
发布时间:2019-06-28

本文共 6456 字,大约阅读时间需要 21 分钟。

  hot3.png

#前情提要 上头的上头一句话,把这一堆日表从今年1月份开始的所有数据导出来,方便审计人员核对。然后把所有数据整合起来,每100w条导出成一个单独的文件,行内容按照csv的文本格式组织。 简简单单几句话,其实里面的坑多的是:

#实现难点

  1. *数据量大:*因为业务的特殊性,这一堆表的数据是可以预先生成的,虽然现在才3月份,但是最新的一张表已经去到了2022年,总计有2000+张日表。其次因为是消费流水数据,所以量大的惊人,通过一个脚本统计出的总数达到了10亿+。
  2. *格式要求:*虽然我也认为这么多的数据是不可能真正一条一条打开来看的,但是领导既然要求导出成csv兼容的文本格式。那如何把mysql里的数据格式化也成了一个难点。
  3. *条件有限:*因为前期根本没有任何工具积累,所有的信息就只告诉你哪些机器可以访问的哪个数据库,其余的一切都得从零开始做。PS. 其实隔壁同事类似的数量级,但是数据在hive上,很快就导完结束下班了。
  4. 导出到U盘: 10亿的消费数据放到U盘里。PS.如果不是知道他是真正的需求方,要不然真会怀疑他是不是想通过这些数据做什么坏事...

#踩过的坑坑洼洼 接到任务,不管多难,总得做不是。那就开始各种google百度,首先第一步是如何将数据从mysql导出到硬盘。最直接的想法是通过navicat提供的数据导出工具 输入图片说明 但是当我看着2000个表需要一个个勾选,导出到一个个文件到windows,因为对自己的电脑的充分不自信片刻就放弃了这个年头(其实写这篇文章的时候发现是自己不会玩navicat... 事实上完全可以通过navicat直接导。但是技术人员嘛,踩了坑还是希望叨逼叨逼一下涨点姿势) 然后就把思路放到了直接通过linux的一堆shell命令工具实现需求。一堆google,在直接戳中要点!

mysql -B -u username -p password database -h dbhost -e "SELECT * FROM accounts;" \ | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

一句命令就把结果直接计算出来了。看到这个答案的时候,我心里实在是激动啊。 而事实上也主要因为自己以前对直接命令行操作mysql不熟悉,事后分析最主要的命令参数其实就一个 -e。execute命令。然后的想法很自然就是跑2000+遍这样的语句,通过管道把结果导出来。

#... ...mysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221024"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221025"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221026"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221027"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221028"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221029"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221030"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221031"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221101"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221102"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221103"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221104"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221105"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221106"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csvmysql -B -N -uusername -ppassword -hyourhost -Pyourport database -e "select column1, column2 ... from table_20221107"   | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" >> tmp.csv

这样就可以把所有的文件导到同一个tmp.csv文件,最后再用一遍split命令按照行把tmp.csv切割成100w行一个的文件就ok了。 然后就开始跑。 结果发现跑了一段时间发现端口配错了,跑到一半的脚本莫名其妙退出。修改好端口之后打算继续跑,发现前面用的是>>管道,这就意味着批量操作中append可能会污染前面已生成的数据,几个G的数据又要重新来了。。。 所以这里就来了两个总结: ##不要用>>命令批量导出,要用>,才能防止重跑数据污染 ##在不确定最终结果是否稳定的情况下,一定要保存中间过度数据 比如上面所有数据都保存到tmp.csv上就不是中间数据,因为所有数据都在一个文件中,要么完成,要么失败,损失的是时间。。。正确的做法是把每个日表导出到对应的一个文件中,最后再通过cat *.csv > final.csv合并起来。这样即使中间有数据出错,一方面好校验,一方面不至于全盘重新开始。 删完数据,继续启动。数据跑正常了,通过wc -l看一下导出数据的速度,平均每秒10000行写入到磁盘。然后计算一下时间发现竟然要四个多小时.... 当时已经是晚上8点了,是打算通宵的节奏么?不行不行,这样的策略太慢。第三个惨痛的领悟: ##导大量数据一定要并行! 继续改,把上面一整个大堆的串行命令切割成n份shell_n.sh,通过nohup shell_n.sh &批量执行。 这次终于成功了,所有的数据顺利导出来。虽然现在只是中间数据,但是离胜利也不远了,剩下的就是catsplit。完成!

本次特殊情况时间紧,没来得及细想执行方案,中间遇到很多个小问题卡了不少时间。而且因为最早没有中间数据,每次卡顿都得重新开始。最后搞到1点多才走,但是也收获不少经验上的东西。

#一个自动化的脚本 最后,为了防止类似事情再度发生,这里准备了一个自动化的脚本。比较简单,只需要修改一些主要的配置就可以自动完成以上所有功能。

# coding:utf-8import osimport datetimestart = datetime.datetime(2017, 1, 1)end = datetime.datetime(2017, 12, 31)host = ""port = ""user = ""password = ""database = ""columns = "*"tableName = ""condition = "create_time<='2016-12-31 23:59:59'"threadNum = 10if not os.path.exists("shell"):    os.mkdir("shell")if not os.path.exists("data"):    os.mkdir("data")preSql = "SELECT ${columns} FROM ${tableName}_${time} WHERE ${condition}"preSql = preSql.replace("${columns}", columns)preSql = preSql.replace("${tableName}", tableName)preSql = preSql.replace("${condition}", condition)preShell = "mysql -B -C -N -u${user} -p${password} -h${host} -P${port} ${database} -e \"${sql}\" | sed \"s/'/\\'/;s/\\t/\\\",\\\"/g;s/^/\\\"/;s/$/\\\"/;s/\\n//g\"  > ./data/tmp_${time}.csv\n"preShell = preShell.replace("${user}", user)preShell = preShell.replace("${password}", password)preShell = preShell.replace("${host}", host)preShell = preShell.replace("${port}", port)preShell = preShell.replace("${database}", database)preShell = preShell.replace("${sql}", preSql)total = (end - start).days + 1step = (total / threadNum) if (total / threadNum) > 1 else 1shellScript = [[] for i in range(threadNum)]count = 0while count < total:    for j in range(threadNum):        if count < total:            current = start + datetime.timedelta(days=count)            currentStr = current.strftime("%Y%m%d")            shell = preShell.replace("${time}", currentStr)            shellScript[j].append(shell)            count += 1count = 0for section in shellScript:    combine = ""    for s in section:        combine += s    with open("./shell/shell_" + str(count) + ".sh", "w") as final_shell:        final_shell.write(combine)    count += 1for index in range(count):    bash = "sudo bash ./shell/shell_" + str(index) + ".sh &"    os.popen(bash)

以上。

转载于:https://my.oschina.net/djzhu/blog/856152

你可能感兴趣的文章