详解 ClickHouse 的 MaterializeMySQL 引擎-易微帮

注意与 ClickHouse 的 MySQL 表引擎区分开

一、概述

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

1. 特点

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

2. 使用细则

DDL 查询：MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME），如果 ClickHouse 不能解析某些 DDL 查询，该查询将被忽略
数据复制：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 子句决定。如果需要创建新的物理顺序，请使用物化视图。

二、案例实操

1. Mysql 配置

sudo vim /etc/my.cnf

#开启 binlog，且格式必须为 ROW 
server-id=1
log-bin=mysql-bin
binlog_format=ROW

#开启 GTID 模式，可以保证主从切换时的数据同步一致性
gtid-mode=on
enforce-gtid-consistency=1 #设置为主从强一致性
log-slave-updates=1 #记录日志

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


sudo systemctl restart mysqld

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);

3. 开启 MaterializeMySQL 引擎

--clickhouse client
set allow_experimental_database_materialize_mysql=1;

4. 创建复制管道

--ClickHouse 中创建 MaterializeMySQL 数据库
CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('hadoop1:3306','testck','root','000000');
--4 个参数分别是 MySQL 地址、databse、username 和 password。

--查看该数据库的数据
use test_binlog;

show tables;

select * from t_organization;
select * from t_user;

5. 修改数据

在 MySQL 中修改数据

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

在 ClickHouse 中查询对应表的数据

select * from t_organization; --对于修改的数据，会自动重写 SQL，为其增加 FINAL 修饰符

--查看版本号和删除标记，原数据还是存在
select *,_sign,_version from t_organization order by _sign desc,_version desc;

6. 删除数据

在 MySQL 中删除数据

DELETE FROM t_organization where id = 2;

在 ClickHouse 中查询对应表的数据

select * from t_organization; --对于已经被删除的数据，_sign=-1，会自动重写 SQL，将 _sign=-1 的数据过滤掉

--查看版本号和删除标记，数据没有被真正删除
select *,_sign,_version from t_organization order by _sign desc,_version desc;

7. 删除表

将 Mysql 中的表删除
```
drop table t_user;
```
在 ClickHouse 中查看表
```
show tables; --表也被同步删除
```

详解 ClickHouse 的 MaterializeMySQL 引擎