数据迁移经验总结——亿级别多表异构的数据迁移工作_异构数据库之间亿级数据迁移

由于系统改版，最近三个月在做数据迁移工作，由于业务的特殊，基本将数据迁移所能踩的坑都踩了一遍，决定好好做个总结。

迁移类型——新老系统表结构变化较大的历史数据

一、核心问题

1.新老表结构变化极大。新表是以deliver为核心，另外还涉及仓储系统的一张表，订单系统的4张表，并按照新的逻辑映射关系进行迁移。
2.增量数据迁移。在全量数据迁移时必然会有新的数据，这些数据应该实时进行迁移
3.亿级别数据性能、效率的考虑。由于订单业务非常重要，数据迁移带来的qps对数据库的压力非常大，需要不断测试迭代找到一个适合的迁移效率和性能。
4.老数据格式问题。12、13年的数据由于历史悠久，会存在缺数据，数据不全的问题，这些问题都应该在程序中进行容错。
5.核对数据比较困难。由于新表涉及多个旧表且数据量庞大，数据核对比较困难，目前自己写脚本按周并根据一定的逻辑进行数据核对，同时辅以肉眼比对。

二、数据迁移流程

全量同步 java程序

增量同步 otter + 扩展的java程序

历史数据 需同步到 后端backend 和 前端cobar库 

1.历史数据按年份下载成多个文本（1个文本2g左右）

2.多线程读文本数据

3.处理类型主要有insert batch_insert update ，根据类型不同，线程调用不同逻辑方法处理数据并写入新表

三、展开讨论

将上面问题进行展开，并总结其中的技术问题

1.项目架构层次

   该迁移项目采用java开发，仅仅作为迁移，定位轻量级，后端db采用jdbcTemplate , spring管理javabean 分为三层， 一层manager主要对数据流根据类型分发给不同业务逻辑处理 一层业务逻辑——处理映射关系 一层dao。

2.db相关问题

   后台db 是一主多从 主用来接受写请求，从都是读请求。 从库性能远远低于主库

  2.1 调用接口取数据的问题

   刚开始做时，由于想把项目做的很轻，获取老数据采用调接口的形式，调用order接口、wms接口获取就可以获取相应数据。但接口是很脆弱的，经统计http接口每秒只能抗500-1000左右的请求，大数据量下接口很快就成为瓶颈。我测试过一次，1亿数据需要2周时间才能导完，所以必须放弃调用接口，自己读db进行查询。

  2.2 冷热库问题

   历史数据大部分在冷库里，在db查询是需要先查热库再查冷库，并按照相应的逻辑进行查库。

  2.3 batch批量插入 主从延迟问题

      一开始数据处理一条插入一条，这样1亿数据要产生1亿条insert语句。对于主库来说写入是没有影响的，但是从库有很大问题，其性能和主库相差深远，同时在同步binlog上会产生很大的延时，一亿insert意味着一亿次commited 一亿次提交 binlog同步时就会进行1亿次网络传输，从库也会执行1亿次insert。这时候延迟还会产生更大的问题，因为主库还有其他业务数据写入，从库没有实时同步，导致客服查不到订单。

     后来改成多value的insert语句，每次处理一批数据，一个commit提交。这样即提高了insert插入性能，又解决了延迟问题。

     jdbcTemplate改为updateBacth()方法，但发现sql语句还是一个数据一个insert，这是必须让jdbc如何支持batch insert， 需要在jdbc连接加上参数 jdbc:mysql://.....&rewriteBatchedStatements=true ;

    2.4  sql的dead connection

    多线程读数据时，发现有几个线程一直阻塞了，jstack后，发现Mysql报sokcetAvaiable，这个错误一般发生在连接前端cobar时。这是因为jdbc连接没有设置连接超时，要加上connectTime socketTIme，这个是自己应用连接数据库的网络超时时间，如果不设置就会一直等待下去。

      设置参数：&failOverReadOnly=false&connectTimeout=60000&socketTimeout=60000 failOverReadOnly jdbc默认超时重试就变为只读了，必须加上failOverReadOnly参数

   3. 程序级优化

    3.1 全量数据的多线程解决方案

    单进程迁移，1亿数据大概需要1周时间，时间和效率都太低了，所以思考改为多线程。其实改多线程很简单，历史数据是文本形式，只需要多线程去逐一读文本，并发处理就可以了，同时采用countdownlatch保证线程全部完成后在退出主程序。

    3.2 增量数据的多线程解决方案

    otter会顺序的消费binlog，消费过程是很快的，但如果采用单进程模式，瓶颈会卡在我的写入程序上，所以将程序该为多线程。otter每次读取2000个binlog记录，主进程一旦获得满2000个后，再启动多线程去并发处理这些记录。还需要注意问题，有可能同一时间段针对一条记录有多个改动，需要以最后一次改动为准，所以我将线程个数最为桶的个数，按照order_id和桶的余数放入到对应的桶里，同时通的value是一个map （order_id , row对象），这样可以保证每一批处理都是最新的记录。

    3.3 otter的扩展

    以后我会再写一篇博文分析otter，这里先简要介绍一下。otter是阿里的一个开源项目，主要解决异地机房实时数据备份的问题，它的原理就是通过把自己伪装成slave去读取master的binlog然后解析成eventData对象并写入新的表里。它默认情况下仅支持一对一的复制、表的字段映射逻辑非常简单的复制。但阿里的项目就是强大，它给用户提供了一个接口，继承之后就可以按照自己的逻辑去同步数据了。继承类是AbstractEventProcessor，这个类会接受到获取的eventData对象（就是binlog一条内容），这里面有你订阅的表的所有字段值，sql类型等。继承后，在该类的方法里引入自己的同步程序，这样otter订阅的数据就会进入自己的程序了。

   4.数据补救策略

    数据迁移后依然会存在一些问题：

    1.增量过程中，由于网络等原因会造成binlog读取的卡顿，canal会进行重试，但是貌似该时间点上的数据依然会丢失

    2.迁移过程中，有一些时间点的误差导致数据丢失或异常。

    由于新旧表结果相差很大，数据量也太大了，不能简单用一条sql，这样会拖库。所以我写程序一周一周核对数据，主要核对新表有旧表没有的，这些需要删除;旧表有新表没有的，这些需要添加;新表旧表status不一致的这些需要根据旧表更新。

    小结：

        1.性能——在迁移前对mysql性能，数据量大小都没有一定的认识，采用接口获取数据后改用读库多线程，一前一后浪费了半个多月的时间，再做迁移一定要考虑好数据量，迁移时间，表结果，mysql性能。

        2.代码编写——该程序耦合太严重，后期每增加一种操作类型，原代码上都要加很多判断，下次再写必须要面向接口编程，使用继承和工厂方法（参考秒杀项目）;jdbcTample很轻量级，单sql多了后，代码量还是会上来，下次提前做好评估，用mybatis;校验脚本和执行脚本过多，日志打印也过多，下次还是采用log4j可输出多文本，同时脚本书写上要更优雅和简洁。

        3.核对数据——数据迁移是一件脏活累活，这次迁移有一大半时间在进行数据和对，不停写sql，不停看校验数据。以后再有类似的迁移必须要先规划好，怎样进行核对，例如选择几个关键字段做hash。

参考文献：

1.《关于数据迁移的方法、步骤和心得》http://m.blog.csdn.net/blog/baoqiangwang_11109/5492910

2.  Jdbc驱动的rewriteBatchedStatements参数 http://www.cnblogs.com/chenjianjx/archive/2012/08/14/2637914.html

3.  深入理解JDBC的超时设置 http://www.importnew.com/2466.html 

4. otter https://github.com/alibaba/otter