ClickHouse学习笔记之MaterializeMySQL引擎

概述

MySQL用户群体很大，为了增强数据的实时性，很多解决方案会利用binlog将数据写入到ClickHouse。为了能够监听binlog事件，我们需要利用类似canal这样的中间件，但这样会增加系统的复杂度。
ClickHouse20.8.2.3版本新增了MaterializeMySQL这一数据库引擎，该引擎能够把ClickHouse中某个数据库映射到MySQL中的某个数据库，并自动在ClickHouse中创建对应的ReplacingMergeTree。ClickHouse以此作为MySQL的副本，读取Binlog并执行DDL和DML，实现基于MySQL Binlog机制的业务数据库实时同步功能。

特点

• MaterializeMySQL支持全量和增量同步，在数据库创建之初会全量同步MySQL中的表和数据，之后则会通过binlog进行增量同步；
• MaterializeMySQL数据库为其所创建的每张ReplacingMergeTree表自动增加_sig和_version字段：前者用于标记数据是否被删除(-1为已删除，1为没有删除)；后者用作ReplacingMergeTree的版本参数，每当监听到对某行数据的增删改操作时，该行数据对应的_version字段在数据库内全局自增。

目前，MaterializeMySQL支持以下四种binlog事件：

1. MYSQL_WRITE_ROWS_EVENT：_sign=1, _version++;
   2.MYSQL_DELETE_ROWS_EVENT：_sign=-1, _version++;
2. MYSQL_UPDATE_ROWS_EVENT：_sign=1, 新数据
3. MYSQL_QUERY_ROWS_EVENT：支持对表的创建、删除、重命名等;

使用细则

DDL查询

MySQL DDL查询会被转换成相应的ClickHouse DDL查询(alter、create、drop、rename)，如果某DDL不能被ClickHouse解析，该查询将会被忽略；

数据复制

MaterializeMySQL不支持直接插入、删除和更新查询，而是将DDL语句进行相应转换：
MySQL insert会被转换成insert with _sign=1；
MySQL delete会被转换成insert with _sign=-1;
MySQL update会被转换成insert with sign = 1和insert with sign=-1。

select查询

如果select查询中没有指定_version，则会使用final修饰符，以返回_version最大值对应的数据，即最新数据；
如果select查询中没有指定_sign，则默认使用where _sign=1，即返回未删除状态的数据

索引转换

ClickHouse数据库表会自动将MySQL主键和索引子句转换成order by语句。ClickHouse只有一个物理顺序，且由order by子句决定。如果需要创建新的物理顺序，则需使用物化视图。

案例

配置MySQL-Windows

打开MySQL配置文件my.ini，开启binlog，且格式为row：

server-id=1
log-bin=mysql-bin
binlog-format=Row
1
2
3

开启GTID模式：

gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1
1
2
3

保存文件，重启MySQL：计算机管理->服务和应用程序->服务->找到MySQL，右击重启：

在MySQL中创建数据库、表和数据：

mysql> create database testck;
Query OK, 1 row affected (0.00 sec)

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO testck.t_organization (code, name,updatetime)
    -> VALUES(1000,'Realinsight',NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO testck.t_organization (code, name,updatetime)
    -> VALUES(1001, 'Realindex',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testck.t_organization (code, name,updatetime)
    -> VALUES(1002,'EDT',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `testck`.`t_user` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `code` int,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO testck.t_user (code) VALUES(1);
Query OK, 1 row affected (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

ClickHouse端

首先开启MySQL物化引擎：

scentos :) set allow_experimental_database_materialized_mysql=1;
1

然后创建复制管道：

scentos :) CREATE DATABASE test_binlog ENGINE =
           MaterializeMySQL('192.168.31.60:3306','testck','root','root');
1
2

参数分别是：ip:端口、远程数据库名、用户名和密码；
在ClickHouse端查看MySQL数据：

scentos :) use test_binlog;

USE test_binlog

Query id: c6aabf92-035c-4588-98e5-b9b729e7f6a7

Ok.

0 rows in set. Elapsed: 0.001 sec.

scentos :) show tables;

SHOW TABLES

Query id: a3abdde3-a54e-4383-88d0-d5ee928ad995

┌─name───────────┐
│ t_organization │
│ t_user         │
└────────────────┘

2 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

至此，MySQL和ClickHouse双端配置成功。

数据的修改和删除

MySQL端修改数据：

mysql> update t_organization set name = CONCAT(name,'-v1') where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3

ClickHouse端查看：

scentos :) select * from t_organization;

SELECT *
FROM t_organization

Query id: 49b13240-482a-4485-b832-70d31badfcae

┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│  1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │
│  2 │ 1001 │ Realindex      │ 2021-12-12 15:07:32 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│  3 │ 1002 │ EDT  │ 2021-12-12 15:07:39 │
└────┴──────┴──────┴─────────────────────┘

3 rows in set. Elapsed: 0.004 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

MySQL端删除数据：

mysql> DELETE FROM t_organization where id = 2;
Query OK, 1 row affected (0.00 sec)
ClickHouse端查看：

scentos :) select * from t_organization;

SELECT *
FROM t_organization

Query id: 00319dc9-8703-4d57-9ccb-26696f08b93e

┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│  1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│  3 │ 1002 │ EDT  │ 2021-12-12 15:07:39 │
└────┴──────┴──────┴─────────────────────┘

2 rows in set. Elapsed: 0.003 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

在方才的查询中增加_sign和_version虚拟字段：

scentos :) select *,_sign,_version from t_organization order by _sign
:-] desc,_version desc;

SELECT
    *,
    _sign,
    _version
FROM t_organization
ORDER BY
    _sign DESC,
    _version DESC

Query id: 236f35b3-8652-4228-9a4a-d65d5c7fc6cb

┌─id─┬─code─┬─name───────────┬──────────updatetime─┬─_sign─┬─_version─┐
│  1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │     1 │        2 │
└────┴──────┴────────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name────────┬──────────updatetime─┬─_sign─┬─_version─┐
│  1 │ 1000 │ Realinsight │ 2021-12-12 15:07:24 │     1 │        1 │
│  2 │ 1001 │ Realindex   │ 2021-12-12 15:07:32 │     1 │        1 │
│  3 │ 1002 │ EDT         │ 2021-12-12 15:07:39 │     1 │        1 │
└────┴──────┴─────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name──────┬──────────updatetime─┬─_sign─┬─_version─┐
│  2 │ 1001 │ Realindex │ 2021-12-12 15:07:32 │    -1 │        3 │
└────┴──────┴───────────┴─────────────────────┴───────┴──────────┘

5 rows in set. Elapsed: 0.006 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

对于已经被删除的数据，_sign=-1，ClickHouse会自动重写SQL，将_sign为-1的数据过滤掉。如果涉及到修改的数据，ClickHouse会为SQL加上final修饰符，并将SQL重写成如下形式：

select * from t_organization final where _sign = 1;
1

表的删除和增加

MySQL端删除一张表：

mysql> drop table t_user;
Query OK, 0 rows affected (0.01 sec)
1
2

ClickHouse端可以看到：

scentos :) show tables;

SHOW TABLES

Query id: cd1e8bb0-a07f-43ba-a0a1-0fcd71d75e4d

┌─name───────────┐
│ t_organization │
└────────────────┘

1 rows in set. Elapsed: 0.002 sec.
1
2
3
4
5
6
7
8
9
10
11

MySQL端新建表和数据：

mysql> CREATE TABLE `testck`.`t_user` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `code` int,
    ->  PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO testck.t_user (code) VALUES(1);
Query OK, 1 row affected (0.01 sec)
1
2
3
4
5
6
7
8
9

ClickHouse端同样可以看到：

scentos :) show tables;

SHOW TABLES

Query id: aaedc66f-fec6-48fd-a23d-274c0f6a9ae9

┌─name───────────┐
│ t_organization │
│ t_user         │
└────────────────┘

2 rows in set. Elapsed: 0.002 sec.

scentos :) select * from t_user;

SELECT *
FROM t_user

Query id: 938aa8ec-2fda-4e12-b842-1dca83fa38c3

┌─id─┬─code─┐
│  1 │    1 │
└────┴──────┘

1 rows in set. Elapsed: 0.004 sec.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25