从MySQL到ClickHouse实时复制与实现
很多人看到标题还以为自己走错了夜场，其实没有。
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 的数据一致性。
要想富，先修路！
审核编辑：汤梓红
-
数据库
+关注
关注
7文章
3794浏览量
64360 -
代码
+关注
关注
30文章
4779浏览量
68521 -
MySQL
+关注
关注
1文章
804浏览量
26528
原文标题：从 MySQL 到 ClickHouse 实时复制与实现
文章出处：【微信号：magedu-Linux，微信公众号：马哥Linux运维】欢迎添加关注！文章转载请注明出处。
发布评论请先 登录
相关推荐
工商网监
评论