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

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

實現機制

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

1. MYSQL_QUERY_EVENT-- DDL
2. 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 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(數據版本)

引擎轉換成了 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 的數據一致性。

要想富，先修路！

審核編輯：湯梓紅