ClickHouse和他的朋友们（9）MySQL实时复制与实现

1、ClickHouse和他的朋友们（9） MySQL实时复制与实现InterpretersInterpretersParsersParserAn IExpressionInsertQuerySei ectQueryOOLQuery很多人看到标题还以为自己走错了夜场，其实没有。ClickHouse可以挂载为MySQL的一个从库，先全量再增量的实时同步MySQL数据这 个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方 案，让OLTP和OLAP的融合从此不再头疼。目前支持MySQL 5.6/5.7/8.0版本兼容Delete/Update语句及大局部常用的DDL操 作。

2、代码已经合并到upstream master分支，预计在20.8版本作为experimental功能发布。毕竟是两个异构生态的融合，仍然有不少的工作要做，同时也期待着社区用户的反应，以加速 迭代。代码获取根据返回结果，可以看到是由3个part组成。parti 由 mysql insert into tl values (1,1), (2,2) 生 成 ：1-a-b_s i g n-_ve rsi on-21 1 1 1 11 11 131 2 | 2 |1 |1 |411111由 mysql delete from tl where a=l 生 成：I-a-TI 1 Isign-_versio

3、n-说明：sign = -1说明处于删除状态由 update 11 set b=b+l 生 成 ：-ab_s ign-_ve rsio n-使用final查询：clickhouse :) select a,b,_sign,_version from tl final;SELECTa，b, _signzversionFROM tlFINAL1011b71sign-_version-1121313-a-b_s ign-_ve rsio n-1415161716172 rows in set. Elapsed: 0.016 sec.18可以看到ReplacingMergeTree已经根据.versi

4、on和OrderBy对记录进行去重。QueryMySQL master:mysql select * from 11;1 row in set (0.00 sec)ClickHouse slave:i2345678910111213141516171819202122clickhouse :) select * from tl;SELECT 文FROM tlclickhouse :) select *,_sign,_version from tl;clickhouse :) select *,_sign,_version from tl;SELECT * _signz _versionFROM

5、 tl-a-1b-1-_s ign-1-_ve rsio n-1 TOC o 1-5 h z I 1I1I-1 I2|2|3|1 |3|I|LI说明：这里还有一条删除记录, _sign为-1MaterializeMySQL被定义成一种存储引擎，所以在读取的时候，会根据_sign状态进行判断， 如果是-1那么是已经删除，进行过滤。并行回放为什么MySQL需要并行回放？假设MySQL master有1024个并发同时写入、更新数据，瞬间产生大量的binlog event ，MySQL slave上只有一个线程-个event接着一个event式回放，于是MySQL实现了并行回放功能！那么，MySQL

6、 slave回放时能否完全（或接近）模拟出master当时的1024并发行为呢?要想并行首先要解决的就是依赖问题：我们需要master标记出哪些event可以并行，哪些 event有先后父系，因为它是第一现场。MySQL通过在binlog里增加：last_committed，相同那么可以并行sequece_number，较小先执行，描述先后依赖last_committed=3last_committed=3sequece_number=4-event1last_committed=4last_committed=4last_committed=5last_committed=4last_com

7、mitted=4last_committed=5sequece_number=5sequece_number=6sequece_number=7-event2-event3-event4event2和events那么可以并行，event4需要等待前面event完成才可以回放。以上只是一个大体原理，目前MySQL有3种并行模式可以选择：,基于database并行.基于 group commit 并行.基于主键不冲突的write set并行最大程度上让MySQL slave加速回放，整套机制还是异常复杂的。回到ClickHouse slave问题，我们采用的单线程回放，延迟已经不是主要问题，这是由

8、它们 的机制决定的：MySQL slave回放时，需要把binlog event转换成SQL，然后模拟master的写入，这种 逻辑复制是导致性能低下的最重要原因。而ClickHouse在回放上，直接把binlog event转换成底层block结构，然后直接写入底 层的存储引擎，接近于物理复制，可以理解为把binlog event直接回放到InnoDB的page。读取最新虽然ClickHouse slave回放非常快，接近于实时，如何在ClickHouse slave上总是读取到最 新的数据呢？其实非常简单，借助MySQL binlog GTID特性，每次读的时候，我们跟master做一次

9、executed_gtid同步，然后等待这些executed_gtid回放完毕即可。数据致性对一致性要求较高的场景，我们怎么验证MySQL master的数据和ClickHouse slave的数 据一致性呢？这块初步想法是提供一个兼容MySQL checksum算法的函数，我们只需对出两边的 checksum 值即可。总结ClickHouse实时复制同步MySQL数据是upstream 2020的一个roadmap，在整体构架上也较有挑战一直无人接单，挑战主要来自两方面:对MySQL复制通道与协议会常熟悉对ClickHouse整体机制*常熟悉这样，在两个本来有点遥远的山头中间架起了一座高速，

10、这条10851号高速由 zhang1024(ClickHouse侧)和BohuTANG(MySQL复制)两个修路工联合承建，目前已经合并 至U upstream 分支。欠于同步MySQL的数据，目前大家的方案基本都是在中间安置一个binlog消费工具，这个 I具对event进行解析，然后再转换成ClickHouse的SQL语句，写到ClickHouse server，链路较口，性能损耗较大。10851号高速是在ClickHouse内部实现一套binlog消费方案，然后根据event解析成 ClickHouse内部的block结构，再直接回写到底层存储引擎，几乎是最高效的一种实现方 式，实现与M

11、ySQL实时同步的能力，让分析更接近现实。基于database级的复制，实现了多源复制的功能，如果复制通道坏掉，我们只需在 ClickHouse侧删掉database再重建一次即可，非常快速、方便，OLTP+OLAP就是这么 简单！要想富，先修路！文内链接：httDS：/github /ClickHoLise/ClickHoLise/pull/10851 s:/github /ClickHouse/ClickHouse s:/bohutang.me/2020/06/05/clickhouse-and-friends-development/ 获取clickhouse/master代码编译即可，

12、方法口 ClickHouse和他的朋友们(1 )编译、开发、测试MySQL Master我们需要一个开启binlog的MySQL作为master:1docker run -d -e MYSQL_ROOT_PASSWORD= 123mysql: 5.7 mysqld -datadir=/var/创立数据库和表，并写入数据:123456789101112mysql create database ckdb;mysql use ckdb;mysql create table tl(a int not null primary key, b int);mysql insert into tl valu

13、es (1,1), (2,2);mysql select * from 11;I a | b I2 rows in set (0.00sec)ClickHouse Slave目前以database为单位进行复制，不同的database可以来自不同的MySQL master，这样就 可以实现多个MySQL源数据同步到一个ClickHouse做OLAP分析功能。首先开启体验开父：1clickhouse :) SET allow_experimental_database_materialize_mysql=l;创立一个复制通道:2345clickhouse :)clickhouse :)click

14、house :)-name-I tlCREATE DATABASE ckdb ENGINE = MaterializeMySQL(1use ckdb;show tables;67891011121314158910clickhouse :) select * from 11;Iar-b)I 1 I 1 I TOC o 1-5 h z HYPERLINK l bookmark56 o Current Document I 2 I 2 I L1J2 rows in set. Elapsed: 0.017 sec.看下ClickHouse的同步位点：cat ckdatas/metadata/ckdb

15、/.metadataVersion:1Binlog File:mysql-bin.000001Binlog Position:913Data Version:0Delete首先在MySQL Master上执行一个删除操作：mysql delete from tl where a=l;Query OK, 1 row affected (0.01 sec)然后在ClickHouse Slave侧查看记录：clickhouse : ) select * from 11; 2SELECT *FROM tlI-a-b1I 2 I 2 I I I I1 rows in set. Elapsed: 0.03

16、2 sec.i2345此时的metadata里Data Version已经递增到2:cat ckdatas/metadata/ckdb/.metadata TOC o 1-5 h z Version:1Binlog File:mysql-bin.000001Binlog Position:1171Data Version:2UpdateMySQL Master:1234567891011121314151617mysql select * from 11; aaaB1 a | b|1 2 |2 |1 row in set (0.00 sec)mysql update t1 set b=b+l;

17、mysql select * from 11;1 a | b|1 2 |3 | 1 row in set (0.00 sec)ClickHouse Slave:clickhouse: ) select * from 11;SELECT *FROM t1r-a-T-b-I 2 | 3 |L1 rows in set. Elapsed: 0.023 sec.10T生育日那么试测It式环境1MySQL8C16G云主机，1921680.3，基础数据10188183条记录2ClickHouse8C16G云主机，3benchyou8C8G云主机，192.168.0.5, 256并发写， s : /gith

18、ub. com/xe性能测试跟硬件环境有较大关系，这里使用的是云主机模式，数据供参考。全量性能1234567891011121314151617181920212223242526278cl6G-vm :) create database sbtest engine=MaterializeMySQL(1192.168.0.3:8cl6G-vm :) watch Ivl;WATCH Ivl-count () -now () -_version-|0 | 2020-07-29 06:36:04 |1 |LLI-count () now () -1-_version-| 1113585 | 2020

19、-07-29 06:36:05 |2 |LLII-count () now () -_version-| 2227170 | 2020-07-29 06:36:07 |3 |-count ()now () -1-_ver sion-| 334075512020-07-2906:36:10 |14Jcount ()1now () -p-_version-1| 4454340I2020-07-2906:36:13 |I5J-count ()一T-now () -p-_version-1| 55679252020-07-2906:36:16 |6LlJ-count ()1-now () -p-_ve

20、rsion| 668151012020-07-2906:36:18 | 17J-count ()now () -r-_version-1| 77950952020-07-2906:36:22 |828293031323334353637383940-count () -now () -_version- TOC o 1-5 h z | 8908680 | 2020-07-29 06:36:25 |9 | count () now () _version-| 10022265 | 2020-07-29 06:36:28 |10 | count () now () _version| 101881

21、83 | 2020-07-29 06:36:28 |11 |IIll-Progress: 11.00 rowsr 220.00 B (0.16 rows/s., 3.17 B/s.)在这个硬件环境下，全量同步性能大概是424507/s，42w事务每秒。因为全量的数据之间没有依赖关系可以进一步优化成并行，加速同步。全量的性能直接决定ClickHouse slave坏掉后重建的速度，如果你的MySQL有10亿条数 据，大概40分钟就可以重建完成。增量性能（实时同步）在当前配置下，ClickHouse slave单线程回放消费能力大于MySQL master 256并发下生产 能力，通过测试可以看到

22、它们保持实时同步。benchyou压测数据，2.1w事务/秒（MySQL在当前环境下TPS上不去）：1./bin/benchyou -mysql-host=l92.168.0.3 -mysql-user=test -mysql-passwo31645time13sthdsr : 0 , w : 2 5 6,u :0,d:0tps19962wtps19962rtps0678910time14stime15sthdsr : 0 , w : 2 5 6,thdsr : 0 , w : 2 5 6,u :u :0,d:00z d:0tps20415tps21131wtps20415wtps21131r

23、tps0rtps0111213time16sthdsr : 0 , w : 2 5 6,u :0,d:0tps 21606wtps21606rtps 01415time17sthdsr : 0 , w : 2 5 6,u :0,d:0tps22505wtps22505rtps0ClickHouse测单线程回放能力，2.1w事务/秒，实时同步：12345678910111213141516171819202122232 4252627282930313233-count () -p-now () -_version-1|150732 |2020-07-3005:17: 15117L_L11-co

24、unt () -p-now()-p_version-1|155477 |2020-07-3005:17: 16|18L_L1l-count () -r-now()-r-_version-1|160222 |II2020-07-3005:17:16l119l-count () p-now()-r-_version-1|164967 |2020-07-3005 : 17 : 16|20II1Jcount () -p-now () -_version-1|169712 |II2020-07-3005:17:1611211-count () -p-now () -_version-1|174457 |

25、2020-07-3005:17:16|22L_L1I-count () -p-now()-p_version-1|179202 |2020-07-3005:17: 17|23II1J-count () -r-now () -_version-1|183947 |II2020-07-3005:17:171|241-count () -p-now ()-p_version-1|188692 |ll2020-07-3005:17:171|25J-count () -r-now()-r-_version-1|193437 |II2020-07-3005:17: 17I|26J-count () -p-

26、now()-p_version-1|198182 |2020-07-3005:17: 17|27lllJ实现机制在探讨机制之前，首先需要了解下MySQL的binlog event，主要有以下几种类型：. MYSQL_QUERY_EVENT- DDL. MYSQL_WRITE_ROWS_EVENT- insrt数据. MYSQL_UPDATE_ROWS_EVENT - update数据. MYSQL DELETE ROWS EVENT delte数据当一个事务提交后，MySQL会把执行的SQL处理成相应的binlog event，并持久化到 binlog 文件。binlog是MySQL对外输出的重要途径，只要你实现MySQL Replication Protocol，就可 以流式的消费MySQL生产的binlog event *具体协议口 Replication Protocol。由于历史原因协议繁琐而诡异这不是本文重点。对于ClickHouse消费MySQL binlog来说，主要有以下3个难点：DDL兼容Delete/Update 支持Query过滤DDLDDL兼容花费了大量的代码去实现。首先，我们看看MySQL的表复制到ClickHouse后会变成什么样子。MySQL master:mysql show create table tlG;* row *Table: