從MySQL到ClickHouse實時復(fù)制與實現(xiàn)

很多人看到標題還以為自己走錯了夜場，其實沒有。

ClickHouse 可以掛載為 MySQL 的一個從庫 ，先全量再增量的實時同步 MySQL 數(shù)據(jù)，這個功能可以說是今年最亮眼、最剛需的功能，基于它我們可以輕松的打造一套企業(yè)級解決方案，讓 OLTP 和 OLAP 的融合從此不再頭疼。

目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 語句，及大部分常用的 DDL 操作。

代碼還處于 Alpha 版本階段，畢竟是兩個異構(gòu)生態(tài)的融合，仍然有不少的工作要做，同時也期待著社區(qū)用戶的反饋，以加速迭代。

代碼獲取

由于還在驗收階段，我們只好把 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

創(chuàng)建數(shù)據(jù)庫和表，并寫入數(shù)據(jù)：

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 為單位進行復(fù)制，不同的 database 可以來自不同的 MySQL master，這樣就可以實現(xiàn)多個 MySQL 源數(shù)據(jù)同步到一個 ClickHouse 做 OLAP 分析功能。

創(chuàng)建一個復(fù)制通道：

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:1
Binlog File:mysql-bin.000001
Binlog Position:913
Data Version:0

Delete

首先在 MySQL Master 上執(zhí)行一個刪除操作：

mysql> delete from t1 where a=1;
Query OK, 1 row affected (0.01 sec)

然后在 ClickHouse Slave 側(cè)查看記錄：

clickhouse :) select * from t1;


SELECT *
FROM t1


┌─a─┬─b─┐
│ 2 │ 2 │
└───┴───┘


1 rows in set. Elapsed: 0.032 sec.

此時的 metadata 里 Data Version 已經(jīng)遞增到 2：

cat ckdatas/metadata/ckdb/.metadata
Version:1
Binlog File:mysql-bin.000001
Binlog Position:1171
Data 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.

實現(xiàn)機制

在探討機制之前，首先需要了解下 MySQL 的 binlog event ，主要有以下幾種類型：

1. MYSQL_QUERY_EVENT-- DDL
2. MYSQL_WRITE_ROWS_EVENT-- insert數(shù)據(jù)
3. MYSQL_UPDATE_ROWS_EVENT -- update數(shù)據(jù)
4. MYSQL_DELETE_ROWS_EVENT -- delete數(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 來說，主要有以下３個難點：

DDL 兼容

Delete/Update 支持

Query 過濾

DDL

DDL 兼容花費了大量的代碼去實現(xiàn)。

首先，我們看看 MySQL 的表復(fù)制到 ClickHouse 后會變成什么樣子。

MySQL master：

mysql> show create table t1G;
*************************** 1. row ***************************
       Table: t1
Create 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(數(shù)據(jù)版本)

引擎轉(zhuǎn)換成了 ReplacingMergeTree，以 _version 作為 column version

原主鍵字段 a 作為排序和分區(qū)鍵

這只是一個表的復(fù)制，其他還有非常多的DDL處理，比如增加列、索引等，感興趣可以觀摩 Parsers/MySQL 下代碼。

Update和Delete

當我們在 MySQL master 執(zhí)行：

mysql> delete from t1 where a=1;
mysql> update t1 set b=b+1;

ClickHouse t1數(shù)據(jù)（把 _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 │
└───┴───┴───────┴──────────┘

根據(jù)返回結(jié)果，可以看到是由 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表明處于刪除狀態(tài)

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 已經(jīng)根據(jù) _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 被定義成一種存儲引擎，所以在讀取的時候，會根據(jù) _sign 狀態(tài)進行判斷，如果是-1則是已經(jīng)刪除，進行過濾。

總結(jié)

ClickHouse 實時復(fù)制同步 MySQL 數(shù)據(jù)是 upstream 2020 的一個 roadmap，在整體構(gòu)架上比較有挑戰(zhàn)一直無人接單，挑戰(zhàn)主要來自兩方面：

對 MySQL 復(fù)制通道與協(xié)議非常熟悉

對 ClickHouse 整體機制非常熟悉

這樣，在兩個本來有點遙遠的山頭中間架起了一座高速，這條 10851號高速由 zhang1024(ClickHouse側(cè)) 和BohuTANG(MySQL復(fù)制) 兩個修路工聯(lián)合承建，目前正在接受 upstream 的驗收。

關(guān)于同步 MySQL 的數(shù)據(jù)，目前大家的方案基本都是在中間安置一個 binlog 消費工具，這個工具對 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)方式。

基于 database 級的復(fù)制，實現(xiàn)了多源復(fù)制的功能，如果復(fù)制通道壞掉，我們只需在 ClickHouse 側(cè)刪除掉 database 然后再重建一次即可，非常方便。

對于單表的數(shù)據(jù)一致性，未來會實現(xiàn)一個 MySQL CRC 函數(shù)，用于校驗 MySQL 與 ClickHouse 的數(shù)據(jù)一致性。

要想富，先修路！

審核編輯：湯梓紅