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

很多人看到標(biāo)題還以為自己走錯(cuò)了夜場(chǎng)，其實(shí)沒(méi)有。

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

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

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

代碼獲取

由于還在驗(yàn)收階段，我們只好把 github 上的 pull request 代碼 pull 到本地。

git fetch origin pull/10851/head:mysql_replica_experiment

開(kāi)始編譯…

MySQL Master

我們需要一個(gè)開(kāi)啟 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ù)庫(kù)和表，并寫入數(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 為單位進(jìn)行復(fù)制，不同的 database 可以來(lái)自不同的 MySQL master，這樣就可以實(shí)現(xiàn)多個(gè) MySQL 源數(shù)據(jù)同步到一個(gè) ClickHouse 做 OLAP 分析功能。

創(chuàng)建一個(gè)復(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 的同步位點(diǎn)：
cat ckdatas/metadata/ckdb/.metadata

Version:1
Binlog File:mysql-bin.000001
Binlog Position:913
Data Version:0

Delete

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

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.

此時(shí)的 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.

實(shí)現(xiàn)機(jī)制

在探討機(jī)制之前，首先需要了解下 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ù)

當(dāng)一個(gè)事務(wù)提交后，MySQL 會(huì)把執(zhí)行的 SQL 處理成相應(yīng)的 binlog event，并持久化到 binlog 文件。

binlog 是 MySQL 對(duì)外輸出的重要途徑，只要你實(shí)現(xiàn) MySQL Replication Protocol，就可以流式的消費(fèi)MySQL 生產(chǎn)的 binlog event，具體協(xié)議見(jiàn) Replication Protocol。

由于歷史原因，協(xié)議繁瑣而詭異，這不是本文重點(diǎn)。

對(duì)于 ClickHouse 消費(fèi) MySQL binlog 來(lái)說(shuō)，主要有以下３個(gè)難點(diǎn)：

DDL 兼容

Delete/Update 支持

Query 過(guò)濾

DDL

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

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

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

可以看到：

默認(rèn)增加了 2 個(gè)隱藏字段：_sign(-1刪除, 1寫入) 和 _version(數(shù)據(jù)版本)

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

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

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

Update和Delete

當(dāng)我們?cè)?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 個(gè) 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 │
└───┴───┴───────┴──────────┘
說(shuō)明：
_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 對(duì)記錄進(jìn)行去重。

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 │
└───┴───┴───────┴──────────┘
說(shuō)明：這里還有一條刪除記錄，_sign為-1

MaterializeMySQL 被定義成一種存儲(chǔ)引擎，所以在讀取的時(shí)候，會(huì)根據(jù) _sign 狀態(tài)進(jìn)行判斷，如果是-1則是已經(jīng)刪除，進(jìn)行過(guò)濾。

總結(jié)

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

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

對(duì) ClickHouse 整體機(jī)制非常熟悉

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

關(guān)于同步 MySQL 的數(shù)據(jù)，目前大家的方案基本都是在中間安置一個(gè) binlog 消費(fèi)工具，這個(gè)工具對(duì) event 進(jìn)行解析，然后再轉(zhuǎn)換成 ClickHouse 的 SQL 語(yǔ)句，寫到 ClickHouse server，鏈路較長(zhǎng)，性能損耗較大。

10851號(hào)高速是在 ClickHouse 內(nèi)部實(shí)現(xiàn)一套 binlog 消費(fèi)方案，然后根據(jù) event 解析成ClickHouse 內(nèi)部的 block 結(jié)構(gòu)，再直接寫回到底層存儲(chǔ)引擎，幾乎是最高效的一種實(shí)現(xiàn)方式。

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

對(duì)于單表的數(shù)據(jù)一致性，未來(lái)會(huì)實(shí)現(xiàn)一個(gè) MySQL CRC 函數(shù)，用于校驗(yàn) MySQL 與 ClickHouse 的數(shù)據(jù)一致性。

要想富，先修路！

審核編輯：湯梓紅