ClickHouse和他的朋友们（9）MySQL实时复制与实现-低调大师

ClickHouse和他的朋友们（9）MySQL实时复制与实现

2020-12-26 551

原文出处：https://bohutang.me/2020/07/26/clickhouse-and-friends-mysql-replication/

最后更新: 2020-09-08

很多人看到标题还以为自己走错了夜场，其实没有。

ClickHouse 可以挂载为 MySQL 的一个从库，先全量再增量的实时同步 MySQL 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的融合从此不再头疼。

目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。代码 (https://github.com/ClickHouse/ClickHouse/pull/10851)已经合并到 upstream master 分支，预计在20.8版本作为experimental 功能发布。

毕竟是两个异构生态的融合，仍然有不少的工作要做，同时也期待着社区用户的反馈，以加速迭代。

代码获取

获取 clickhouse/master (https://github.com/ClickHouse/ClickHouse) 代码编译即可，方法见 ClickHouse和他的朋友们（1）编译、开发、测试...

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 :) SET allow_experimental_database_materialize_mysql=1;

创建一个复制通道：

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          8C16G 云主机, 192.168.0.3，基础数据 10188183 条记录
ClickHouse     8C16G 云主机, 192.168.0.4
benchyou       8C8G  云主机,  192.168.0.5, 256并发写, https://github.com/xelabs/benchyou

性能测试跟硬件环境有较大关系，这里使用的是云主机模式，数据供参考。

全量性能

8c16G-vm :) create database sbtest engine=MaterializeMySQL('192.168.0.3:3306', 'sbtest', 'test', '123');

8c16G-vm :) watch lv1;

WATCH lv1

┌─count()─┬───────────────now()─┬─_version─┐
│       0 │ 2020-07-29 06:36:04 │        1 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 1113585 │ 2020-07-29 06:36:05 │        2 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 2227170 │ 2020-07-29 06:36:07 │        3 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 3340755 │ 2020-07-29 06:36:10 │        4 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 4454340 │ 2020-07-29 06:36:13 │        5 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 5567925 │ 2020-07-29 06:36:16 │        6 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 6681510 │ 2020-07-29 06:36:18 │        7 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 7795095 │ 2020-07-29 06:36:22 │        8 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│ 8908680 │ 2020-07-29 06:36:25 │        9 │
└─────────┴─────────────────────┴──────────┘
┌──count()─┬───────────────now()─┬─_version─┐
│ 10022265 │ 2020-07-29 06:36:28 │       10 │
└──────────┴─────────────────────┴──────────┘
┌──count()─┬───────────────now()─┬─_version─┐
│ 10188183 │ 2020-07-29 06:36:28 │       11 │
└──────────┴─────────────────────┴──────────┘
← Progress: 11.00 rows, 220.00 B (0.16 rows/s., 3.17 B/s.)

在这个硬件环境下，全量同步性能大概是 424507/s，42w 事务每秒。因为全量的数据之间没有依赖关系，可以进一步优化成并行，加速同步。全量的性能直接决定 ClickHouse slave 坏掉后重建的速度，如果你的 MySQL 有 10 亿条数据，大概 40 分钟就可以重建完成。

增量性能(实时同步)

在当前配置下，ClickHouse slave 单线程回放消费能力大于 MySQL master 256 并发下生产能力，通过测试可以看到它们保持实时同步。

benchyou 压测数据，2.1w 事务/秒(MySQL 在当前环境下TPS上不去):

./bin/benchyou --mysql-host=192.168.0.3 --mysql-user=test --mysql-password=123 --oltp-tables-count=1 --write-threads=256 --read-threads=0

time            thds               tps     wtps    rtps
[13s]        [r:0,w:256,u:0,d:0]  19962    19962   0    

time            thds               tps     wtps    rtps
[14s]        [r:0,w:256,u:0,d:0]  20415    20415   0 

time            thds               tps     wtps    rtps
[15s]        [r:0,w:256,u:0,d:0]  21131    21131   0

time            thds               tps     wtps    rtps
[16s]        [r:0,w:256,u:0,d:0]  21606    21606   0

time            thds               tps     wtps    rtps
[17s]        [r:0,w:256,u:0,d:0]  22505    22505   0

ClickHouse 侧单线程回放能力，2.1w 事务/秒，实时同步：

┌─count()─┬───────────────now()─┬─_version─┐
│  150732 │ 2020-07-30 05:17:15 │       17 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  155477 │ 2020-07-30 05:17:16 │       18 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  160222 │ 2020-07-30 05:17:16 │       19 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  164967 │ 2020-07-30 05:17:16 │       20 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  169712 │ 2020-07-30 05:17:16 │       21 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  174457 │ 2020-07-30 05:17:16 │       22 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  179202 │ 2020-07-30 05:17:17 │       23 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  183947 │ 2020-07-30 05:17:17 │       24 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  188692 │ 2020-07-30 05:17:17 │       25 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  193437 │ 2020-07-30 05:17:17 │       26 │
└─────────┴─────────────────────┴──────────┘
┌─count()─┬───────────────now()─┬─_version─┐
│  198182 │ 2020-07-30 05:17:17 │       27 │
└─────────┴─────────────────────┴──────────┘

实现机制

在探讨机制之前，首先需要了解下 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 (https://dev.mysql.com/doc/internals/en/replication-protocol.html)。

由于历史原因，协议繁琐而诡异，这不是本文重点。

对于 ClickHouse 消费 MySQL binlog 来说，主要有以下３个难点：

DDL 兼容
Delete/Update 支持
Query 过滤

DDL

DDL 兼容花费了大量的代码去实现。

首先，我们看看 MySQL 的表复制到 ClickHouse 后会变成什么样子。

MySQL master:

mysql> show create table t1\G;
*************************** 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 下代码。

索引转换

MySQL 对应的主键/索引如何对应到 MaterializeMySQL 表结构呢？

首先针对 MySQL 建表语句进行key扫描，InterpretersMySQLDDLQuery::getKeys：(https://github.com/ClickHouse/ClickHouse/blob/1076a42cf599c7eedd7d0cda2246f9afaf6434a4/src/Interpreters/MySQL/InterpretersMySQLDDLQuery.cpp#L157)

扫描 unique_key
扫描 primary_key
扫描 auto_increment

其次根据 key 按照以下顺序生成 OrderBy tuple 表达式，InterpretersMySQLDDLQuery::getOrderByPolicy： (https://github.com/ClickHouse/ClickHouse/blob/1076a42cf599c7eedd7d0cda2246f9afaf6434a4/src/Interpreters/MySQL/InterpretersMySQLDDLQuery.cpp#L288)

primary_key[not increment]
key[not increment]
unique[not increment]
unique[increment]
key[increment]
primary_key[increment]

ClickHouse 的物理序目前只有一种(多物理序有一定的规划)，是有 OrderBy 决定，所以 MaterializeMySQL 索引如果利用不佳，可以使用物化视图建立新的物理序解决。

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则是已经删除，进行过滤。

并行回放

为什么 MySQL 需要并行回放？

假设 MySQL master 有 1024 个并发同时写入、更新数据，瞬间产生大量的 binlog event ，MySQL slave 上只有一个线程一个 event 接着一个 event 式回放，于是 MySQL 实现了并行回放功能！

那么，MySQL slave 回放时能否完全(或接近)模拟出 master 当时的 1024 并发行为呢？

要想并行首先要解决的就是依赖问题：我们需要 master 标记出哪些 event 可以并行，哪些 event 有先后关系，因为它是第一现场。

MySQL 通过在 binlog 里增加:

last_committed，相同则可以并行
sequece_number，较小先执行，描述先后依赖

last_committed=3   sequece_number=4   -- event1
last_committed=4   sequece_number=5   -- event2
last_committed=4   sequece_number=6   -- event3
last_committed=5   sequece_number=7   -- event4

event2 和 event3 则可以并行，event4 需要等待前面 event 完成才可以回放。以上只是一个大体原理，目前 MySQL 有３种并行模式可以选择：

基于 database 并行
基于 group commit 并行
基于主键不冲突的 write set 并行

最大程度上让 MySQL slave加速回放，整套机制还是异常复杂的。

回到 ClickHouse slave 问题，我们采用的单线程回放，延迟已经不是主要问题，这是由它们的机制决定的：MySQL slave 回放时，需要把 binlog event 转换成 SQL，然后模拟 master 的写入，这种逻辑复制是导致性能低下的最重要原因。而 ClickHouse 在回放上，直接把 binlog event 转换成底层 block 结构，然后直接写入底层的存储引擎，接近于物理复制，可以理解为把 binlog event 直接回放到 InnoDB 的 page。

读取最新

虽然 ClickHouse slave 回放非常快，接近于实时，如何在ClickHouse slave上总是读取到最新的数据呢？

其实非常简单，借助 MySQL binlog GTID 特性，每次读的时候，我们跟ｍaster 做一次 executed_gtid 同步，然后等待这些 executed_gtid 回放完毕即可。

数据一致性

对一致性要求较高的场景，我们怎么验证 MySQL master 的数据和 ClickHouse slave 的数据一致性呢？

这块初步想法是提供一个兼容 MySQL checksum 算法的函数，我们只需对比两边的 checksum 值即可。

总结

ClickHouse 实时复制同步 MySQL 数据是 upstream 2020 的一个 roadmap，在整体构架上比较有挑战一直无人接单，挑战主要来自两方面：

对 MySQL 复制通道与协议非常熟悉
对 ClickHouse 整体机制非常熟悉

这样，在两个本来有点遥远的山头中间架起了一座高速，这条 10851号(https://github.com/ClickHouse/ClickHouse/pull/10851) 高速由 zhang1024(ClickHouse侧) 和 BohuTANG(MySQL复制) 两个修路工联合承建，目前已经合并到 upstream 分支。

关于同步 MySQL 的数据，目前大家的方案基本都是在中间安置一个 binlog 消费工具，这个工具对 event 进行解析，然后再转换成 ClickHouse 的 SQL 语句，写到 ClickHouse server，链路较长，性能损耗较大。

10851号 (https://github.com/ClickHouse/ClickHouse/pull/10851) 高速是在 ClickHouse 内部实现一套 binlog 消费方案，然后根据 event 解析成 ClickHouse 内部的 block 结构，再直接回写到底层存储引擎，几乎是最高效的一种实现方式，实现与 MySQL 实时同步的能力，让分析更接近现实。

基于 database 级的复制，实现了多源复制的功能，如果复制通道坏掉，我们只需在 ClickHouse 侧删掉 database 再重建一次即可，非常快速、方便，OLTP+OLAP 就是这么简单！

要想富，先修路！

全文完。

Enjoy ClickHouse :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

本文分享自微信公众号 - 老叶茶馆（iMySQL_WX）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

微信关注我们

原文链接：https://my.oschina.net/yejr/blog/4864796

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

Dart基础——如何在Dart&Flutter中使用Stream

1 什么是Stream? Stream是Dart用来处理异步的API，和同样用来处理异步的Future不同的是，Stream可以异步的返回多个结果，而Future只能返回一个，如果你对Future有疑问，可以参考作者的上一篇文章，Dart基础——Dart异步Future与事件循环Event Loop。 2 如何创建Stream? 1.1使用Stream的构造方法 Stream periodicStream = Stream.periodic(Duration(seconds: 2), (num) { return num; }); periodic构造方法主要有两个参数，第一个参数类型为Duration（时间间隔），第二个参数类型为Function，Function每隔一个Duration（时间间隔）会被调用一次,参数num为事件调用的次数，从0开始依次递增。翻阅源码 Stream.periodic是使用Timer.periodic加_SyncStreamController实现的 1.2将方法的返回值声明为Stream Stream<String> timedCount...

2020-12-28

664

李荣谦有道精品课数据中台团队数据中台实时数仓负责人我们本次想要和大家分享一下有道精品课数据中台的架构演进过程，以及Doris作为一个MPP分析型数据库是如何为不断增长的业务体量提供有效支撑并进行数据赋能的。本文以我们在实时数仓选型的经验为切入点，进一步着重分享使用Doris过程中遇到的问题以及我们针对这些问题所做出的调整和优化。 1 背景 1.1 业务场景根据业务需求，目前有道精品课的数据层架构上可分为离线和实时两部分。离线系统主要处理埋点相关数据，采用批处理的方式定时计算。而实时流数据主要来源于各个业务系统实时产生的数据流以及数据库的变更日志，需要考虑数据的准确性、实时性和时序特征，处理过程非常复杂。有道精品课数据中台团队依托于其实时计算能力在整个数据架构中主要承担了实时数据处理的角色，同时为下游离线数仓提供实时数据同步服务。数据中台主要服务的用户角色和对应的数据需求如下：运营/策略/负责人主要查看学生的整体情况，查询数据中台的一些课程维度实时聚合数据辅导/销售主要关注所服务学生的各种实时明细数据品控主要查看课程/老师/辅导各维度整体数据，通过T+1的离线报表...

2020-12-28

690

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。