ClickHouse MaterializeMySQL引擎

本文及后续所有文章都以 21.7.3.14-2 做为版本讲解和入门学习

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

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

# 特点

MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步。
MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段。其中，_version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 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
MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。

# 细则

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，即返回未删除状态（_sign=1) 的数据。
索引转换
ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。
ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图。

# 使用

首先要给你的 mysql 开启 binlog，在 /etc/my.cnf 的 [mysqld] 下添加

# 服务id 唯一
server-id=1
# 日志记录
log-bin=mysql-bin
# 只能是ROW
binlog_format=ROW

1
2
3
4
5
6

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

# 使用mysql GTID模式
gtid-mode=on
# 设置为主从强一致性
enforce-gtid-consistency=1
# 记录日志
log-slave-updates=1

1
2
3
4
5
6

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

修改完毕后重启 mysql.

开启 ClickHouse 物化引擎

set allow_experimental_database_materialize_mysql=1;

ClickHouse 中创建 MaterializeMySQL 数据库

# 创建clickhouse的库
CREATE DATABASE 库名 ENGINE = MaterializeMySQL('localhost:3306','库名称','账号','密码');
# 查询库
show databases;
use 库名
# 查看库所有表
show tables;

1
2
3
4
5
6
7

对 mysql 的表的所有增删改都会同步到 clickhouse 的表，包括建表删表改表。他的修改和删除完全靠 clickhouse 建表时自带的 _sign 和 _version，我们可以查表时候把这两个字段显示的指定出来。

select *,_sign,_version from  表名 order by _sign desc , _version  desc;

上次更新: 6/11/2025, 4:10:30 PM