ClickHouse和他的朋友們（9）MySQL實時復制與實現(xiàn)

1、ClickHouse和他的朋友們（9） MySQL實時復制與實現(xiàn)InterpretersInterpretersParsersParserAn IExpressionInsertQuerySei ectQueryOOLQuery很多人看到標題還以為自己走錯了夜場，其實沒有。ClickHouse可以掛載為MySQL的一個從庫，先全量再增量的實時同步MySQL數(shù)據(jù)這 個功能可以說是今年最亮眼、最剛需的功能，基于它我們可以輕松的打造一套企業(yè)級解決方 案，讓OLTP和OLAP的融合從此不再頭疼。目前支持MySQL 5.6/5.7/8.0版本兼容Delete/Update語句及大局部常用的DDL操 作。

2、代碼已經(jīng)合并到upstream master分支，預(yù)計在20.8版本作為experimental功能發(fā)布。畢竟是兩個異構(gòu)生態(tài)的融合，仍然有不少的工作要做，同時也期待著社區(qū)用戶的反應(yīng)，以加速 迭代。代碼獲取根據(jù)返回結(jié)果，可以看到是由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說明處于刪除狀態(tài)由 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已經(jīng)根據(jù).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被定義成一種存儲引擎，所以在讀取的時候，會根據(jù)_sign狀態(tài)進行判斷， 如果是-1那么是已經(jīng)刪除，進行過濾。并行回放為什么MySQL需要并行回放？假設(shè)MySQL master有1024個并發(fā)同時寫入、更新數(shù)據(jù)，瞬間產(chǎn)生大量的binlog event ，MySQL slave上只有一個線程-個event接著一個event式回放，于是MySQL實現(xiàn)了并行回放功能！那么，MySQL

6、 slave回放時能否完全（或接近）模擬出master當時的1024并發(fā)行為呢?要想并行首先要解決的就是依賴問題：我們需要master標記出哪些event可以并行，哪些 event有先后父系，因為它是第一現(xiàn)場。MySQL通過在binlog里增加：last_committed，相同那么可以并行sequece_number，較小先執(zhí)行，描述先后依賴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加速回放，整套機制還是異常復雜的?；氐紺lickHouse slave問題，我們采用的單線程回放，延遲已經(jīng)不是主要問題，這是由

8、它們 的機制決定的：MySQL slave回放時，需要把binlog event轉(zhuǎn)換成SQL，然后模擬master的寫入，這種 邏輯復制是導致性能低下的最重要原因。而ClickHouse在回放上，直接把binlog event轉(zhuǎn)換成底層block結(jié)構(gòu)，然后直接寫入底 層的存儲引擎，接近于物理復制，可以理解為把binlog event直接回放到InnoDB的page。讀取最新雖然ClickHouse slave回放非?？?，接近于實時，如何在ClickHouse slave上總是讀取到最 新的數(shù)據(jù)呢？其實非常簡單，借助MySQL binlog GTID特性，每次讀的時候，我們跟master做一次

9、executed_gtid同步，然后等待這些executed_gtid回放完畢即可。數(shù)據(jù)致性對一致性要求較高的場景，我們怎么驗證MySQL master的數(shù)據(jù)和ClickHouse slave的數(shù) 據(jù)一致性呢？這塊初步想法是提供一個兼容MySQL checksum算法的函數(shù)，我們只需對出兩邊的 checksum 值即可。總結(jié)ClickHouse實時復制同步MySQL數(shù)據(jù)是upstream 2020的一個roadmap，在整體構(gòu)架上也較有挑戰(zhàn)一直無人接單，挑戰(zhàn)主要來自兩方面:對MySQL復制通道與協(xié)議會常熟悉對ClickHouse整體機制*常熟悉這樣，在兩個本來有點遙遠的山頭中間架起了一座高速，

10、這條10851號高速由 zhang1024(ClickHouse側(cè))和BohuTANG(MySQL復制)兩個修路工聯(lián)合承建，目前已經(jīng)合并 至U upstream 分支。欠于同步MySQL的數(shù)據(jù)，目前大家的方案基本都是在中間安置一個binlog消費工具，這個 I具對event進行解析，然后再轉(zhuǎn)換成ClickHouse的SQL語句，寫到ClickHouse server，鏈路較口，性能損耗較大。10851號高速是在ClickHouse內(nèi)部實現(xiàn)一套binlog消費方案，然后根據(jù)event解析成 ClickHouse內(nèi)部的block結(jié)構(gòu)，再直接回寫到底層存儲引擎，幾乎是最高效的一種實現(xiàn)方 式，實現(xiàn)與M

11、ySQL實時同步的能力，讓分析更接近現(xiàn)實?；赿atabase級的復制，實現(xiàn)了多源復制的功能，如果復制通道壞掉，我們只需在 ClickHouse側(cè)刪掉database再重建一次即可，非?？焖佟⒎奖?，OLTP+OLAP就是這么 簡單！要想富，先修路！文內(nèi)鏈接：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 )編譯、開發(fā)、測試MySQL Master我們需要一個開啟binlog的MySQL作為master:1docker run -d -e MYSQL_ROOT_PASSWORD= 123mysql: 5.7 mysqld -datadir=/var/創(chuàng)立數(shù)據(jù)庫和表，并寫入數(shù)據(jù):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，這樣就 可以實現(xiàn)多個MySQL源數(shù)據(jù)同步到一個ClickHouse做OLAP分析功能。首先開啟體驗開父：1clickhouse :) SET allow_experimental_database_materialize_mysql=l;創(chuàng)立一個復制通道: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上執(zhí)行一個刪除操作：mysql delete from tl where a=l;Query OK, 1 row affected (0.01 sec)然后在ClickHouse Slave側(cè)查看記錄：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已經(jīng)遞增到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式環(huán)境1MySQL8C16G云主機，1921680.3，基礎(chǔ)數(shù)據(jù)10188183條記錄2ClickHouse8C16G云主機，3benchyou8C8G云主機，192.168.0.5, 256并發(fā)寫， s : /gith

18、ub. com/xe性能測試跟硬件環(huán)境有較大關(guān)系，這里使用的是云主機模式，數(shù)據(jù)供參考。全量性能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.)在這個硬件環(huán)境下，全量同步性能大概是424507/s，42w事務(wù)每秒。因為全量的數(shù)據(jù)之間沒有依賴關(guān)系可以進一步優(yōu)化成并行，加速同步。全量的性能直接決定ClickHouse slave壞掉后重建的速度，如果你的MySQL有10億條數(shù) 據(jù)，大概40分鐘就可以重建完成。增量性能（實時同步）在當前配置下，ClickHouse slave單線程回放消費能力大于MySQL master 256并發(fā)下生產(chǎn) 能力，通過測試可以看到

22、它們保持實時同步。benchyou壓測數(shù)據(jù)，2.1w事務(wù)/秒（MySQL在當前環(huán)境下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事務(wù)/秒，實時同步：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實現(xiàn)機制在探討機制之前，首先需要了解下MySQL的binlog event，主要有以下幾種類型：. MYSQL_QUERY_EVENT- DDL. MYSQL_WRITE_ROWS_EVENT- insrt數(shù)據(jù). MYSQL_UPDATE_ROWS_EVENT - update數(shù)據(jù). MYSQL DELETE ROWS EVENT delte數(shù)據(jù)當一個事務(wù)提交后，MySQL會把執(zhí)行的SQL處理成相應(yīng)的binlog event，并持久化到 binlog 文件。binlog是MySQL對外輸出的重要途徑，只要你實現(xiàn)MySQL Replication Protocol，就可 以流式的消費MySQL生產(chǎn)的binlog event *具體協(xié)議口 Replication Protocol。由于歷史原因協(xié)議繁瑣而詭異這不是本文重點。對于ClickHouse消費MySQL binlog來說，主要有以下3個難點：DDL兼容Delete/Update 支持Query過濾DDLDDL兼容花費了大量的代碼去實現(xiàn)。首先，我們看看MySQL的表復制到ClickHouse后會變成什么樣子。MySQL master:mysql show create table tlG;* row *Table: