从mysql同步数据到clickhouse，实现数据分析的加速处理

提要

• 这事时间有些长久了，快有三年的时间，在这里记录下的我自己对于在线事务处理和在线分析处理的使用感受和迁移路程。

2021年

第一年

• 2021年进入现在的公司，作为当前软件开发公司十多年来的第一个专职运维，开始了长达3个月的业务系统架构梳理；
• 随着在一个个项目的配置文件中查找中间件配置并记录，发现所有业务系统的部署方案都一样，同一类业务所有模块都运行在一台服务器上，为了提高负载能力，有的模块启动了好多个，数据库是sqlserver和mysql，单点运行，当然冷备也没有的。

2022年

第二年

• 公司新上了一套统计分析系统，数据库还是mysql，用的是pxc，三台服务器的最小集群，刚开始数据是从业务系统通过程序同步过来的，后来采用了canal deployer->rocketmq->canal adapter，新数据库中增加了预处理表，项目运行速度还可以，查询压力不大。

2023年 疫情 结束

1. 业务量暴增，mysql最大表从1000万行到现在快3000万行，统计分析系统的数据因为接入了多个业务系统，查询需求增长很多，预处理表也横向（行数据）和纵向（行数）增长，而我们租用的云服务器，磁盘iops却急剧下降，高速盘只有不到200。
2. 这带来的结果是：统计查询的数据无法出结果，在系统里查询会引起卡死，问题提交到研发，也没办法解决，最后只能通过高可用部署、堆内存限制，脚本自检重启的方法来自动拉起服务（jar包启动）。
3. 系统改造，由于数据库慢，公司进行了数据库改造，增加了大量索引，查询速度上升很多，同时磁盘存储也成倍增加，这个也造成了后期的迁移工作困境，这里不提。
   • 改造前期，公司做了很多咨询：

   1. Tidb方案，由于msyql版本兼容和成本问题放弃；
   2. 我推荐的Elasticsearch（使用现有的一套5服务日志集群），研发要同步写入和修改数据，担心一致性难以保证，被我自己否定了该方案；
   3. 有人建议postgresql，sql语句相近，迁移需要在canal deployer->rocktmq的基础上进行开发，领导采纳，研发对canal adapter进行了修改，修改并启用了历史同步方案程序来进行数据验证。

4. 转机， 在系统改造的途中，由于某些原因，公司换了云服务器供应商，进行了机房改造，部分业务迁移到了自建机房。业务系统数据库上了云服务器的高速ssd硬盘，以前无法统计的数据，现在能够查询出来，查询结果的时间有点长，慢查询最高超过100秒（文章最近两天）；统计系统上了物理服务器的RAID6 SSD磁盘（intel 企业 sata ssd 960G 4盘），mysql和postgresql的最大慢查询时间50秒左右（文章最近两天）。

照理说，问题差不多已经解决了吧，我们可能会这么感觉，但是，当你对别人说，我们解决了一个历史遗留的查询慢的问题，终于使业务能正常使用了，以前无法查询的结果，现在一分钟或者几分钟就能查出来，一般人会很惊讶的问：一分钟查出来不慢吗？正常说来，分析系统应该是秒级的响应的，es集群上的nginx访问日志每天有20-30w条，一分片一副本，存储时长一年，单次年度聚合统计的时间根据分片分布的服务器，结果在10秒以内返回。这里还没完

学习

缘由

• 所有业务系统都做了一次迁移，换到了混合云模式，服务器数量大幅缩减，es运行在物理机，和其他业务共用资源，存储业务系统模块运行日志、nginx访问日志，慢查询日志等，单台的es优势不在。
• 这使我想起了以前看过的关于clickhouse的文章，都说单服务的查询速度超过es；而我现在还需要增加日志的采集，采集测试开发环境中的几十个模块日志，因为都是linux系统，后面就按照ilogtail->kafka->python->clickhouse->grafana搭建了一套日志采集查询系统，查询使用sql语句，接入grafana很方便。
• 到这，可能会知道我想做什么了，对，就是用clickhouse来做在线数据分析；将统计数据通过grafana进行显示，这看起来是一个很好的方案。

解决方案

• 说干就干，基础数据通过postgresql导出到csv，再导入clickhouse，然后通过binlog解析同步，这中间最麻烦的是binlog同步，很多文章推荐clickhouse自己的同步方案，不过这在clickhouse官方是一个实验性的引擎（MaterializedMySQL）。后来通过一篇文章，找到了Bifrost这个软件，虽然有些小问题，比如v1正式版很容易出现同步数据无法写入（DDL解析有问题），v2的beta版运行了一段时间自己崩溃退出了，希望作者在后面能增加一个错误自动skip并记录的功能。为了弥补遗漏数据，解决Bifrost同步卡主的问题，我用python写了一个校验并同步的脚本，定时运行，验证数据准确性和同步数据，通过clickhouse的ReplacingMergeTree表引擎，做去重处理，目前的处理方案已经运行了一个多月时间：

  binlog实时同步: mysql -> Bifrost -> clickhouse
  python定时校验：mysql -> python脚本 -> clickhouse

目前就记录到这里吧，很久没有打这么多文字，以前的文章多数是脚本代码，现在终于有了写博客的感觉（不是写技术文档）