mysql的全量导出_如何在下班前全量导出mysql的10亿数据到U盘？

#前情提要

上头的上头一句话，把这一堆日表从今年1月份开始的所有数据导出来，方便审计人员核对。然后把所有数据整合起来，每100w条导出成一个单独的文件，行内容按照csv的文本格式组织。

简简单单几句话，其实里面的坑多的是：

#实现难点

*数据量大:*因为业务的特殊性，这一堆表的数据是可以预先生成的，虽然现在才3月份，但是最新的一张表已经去到了2022年，总计有2000+张日表。其次因为是消费流水数据，所以量大的惊人，通过一个脚本统计出的总数达到了10亿+。

*格式要求:*虽然我也认为这么多的数据是不可能真正一条一条打开来看的，但是领导既然要求导出成csv兼容的文本格式。那如何把mysql里的数据格式化也成了一个难点。

*条件有限:*因为前期根本没有任何工具积累，所有的信息就只告诉你哪些机器可以访问的哪个数据库，其余的一切都得从零开始做。PS. 其实隔壁同事类似的数量级，但是数据在hive上，很快就导完结束下班了。

导出到U盘: 10亿的消费数据放到U盘里。PS.如果不是知道他是真正的需求方，要不然真会怀疑他是不是想通过这些数据做什么坏事...

#踩过的坑坑洼洼

接到任务，不管多难，总得做不是。那就开始各种google百度，首先第一步是如何将数据从mysql导出到硬盘。最直接的想法是通过navicat提供的数据导出工具

但是当我看着2000个表需要一个个勾选，导出到一个个文件到windows，因为对自己的电脑的充分不自信片刻就放弃了这个年头(其实写这篇文章的时候发现是自己不会玩navicat... 事实上完全可以通过navicat直接导。但是技术人员嘛，踩了坑还是希望叨逼叨逼一下涨点姿势)

然后就把思路放到了直接通过linux的一堆shell命令工具实现需求。一堆google，在stackoverflow上找到了一个47赞的问答直接戳中要点！

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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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.csv

mysql -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 &批量执行。

这次终于成功了，所有的数据顺利导出来。虽然现在只是中间数据，但是离胜利也不远了，剩下的就是cat，split。完成！

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

#一个自动化的脚本

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

# coding:utf-8

import os

import datetime

start = 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 = 10

if 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 + 1

step = (total / threadNum) if (total / threadNum) > 1 else 1

shellScript = [[] for i in range(threadNum)]

count = 0

while 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 += 1

count = 0

for 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 += 1

for index in range(count):

bash = "sudo bash ./shell/shell_" + str(index) + ".sh &"

os.popen(bash)

以上。