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 t1\G;*************************** 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,

    _version


FROM 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,

    _version


FROM t1


FINAL





┌─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,

    _version


FROM 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 的資料一致性。

要想富，先修路！

原文轉自

從 MySQL 到 ClickHouse 實時複製與實現