从MySQL到ClickHouse实时复制与实现
很多人看到标题还以为自己走错了夜场，其实没有。
clickhouse 可以挂载为 mysql 的一个从库 ，先全量再增量的实时同步 mysql 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 oltp 和 olap 的融合从此不再头疼。
目前支持 mysql 5.6/5.7/8.0 版本，兼容 delete/update 语句，及大部分常用的 ddl 操作。
代码还处于 alpha 版本阶段，毕竟是两个异构生态的融合，仍然有不少的工作要做，同时也期待着社区用户的反馈，以加速迭代。
代码获取
由于还在验收阶段，我们只好把 github 上的 pull request 代码 pull 到本地。
git fetch origin pull/10851/head:mysql_replica_experiment
开始编译…
mysql master
我们需要一个开启 binlog 的 mysql 作为 master:
docker run -d -e mysql_root_password=123 mysql:5.7 mysqld --datadir=/var/lib/mysql --server-id=1 --log-bin=/var/lib/mysql/mysql-bin.log --gtid-mode=on --enforce-gtid-consistency
创建数据库和表，并写入数据：
mysql> create database ckdb;mysql> use ckdb;mysql> create table t1(a int not null primary key, b int);mysql> insert into t1 values(1,1),(2,2);mysql> select * from t1;+---+------+| a | b |+---+------+| 1 | 1 || 2 | 2 |+---+------+2 rows in set (0.00 sec)
clickhouse slave
目前以 database 为单位进行复制，不同的 database 可以来自不同的 mysql master，这样就可以实现多个 mysql 源数据同步到一个 clickhouse 做 olap 分析功能。
创建一个复制通道：
clickhouse :) create database ckdb engine = materializemysql('172.17.0.2:3306', 'ckdb', 'root', '123');clickhouse :) use ckdb;clickhouse :) show tables;┌─name─┐│ t1 │└──────┘clickhouse :) select * from t1;┌─a─┬─b─┐│ 1 │ 1 │└───┴───┘┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘2 rows in set. elapsed: 0.017 sec.
看下 clickhouse 的同步位点：
cat ckdatas/metadata/ckdb/.metadata
version:1binlog file:mysql-bin.000001binlog position:913data version:0
delete
首先在 mysql master 上执行一个删除操作：
mysql> delete from t1 where a=1;query ok, 1 row affected (0.01 sec)
然后在 clickhouse slave 侧查看记录：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘1 rows in set. elapsed: 0.032 sec.
此时的 metadata 里 data version 已经递增到 2：
cat ckdatas/metadata/ckdb/.metadataversion:1binlog file:mysql-bin.000001binlog position:1171data version:2
update
mysql master：
mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 2 |+---+------+1 row in set (0.00 sec)mysql> update t1 set b=b+1;mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 3 |+---+------+1 row in set (0.00 sec)
clickhouse slave：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘1 rows in set. elapsed: 0.023 sec.
实现机制
在探讨机制之前，首先需要了解下 mysql 的 binlog event ，主要有以下几种类型：
1. mysql_query_event-- ddl2. mysql_write_rows_event -- insert数据3. mysql_update_rows_event -- update数据4. mysql_delete_rows_event -- delete数据
当一个事务提交后，mysql 会把执行的 sql 处理成相应的 binlog event，并持久化到 binlog 文件。
binlog 是 mysql 对外输出的重要途径，只要你实现 mysql replication protocol，就可以流式的消费mysql 生产的 binlog event，具体协议见 replication protocol。
由于历史原因，协议繁琐而诡异，这不是本文重点。
对于 clickhouse 消费 mysql binlog 来说，主要有以下３个难点：
ddl 兼容
delete/update 支持
query 过滤
ddl
ddl 兼容花费了大量的代码去实现。
首先，我们看看 mysql 的表复制到 clickhouse 后会变成什么样子。
mysql master：
mysql> show create table t1g;*************************** 1. row *************************** table: t1create table: create table `t1` ( `a` int(11) not null, `b` int(11) default null, primary key (`a`)) engine=innodb default charset=latin1
clickhouse slave：
attach table t1( `a` int32, `b` nullable(int32), `_sign` int8, `_version` uint64)engine = replacingmergetree(_version)partition by intdiv(a, 4294967)order by tuple(a)settings index_granularity = 8192
可以看到：
默认增加了 2 个隐藏字段：_sign(-1删除, 1写入) 和 _version(数据版本)
引擎转换成了 replacingmergetree，以 _version 作为 column version
原主键字段 a 作为排序和分区键
这只是一个表的复制，其他还有非常多的ddl处理，比如增加列、索引等，感兴趣可以观摩 parsers/mysql 下代码。
update和delete
当我们在 mysql master 执行：
mysql> delete from t1 where a=1;mysql> update t1 set b=b+1;
clickhouse t1数据（把 _sign 和 _version 一并查询）：
clickhouse :) select a,b,_sign, _version from t1;select a, b, _sign, _versionfrom t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ 1 │ 1 ││ 2 │ 2 │ 1 │ 1 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘
根据返回结果，可以看到是由 3 个 part 组成。
part1 由 mysql> insert into t1 values(1,1),(2,2) 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ 1 │ 1 ││ 2 │ 2 │ 1 │ 1 │└───┴───┴───────┴──────────┘
part2 由 mysql> delete from t1 where a=1 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘说明：_sign = -1表明处于删除状态
part3 由 update t1 set b=b+1 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘
使用 final 查询：
clickhouse :) select a,b,_sign,_version from t1 final;select a, b, _sign, _versionfrom t1final┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘2 rows in set. elapsed: 0.016 sec.
可以看到 replacingmergetree 已经根据 _version 和 orderby 对记录进行去重。
query
mysql master：
mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 3 |+---+------+1 row in set (0.00 sec)
clickhouse slave：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘clickhouse :) select *,_sign,_version from t1;select *, _sign, _versionfrom t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 ││ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘说明：这里还有一条删除记录，_sign为-1
materializemysql 被定义成一种存储引擎，所以在读取的时候，会根据 _sign 状态进行判断，如果是-1则是已经删除，进行过滤。
总结
clickhouse 实时复制同步 mysql 数据是 upstream 2020 的一个 roadmap，在整体构架上比较有挑战一直无人接单，挑战主要来自两方面：
对 mysql 复制通道与协议非常熟悉
对 clickhouse 整体机制非常熟悉
这样，在两个本来有点遥远的山头中间架起了一座高速，这条 10851号 高速由 zhang1024(clickhouse侧) 和bohutang(mysql复制) 两个修路工联合承建，目前正在接受 upstream 的验收。
关于同步 mysql 的数据，目前大家的方案基本都是在中间安置一个 binlog 消费工具，这个工具对 event 进行解析，然后再转换成 clickhouse 的 sql 语句，写到 clickhouse server，链路较长，性能损耗较大。
10851号 高速是在 clickhouse 内部实现一套 binlog 消费方案，然后根据 event 解析成clickhouse 内部的 block 结构，再直接写回到底层存储引擎，几乎是最高效的一种实现方式。
基于 database 级的复制，实现了多源复制的功能，如果复制通道坏掉，我们只需在 clickhouse 侧删除掉 database 然后再重建一次即可，非常方便。
对于单表的数据一致性，未来会实现一个 mysql crc 函数，用于校验 mysql 与 clickhouse 的数据一致性。
要想富，先修路！
高频电路覆盖绿油的作用及解决办法
区块链技术是否能阻止一只蝴蝶轻轻扇动翅膀就可引起的金融大风暴危机？
mkp61是什么电容？
华为推出“鲲鹏920”芯片，7纳米，号称是业界性能最高的基于Arm架构的芯片
卫星干扰起因及改善的解决办法解析
从MySQL到ClickHouse实时复制与实现
新能源汽车连接器现有技术有哪些
LG将首发支持3D应用的Google TV
“数字孪生”十问十答
RFID工具管理系统的主要功能及应用场合
基于DSP+CPLD的智能IED设计
PCB布线—教你如何成为走线高手
半导体的知识详解
PLC、DCS、FCS三大控制系统的特点
台积电美国5nm芯片将于2024年实现量产
维护工作为何出现使众人无从下手的“本位主义”倾向？
win7支持Chrome浏览器竟延长6个月
led屏和原装屏的区别
国产半导体设备的四个思考
一文带你了解 DAC
clickhouse 可以挂载为 mysql 的一个从库 ，先全量再增量的实时同步 mysql 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 oltp 和 olap 的融合从此不再头疼。
目前支持 mysql 5.6/5.7/8.0 版本，兼容 delete/update 语句，及大部分常用的 ddl 操作。
代码还处于 alpha 版本阶段，毕竟是两个异构生态的融合，仍然有不少的工作要做，同时也期待着社区用户的反馈，以加速迭代。
代码获取
由于还在验收阶段，我们只好把 github 上的 pull request 代码 pull 到本地。
git fetch origin pull/10851/head:mysql_replica_experiment
开始编译…
mysql master
我们需要一个开启 binlog 的 mysql 作为 master:
docker run -d -e mysql_root_password=123 mysql:5.7 mysqld --datadir=/var/lib/mysql --server-id=1 --log-bin=/var/lib/mysql/mysql-bin.log --gtid-mode=on --enforce-gtid-consistency
创建数据库和表，并写入数据：
mysql> create database ckdb;mysql> use ckdb;mysql> create table t1(a int not null primary key, b int);mysql> insert into t1 values(1,1),(2,2);mysql> select * from t1;+---+------+| a | b |+---+------+| 1 | 1 || 2 | 2 |+---+------+2 rows in set (0.00 sec)
clickhouse slave
目前以 database 为单位进行复制，不同的 database 可以来自不同的 mysql master，这样就可以实现多个 mysql 源数据同步到一个 clickhouse 做 olap 分析功能。
创建一个复制通道：
clickhouse :) create database ckdb engine = materializemysql('172.17.0.2:3306', 'ckdb', 'root', '123');clickhouse :) use ckdb;clickhouse :) show tables;┌─name─┐│ t1 │└──────┘clickhouse :) select * from t1;┌─a─┬─b─┐│ 1 │ 1 │└───┴───┘┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘2 rows in set. elapsed: 0.017 sec.
看下 clickhouse 的同步位点：
cat ckdatas/metadata/ckdb/.metadata
version:1binlog file:mysql-bin.000001binlog position:913data version:0
delete
首先在 mysql master 上执行一个删除操作：
mysql> delete from t1 where a=1;query ok, 1 row affected (0.01 sec)
然后在 clickhouse slave 侧查看记录：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 2 │└───┴───┘1 rows in set. elapsed: 0.032 sec.
此时的 metadata 里 data version 已经递增到 2：
cat ckdatas/metadata/ckdb/.metadataversion:1binlog file:mysql-bin.000001binlog position:1171data version:2
update
mysql master：
mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 2 |+---+------+1 row in set (0.00 sec)mysql> update t1 set b=b+1;mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 3 |+---+------+1 row in set (0.00 sec)
clickhouse slave：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘1 rows in set. elapsed: 0.023 sec.
实现机制
在探讨机制之前，首先需要了解下 mysql 的 binlog event ，主要有以下几种类型：
1. mysql_query_event-- ddl2. mysql_write_rows_event -- insert数据3. mysql_update_rows_event -- update数据4. mysql_delete_rows_event -- delete数据
当一个事务提交后，mysql 会把执行的 sql 处理成相应的 binlog event，并持久化到 binlog 文件。
binlog 是 mysql 对外输出的重要途径，只要你实现 mysql replication protocol，就可以流式的消费mysql 生产的 binlog event，具体协议见 replication protocol。
由于历史原因，协议繁琐而诡异，这不是本文重点。
对于 clickhouse 消费 mysql binlog 来说，主要有以下３个难点：
ddl 兼容
delete/update 支持
query 过滤
ddl
ddl 兼容花费了大量的代码去实现。
首先，我们看看 mysql 的表复制到 clickhouse 后会变成什么样子。
mysql master：
mysql> show create table t1g;*************************** 1. row *************************** table: t1create table: create table `t1` ( `a` int(11) not null, `b` int(11) default null, primary key (`a`)) engine=innodb default charset=latin1
clickhouse slave：
attach table t1( `a` int32, `b` nullable(int32), `_sign` int8, `_version` uint64)engine = replacingmergetree(_version)partition by intdiv(a, 4294967)order by tuple(a)settings index_granularity = 8192
可以看到：
默认增加了 2 个隐藏字段：_sign(-1删除, 1写入) 和 _version(数据版本)
引擎转换成了 replacingmergetree，以 _version 作为 column version
原主键字段 a 作为排序和分区键
这只是一个表的复制，其他还有非常多的ddl处理，比如增加列、索引等，感兴趣可以观摩 parsers/mysql 下代码。
update和delete
当我们在 mysql master 执行：
mysql> delete from t1 where a=1;mysql> update t1 set b=b+1;
clickhouse t1数据（把 _sign 和 _version 一并查询）：
clickhouse :) select a,b,_sign, _version from t1;select a, b, _sign, _versionfrom t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ 1 │ 1 ││ 2 │ 2 │ 1 │ 1 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘
根据返回结果，可以看到是由 3 个 part 组成。
part1 由 mysql> insert into t1 values(1,1),(2,2) 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ 1 │ 1 ││ 2 │ 2 │ 1 │ 1 │└───┴───┴───────┴──────────┘
part2 由 mysql> delete from t1 where a=1 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘说明：_sign = -1表明处于删除状态
part3 由 update t1 set b=b+1 生成：
┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘
使用 final 查询：
clickhouse :) select a,b,_sign,_version from t1 final;select a, b, _sign, _versionfrom t1final┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 │└───┴───┴───────┴──────────┘┌─a─┬─b─┬─_sign─┬─_version─┐│ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘2 rows in set. elapsed: 0.016 sec.
可以看到 replacingmergetree 已经根据 _version 和 orderby 对记录进行去重。
query
mysql master：
mysql> select * from t1;+---+------+| a | b |+---+------+| 2 | 3 |+---+------+1 row in set (0.00 sec)
clickhouse slave：
clickhouse :) select * from t1;select *from t1┌─a─┬─b─┐│ 2 │ 3 │└───┴───┘clickhouse :) select *,_sign,_version from t1;select *, _sign, _versionfrom t1┌─a─┬─b─┬─_sign─┬─_version─┐│ 1 │ 1 │ -1 │ 2 ││ 2 │ 3 │ 1 │ 3 │└───┴───┴───────┴──────────┘说明：这里还有一条删除记录，_sign为-1
materializemysql 被定义成一种存储引擎，所以在读取的时候，会根据 _sign 状态进行判断，如果是-1则是已经删除，进行过滤。
总结
clickhouse 实时复制同步 mysql 数据是 upstream 2020 的一个 roadmap，在整体构架上比较有挑战一直无人接单，挑战主要来自两方面：
对 mysql 复制通道与协议非常熟悉
对 clickhouse 整体机制非常熟悉
这样，在两个本来有点遥远的山头中间架起了一座高速，这条 10851号 高速由 zhang1024(clickhouse侧) 和bohutang(mysql复制) 两个修路工联合承建，目前正在接受 upstream 的验收。
关于同步 mysql 的数据，目前大家的方案基本都是在中间安置一个 binlog 消费工具，这个工具对 event 进行解析，然后再转换成 clickhouse 的 sql 语句，写到 clickhouse server，链路较长，性能损耗较大。
10851号 高速是在 clickhouse 内部实现一套 binlog 消费方案，然后根据 event 解析成clickhouse 内部的 block 结构，再直接写回到底层存储引擎，几乎是最高效的一种实现方式。
基于 database 级的复制，实现了多源复制的功能，如果复制通道坏掉，我们只需在 clickhouse 侧删除掉 database 然后再重建一次即可，非常方便。
对于单表的数据一致性，未来会实现一个 mysql crc 函数，用于校验 mysql 与 clickhouse 的数据一致性。
要想富，先修路！
高频电路覆盖绿油的作用及解决办法
区块链技术是否能阻止一只蝴蝶轻轻扇动翅膀就可引起的金融大风暴危机？
mkp61是什么电容？
华为推出“鲲鹏920”芯片，7纳米，号称是业界性能最高的基于Arm架构的芯片
卫星干扰起因及改善的解决办法解析
从MySQL到ClickHouse实时复制与实现
新能源汽车连接器现有技术有哪些
LG将首发支持3D应用的Google TV
“数字孪生”十问十答
RFID工具管理系统的主要功能及应用场合
基于DSP+CPLD的智能IED设计
PCB布线—教你如何成为走线高手
半导体的知识详解
PLC、DCS、FCS三大控制系统的特点
台积电美国5nm芯片将于2024年实现量产
维护工作为何出现使众人无从下手的“本位主义”倾向？
win7支持Chrome浏览器竟延长6个月
led屏和原装屏的区别
国产半导体设备的四个思考
一文带你了解 DAC