13.ClickHouse之MaterializeMySQL引擎

Leefs 2022-05-06 PM 985℃ 0条

[TOC]

### 前言

MaterializeMySQL号称ClickHouse的王炸功能，本篇文章将结合具体示例来对MaterializeMySQL进行一个介绍

**本篇示例版本**

+ ClickHouse 21.7.3.14-2
+ MySQL 8.0.23

### 一、概述

MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 `binlog` 将数据写入到 ClickHouse。为了能够监听 `binlog` 事件，我们需要用到类似 canal 这样的第三 方中间件，这无疑增加了系统的复杂度。

`ClickHouse 20.8.2.3` 版本新增加了 `MaterializeMySQL` 的 `database` 引擎，该 `database` 能映射到 MySQL 中的某个 `database` ， 并自动在 ClickHouse 中创建对应的 `ReplacingMergeTree`。ClickHouse 服务做为 MySQL 副本，读取 `Binlog` 并执行 DDL 和 DML 请求，实现了基于 MySQL `Binlog` 机制的业务数据库实时同步功能。

### 二、特点

（1）`MaterializeMySQL` 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。

（2）`MaterializeMySQL` database为其所创建的每张 `ReplacingMergeTree` 自动增加了 `_sign` 和 `_version` 字段。

+ `_version`：用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update  和 delete 事件时，在 databse 内全局自增。
+ `_sign`：用于标记是否被删除，取值 1（未删除）或者 -1（已删除）。

**目前 MaterializeMySQL 支持如下几种 binlog 事件:**

+ **MYSQL_WRITE_ROWS_EVENT**（insert）: `_sign = 1`，`_version ++`
+ **MYSQL_DELETE_ROWS_EVENT**（delete）: `_sign = -1`，`_version ++`
+ **MYSQL_UPDATE_ROWS_EVENT**（update）: 新数据 `_sign = 1`
+ **MYSQL_QUERY_EVENT**（DDL）: 支持 `CREATE TABLE` 、`DROP TABLE` 、`RENAME TABLE` 等。

### 三、MaterializeMySQL实现流程

+ MaterializeMySQL支持数据库级别的复制。

+ 当在Clickhouse中创建库级别复制后，clickhouse通过我们指定的数据库账号通过`TCP/IP`连接到数据，对数据库执行`Flush table with read lock` 并获取相关的binlog、表结构元数据信息；

元数据复制完毕后释放全局只读锁，并开始通过`select * from table_name`开始复制表数据信息。

+ 对于后续的增量数据的同步，MaterializeMySQL通过对binlog event的解析来实现的实时同步。

+ 对于DDL操作，MaterializeMySQL默认将MySQL表数据的主键作为CK表的排序键和分区键，但是由于Clickhouse与MySQL的数据定义有区别，DDL语句也会进行相应的转换。

+ 对于Update/Delete操作，MaterializeMySQL引入**version的隐藏字段，用来做版本控制，并结合sign字段标记数据的有效性**。

### 四、使用细则

**（1）DDL 查询**

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。 如果 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 子句决定。如果需要创建新的物理顺序， 请使用物化视图。

### 五、案例实操

#### 5.1  MySQL开启binlog和GTID模式

**（1）确保 MySQL 开启了 binlog 功能，且格式为 ROW**

打开`/etc/my.cnf`,在[mysqld]下添加：

```xml
server-id=1 
log-bin=mysql-bin
binlog_format=ROW
```

**（2）开启 GTID 模式**

如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本，那么 MySQL 还需要配置开启GTID模式, 这种方式在 mysql 主从模式下可以确保数据同步的一致性(主从切换时)。

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

![13.ClickHouse之MaterializeMySQL引擎01.jpg](https://lilinchao.com/usr/uploads/2022/05/1894869407.jpg)

GTID 是 MySQL 复制增强版，从 MySQL 5.6 版本开始支持，目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一ID和序号，我们可以不用关心 MySQL 集群主从拓扑结构，直接告知 MySQL 这个 GTID 即可。

**（3）重启MySQL**

```bash
sudo systemctl restart mysqld
```

#### 5.2 准备 MySQL 表和数据

**（1）在 MySQL 中创建数据表并写入数据**

```sql
-- 创建数据库testck
CREATE DATABASE testck;

-- 创建表t_organization
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());
```

![13.ClickHouse之MaterializeMySQL引擎02.jpg](https://lilinchao.com/usr/uploads/2022/05/4136667704.jpg)

**（2）创建第二张表**

```sql
-- 创建表t_user
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);
```

#### 5.3 开启 ClickHouse 物化引擎

```sql
set allow_experimental_database_materialize_mysql=1;
```

#### 5.4 创建复制管道

**（1）ClickHouse 中创建 MaterializeMySQL 数据库**

```sql
CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('192.168.159.128:3306','testck','root','123456');
```

其中4个参数分别是 MySQL 地址、databse、username 和 password。

**（2）查看 ClickHouse 的数据**

```sql
use test_binlog;
show tables;
select * from t_organization;
select * from t_user;
```

#### 5.5 修改数据

**（1）在 MySQL 中修改数据**

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

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

```sql
select * from t_organization;
```

#### 5.6 删除数据

**（1）MySQL 删除数据**

```sql
DELETE FROM t_organization where id = 2;
```

**（2）ClicKHouse，日志有 DeleteRows 的 binlog 监听事件，查看数据**

```sql
select * from t_organization;
```

**（3）在刚才的查询中增加 `_sign` 和 `_version` 虚拟字段**

```sql
select *,_sign,_version from t_organization order by _sign desc,_version desc;
```

在查询时，对于已经被删除的数据，`_sign=-1`，ClickHouse 会自动重写 SQL，将 `_sign =  -1` 的数据过滤掉;

对于修改的数据，则自动重写 SQL，为其增加 FINAL 修饰符。

```sql
select * from t_organization
等同于
select * from t_organization final where _sign = 1
```

#### 5.7 删除表

**（1）在 mysql 执行删除表**

```sql
drop table t_user;
```

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

```sql
show tables;
select * from t_user;
DB::Exception: Table scene_mms.scene doesn't exist.. 
```

**（3）mysql 新建表，clickhouse 可以查询到**

```sql
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;
```

*附参考文章：*

*《尚硅谷大数据之ClickHouse》*

标签: ClickHouse

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://www.lilinchao.com/archives/2071.html

上一篇 12.ClickHouse之物化视图

下一篇 01.NIO简单介绍

13.ClickHouse之MaterializeMySQL引擎

评论啦~

栏目分类

标签云

友情链接申请

13.ClickHouse之MaterializeMySQL引擎

 评论啦~

 栏目分类

标签云

友情链接申请

评论啦~

栏目分类

标签云

友情链接申请