Clickhouse MaterializeMySQL引擎详解

MaterializeMySQL引擎

1 概述

MaterializeMySQL是针对Mysql设计的，为了增强数据的实时性，很多解决方案会利用binlog将数据写入到Clickhouse中，为了能够监听到Binlog事件，需要采用类似于canal这样的第三方中间件，这样就会增加系统的复杂度。
Clickhouse在20.8版本增加了MaterializeMySQL的database引擎，该database能映射到Mysql中的某个database，并自动在clickhouse中创建对应的ReplacingMergeTree。clickhouse服务作为Mysql副本，读取Binlog并执行DDL和DML请求，实现了基于Mysql Binlog机制的业务数据库实时同步功能。

1.1 特点

1. MaterializeMySQL同时支持全量和增量同步，在database创建之初会全量同步MySQL中的表和数据，之后则会通过binlog进行增量同步。
2. MaterializeMySQL database为其所创建的每张ReplacingMergeTree表自动增减了_sign和_version字段。
   1. _sign用于标记是否被删除，取值1或-1
   2. _version用作ReplacingMergeTree的ver版本参数，每当监听到insert、update和delete事件时，在database内全局自增
3. MaterializeMySQL支持以下几种binlog事件：
   1. MYSQL_WRITE_ROWS_EVENT: _sign = 1，_version ++
   2. MYSQL_DELETE_ROWS_EVENT: _sign = -1，_version ++
   3. MYSQL_UPDATE_ROWS_EVENT: 新数据 _sign = 1
   4. MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。

1.2 使用细则

1. DDL查询

MySQL DDL查询会被转换为Clickhouse DDL查询，如果Clickhouse不能解析某些DDL查询，该查询将会被忽略。

2. 数据复制

MaterializeMySQL 不支持直接插入、删除和更新查询，而是将 DDL 语句进行相应转换：
MySQL INSERT 查询被转换为 INSERT with _sign=1。
MySQL DELETE 查询被转换为 INSERT with _sign=-1。
MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。

3. select 查询

如果在 SELECT 查询中没有指定_version，则使用 FINAL 修饰符，返回_version 的最大值
对应的数据，即最新版本的数据。
如果在 SELECT 查询中没有指定_sign，则默认使用 WHERE _sign=1，即返回未删除状态
（_sign=1)的数据。

4. 索引转换

ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。
ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。

2 案例实操

2.1 MySQL开启binlog和GTID模式

1. 开启binlog

server-id=1 
log-bin=mysql-bin
binlog_format=ROW
1
2
3

2. 开启GTID

GTID是在MySQL主从模式下可以确保数据同步的一致性（主从切换时）。
GTID属于MySQL复制增强版，是目前MySQL主流复制模式，他为每个event分配一个全局唯一ID号，我们不用关心MySQL集群主从拓扑结构，直接告知MySQL这个GTID即可。

gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志
1
2
3

3. 重启MySQL

sudo systemctl restart mysqld
1

2.2 准备MySQL表和数据

CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int NOT NULL,
 `name` text DEFAULT NULL,
 `updatetime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1002,'EDT',NOW());

CREATE TABLE `testck`.`t_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.3 开启ClickHouse物化引擎

set allow_experimental_database_materialize_mysql=1;
1

2.4 创建复制管道

1. clickhouse 中创建MaterializeMySQL数据库

CREATE DATABASE test_binlog ENGINE = 
MaterializeMySQL('IP:PORT','DBNAME','USERNAME','PASSWORD');
1
2

2. 查看Clickhouse数据

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;
1
2
3
4

2.5 修改数据

1. 在MySQL中修改数据

update t_organization set name = CONCAT(name,'-v1') where id = 1
1

2. 查看clickhouse日志可以看到binlog监听事件，查询clickhouse

select * from t_organization;
1

2.6 删除数据

1. MySQL删除数据

DELETE FROM t_organization where id = 2;
1

2. clickhouse日志有deleteRows的binlog监听事件，查看数据

select * from t_organization;
1

3. 在刚才的查询中增加_sign和_version虚拟字段

select *,_sign,_version from t_organization order by _sign desc,_version desc;
1

在查询时，对于已经被删除的数据，_sign=-1，ClickHouse 会自动重写 SQL，将 _sign =-1 的数据过滤掉;对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gCm7xGMK-1684912806930)(https://cdn.nlark.com/yuque/0/2023/png/26215275/1684912739336-0d857ec6-11ab-474e-a110-0c4ef8e13f70.png#averageHue=%23cda765&clientId=ued59ffc3-a065-4&from=paste&height=309&id=uae6ac643&originHeight=309&originWidth=892&originalType=binary&ratio=1&rotation=0&showTitle=false&size=27728&status=done&style=none&taskId=u5415f037-1532-4573-afe3-9857edad853&title=&width=892)]

select * from t_organization
等同于
select * from t_organization final where _sign = 1
1
2
3

2.7 删除表

1. 在 mysql 执行删除表

drop table t_user;
1

2. 此时在 clickhouse 处会同步删除对应表，如果查询会报错

show tables;
select * from t_user;
DB::Exception: Table scene_mms.scene doesn't exist..
1
2
3

3. mysql 新建表，clickhouse 可以查询到

CREATE TABLE `testck`.`t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);

-- ClickHouse 查询
show tables;
select * from t_user;
1
2
3
4
5
6
7
8
9
10