干货好文 | 初探MySQL迁移到ClickHouse

前 言

ClickHouse无疑是目前最火的OLAP解决方案，笔者所在的运维团队，ClickHouse的数量近两年增长迅猛。

最近老板安排了一个任务，要求我调研现在MySQL到ClickHouse的同步工具，方便后面将线上的在线OLTP数据实时同步给线下的OLAP ClickHouse，这样业务人员和运营人员基于它进行统计查询更直观快速。

目前，市面上的相关工具和产品，主要分为三类：

1 ClickHouse原生提供的MySQL同步工具

2 命令行式的MySQL to ClickHouse同步工具

3 界面引导式的MySQL to ClickHouse同步工具

01 ClickHouse原生同步工具

ClickHouse 官方提供了MaterializedMySQL的同步复制工具，它会获取MySQL的binlog，并重放DDL和DML。但是这个特性没有release，官方文档用醒目的字体显示“This is an experimental feature that should not be used in production.”不能用在生产环境中，并且看代码也有一段时间没有更新了。

测试结果如下：

• 在MySQL上创建一个表。

mysql> create database db1;
mysql> create table test1 (a INT PRIMARY KEY, b INT);

• 在ClickHouse上新建MaterializeMySQL的表。

## 这里要专门设置以便clickhouse能使用这个特性
ck> set allow_experimental_database_materialize_mysql=1;   


## 这里创建连接到user@192.168.1.17:3306/db1的database连接，命名为db1_ck
ck> CREATE DATABASE db1_ck ENGINE = MaterializeMySQL('192.168.1.17:3306', 'db1', 'user', '***');


## 进入db1_ck这个数据库
ck> use db1_ck;


## 查看db1_ck库下的表，可以看到test1这个表
ck> show tables;

• 查看test1这个表在ck上的表结构。

ck> show create table test1;


CREATE TABLE db1_ck.test1
(
    `a` Int32,
    `b` Nullable(Int32),
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1,
    INDEX _version _version TYPE minmax GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(a, 4294967)
ORDER BY tuple(a)
SETTINGS index_granularity = 8192

test1表使用的是ReplacingMergeTree引擎，表结构新增了两个字段_sign和_version作为隐藏字段，用于标识该行是否删除和版本号。这样将MySQL的update和delete统一转换成insert，充分利用ClickHouse快速导入数据、update和delete慢且容易出问题的特性。内部实现原理的细节和好处，我们将在另外一篇文章里面介绍。

• 在MySQL上操作，看数据会不会同步到ClickHouse上。

mysql> insert into test1 values (1,11),(2,22),(3,33);
mysql> update test1 set b=77 where a=1;
mysql> delete from test1 where a=2;


ck> select * from db1_ck.test1;
┌─a─┬──b─┐
│ 1 │ 77 │
│ 3 │ 33 │
└───┴────┘

可以看到，数据基本都正确地同步到ClickHouse上去了。

02 命令行式同步工具

这一类是利用命令行来实现MySQL同步到ClickHouse的工具，例如Altinity的clickhouse-mysql-data-reader工具，这个工具也可以实现DML的数据同步。但是安装和命令行使用太麻烦，而且这个工具2020年就不再更新了，所以此次没有进一步深入研究。

03  界面引导式工具——以DBMotion为例

有独立的web界面来实现MySQL同步到ClickHouse，用户体验更方便。类似的国内产品有很多，从简单性和稳定性来说，个人偏向于DBMotion。下面就以DBMotion的线上版本为例，介绍一下界面引导式MySQL to ClickHouse的迁移。

迁移任务配置和查看

• 首先，进入DBMotion的页面，点击免费使用。
  ​​https://squids.cn/product/dbmotion​​

• 登录DBMotion的任务列表页面。
  ​​https://console.squids.cn/console/transfer​​

• 单击“添加任务”按钮，进入“源端目标库配置”。
  为方便展示，这里选择的是在公网ECS上创建的MySQL和ClickHouse。

注意：这里的源库与目标库的用户、权限，建议按照授权语句的要求，在源库和目标库新建用户，否则后面的预检查和迁移有可能会失败。迁移完成后，就可以将源端和目标端的用户删除。 

• 点击“测试连接以进行下一步”，进入“迁移选项配置”页。
  这里可以展开选择你要同步的表，选择并发数和迁移哪些MySQL的表到目标端。 

• 最后，用户确认配置并进行预检查。
  这一步可以看到用户选择的是哪些内容，而且预检查会提前发现一些可能导致迁移失败的问题 。

这个确认页面内容丰富，涵盖了源库、目标库和要迁移的对象的信息。还会主动预检查所有可能导致迁移失败的点，出现问题时会发出提醒，并给出修复建议，是一个比较方便的功能。

• 能看到新建任务成功，点击任务就可以进入任务详情页面 。

• 在任务配置页面，可以看到这次迁移同步任务的源库、目标库和迁移对象信息，之前的预检查信息也可以在这里回顾。

迁移详情主要包括“对象迁移”、“全量迁移”、“增量迁移”和“数据校验”。

• 对象迁移。
  ​以这两个测试表为例，将对象同步过去的速度是比较快的。对象迁移，DBMotion具体同步的内容，我们看目标库上的表和数据的时候可以看到。

• 全量迁移。
  ​两个表的数据不多，同步的速度较快。

这里每个步骤都有迁移进度、迁移速度、已迁移时间、预计剩余时间，对于迁移的进展和大概需要多少时间，一目了然。

• 增量迁移。
  记录了同步的binlog位点信息。

• 数据校验。
  这个功能是其他相关产品没有的功能，会校验MySQL和ClickHouse的表和数据。对于需要精确匹配的迁移来说，是一个很实用的功能。

• 结束迁移。
  结束迁移是为了关闭增量，保证迁移完成以后，就可以结束迁移了。

• 任务日志。
  在迁移报错的时候，用来检查到底是哪个环节出现了问题。

总体来看，这个操作是比较简单的。只需要配置好源端、目标端和迁移对象，它就会帮你创建好迁移任务。任务展示页面也比较清晰明了，同步了哪些内容，数据是否一致都有比较好的展现。

实际迁移结果

接下来，我们来看它的实际迁移结果。

全量迁移

• 源库表结构。
  源库上test_grant1.test1表的表结构如下：

• 目标库结构。
  迁移完成后，在目标库里看，DBMotion新建了两个schema，test_grant1和test_grant1_ck，并分别在这两个schema下新建了test1_ck表和test1表，对应的表结构如下：

这一步，DBMotion应该是参考ClickHouse原生解决方案MaterializedMySQL来实现的。解释一下：

• test_grant1_ck.test1_ck是ReplacingMergeTree的表，除了a、b两列以外，还有__version@@和__event_type@@，对应version和sign两个字段。
• 为了方便用户使用，在目标库上专门新建了test_grant1.test1的视图，让用户查询的时候，看到的数据和源库一致。

增量迁移

我们测试了一下在源库上做增删改的操作，在目标库上查看同步效果，发现同步延迟基本在秒级别，同步的数据也完全一致。

• 源库增删改查。

• 目标库查看数据。

这里可以明显看到，test_grant1_ck.test1_ck中记录的是源库做的所有DML操作，通过视图合并后，test_grant1.test1查询出来的数据跟源库一模一样。

PS：B站上有一个DBMotion的介绍视频，不喜欢看文字的，可以看这个视频了解一下它的安装和使用方式。

总结

• MySQL to Clickhouse，目前有ClickHouse原生提供的同步工具、命令行同步工具和界面指引型同步工具。原生的工具暂时不能用在生产环境，命令行工具使用起来比较繁琐，界面型相对简单易用。
• 界面型同步工具中，DBMotion相对比较直观简单。
• 这些工具对DDL、无主键表都有一些限制，这些问题我将在另外一篇文章里面详细描述。