大数据迁移——Python+MySQL

引言

最近，因为课题组更换服务器，有一批数据需要做数据迁移，数据量大约150G-200G，一部分数据存储在原来服务器的MySQL上，另外一部分数据以txt文件存储在硬盘上。现在，我需要将这些数据全部迁移存储在新服务器的MySQL上，方便后期课题的分析处理以及大量的查询需求。

这个看似简单的任务，却给我带来了很多困扰，转存效率极其低下。我查阅资料发现网络上关于这方面的资料很少，因此记录下这次经历，与大家分享。

方法一：数据库复制 ——最糟糕

我们在MySQL数据库中，遇到数据迁移需求时，最简单直接的想法就是直接复制，当表的数据量较小时，这个操作很方便，但是当数据量很大时，这是一个极其错误的操作，原因如下：
1）一旦复制开始，就只能等待，无法知道执行进度；
2）由于表中数据量很大时，整个过程会很耗时，几十分钟到几小时不等；
3）整个过程不可控，遇到网络故障，连接丢失，简直就是灾难。

方法二：数据库转存——最蜗牛

另外，我想到通过MySQL数据库中转存的方式迁移数据。想法就是，先将数据库中的数据转存为中间形式，再在写入到新的数据库中，主要有两种方式：
1）转入转出，先将数据转出为txt文件，再通过load data命令导入到新数据库中。我发现，数据导入很快，8000W条数据导出到txt文件大概20min，但是导入速度非常非常慢，堪比蜗牛速度，我用的Navicat客户端，1s操作几百条条数据。另外，我还有约120G的txt文件数据需要写入数据库！！！
2) SQL文件转存，先将数据转存为SQL文件，再在新数据库中直接运行SQL文件。这个操作也是蜗牛速度，放张图大家感受下绝望。

方法三：数据库备份——最尬速

然后，我查了资料想到了数据库备份再恢复的方法，通过将MySQL数据库中选定的数据库或表备份再恢复进行数据迁移。8000W记录的数据表（8G）备份耗时约30-50min，备份恢复耗时7-8hour，这个速度比之前快了很多，但是一想这才完成了1%，这个速度让人非常尴尬，心态炸裂。

方法四：内存操作Python+MySQL——最火箭

尝试过前3种方法后，我一直在思考，是什么限制了MySQL的写入速度，它的导出速度还是很快的。数据转存时，服务器的CPU，内存和硬盘IO占用都不高，那么多闲置的资源，简直浪费。然后，我就想到内存操作，MySQL的内存操作还是很快的，只是写回硬盘耗时。方案如下：通过python的pandas将文件数据分块读入内存，再通过批量写入操作写入数据库。经过测试，发现效率非常可以，可以达到1s十几万条的写入速度，速度堪比火箭速度。而且，可以根据个人电脑或服务器配置，设定一次性读入内存的数据量大小，并且可以通过代码优化进一步提高速度。

参考博客：
Working efficiently with Large Data in pandas and MySQL (or any other RDBMS)