clickhouse 实时同步mysql binlog

为了能够监听 binlog 事件，之前我们需要用到类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度。

ClickHouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据，提升数仓的查询性能和数据同步的时效性；原有mysql中承担的数据分析工作可交由clickhouse去做，这么做可显著降低线上mysql的负载，从此OLTP与OLAP业务实现完美融合。

新功能采用 MaterializeMySQL 的 database 引擎，该 database 能映射到 MySQL 中的某个 database，并自动在 ClickHouse 中创建对应的 ReplacingMergeTree。

MaterializeMySQL database engine 支持的情况：

1.支持mysql 库级别的数据同步，暂不支持表级别的。
2.MySQL 库映射到clickhouse中自动创建为ReplacingMergeTree 引擎的表。
3.支持全量和增量同步，首次创建数据库引擎时进行一次全量复制，之后通过监控binlog变化进行增量数据同步。
4.支持的操作:insert，update，delete，alter，create，drop，truncate等大部分DDL操作。
5.支持的MySQL复制为GTID复制。

MaterializeMySQL 同步流程：

1.创建MySQL的映射表，在MaterializeMySQL引擎中填写MySQL连接信息(ip地址+端口+数据库名称+用户名+密码)，ClickHouse会创建一个引擎为ReplacingMergeTree 的数据表,其中MySQL表的 PRIMARY KEY 作为了 ReplacingMergeTree 的 PARTITION BY，并且按照类型大小除以1000整除;
2.此时ClickHouse会拉取MySQL对象库下的所有表结构，并且添加_sign和_version字段，_sign字段表示数据是否删除，_version字段表示数据最新版本；
3.首次同步采用全量同步MySQL中的表数据，_version版本全为1；
4.后续采用增量同步消费binlog中的SQL,当ClickHouse监听到 insert、update 和 delete 事件时，利用_version号在 databse 内全局自增记录最新数据，当ClickHouse同步到delete语句时，更新_sign字段的值为-1（此时，熟悉MySQL的肯定会想到伪删除，对没错，它就是利用伪删除）。

目前 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等。

搭建流程：

--添加mysql配置文件
vim /etc/my.cnf

server_id = 66
binlog_format = ROW
log_bin = /data/3306/binlog/mysql-bin
gtid-mode = on
enforce-gtid-consistency = 1 # 设置为主从强一致性
log-slave-updates = 1 # 记录日志

–查询mysql版本信息
select version() ;
+------------+
| version() |
+------------+
| 5.7.28-log |
+------------+

–创建测试库、表
create database clickhouse_test;
use clickhouse_test;

CREATE TABLE scene (
id int NOT NULL AUTO_INCREMENT,
code int NOT NULL,
title text DEFAULT NULL,
updatetime datetime DEFAULT NULL,
PRIMARY KEY (id), ## 主键要设置为not null，否则ClickHouse同步会报错
KEY idx_code (code) ## 索引键也要设置为not null，否则ClickHouse同步会报错
) ENGINE=InnoDB default charset=Latin1;

show tables;
–插入数据
INSERT INTO scene(code, title, updatetime) VALUES(1001,‘aaa’,NOW());
INSERT INTO scene(code, title, updatetime) VALUES(1002,‘bbb’,NOW());
INSERT INTO scene(code, title, updatetime) VALUES(1003,‘ccc’,NOW());
INSERT INTO scene(code, title, updatetime) VALUES(1004,‘ddd’,NOW());
commit;

–查询ClickHouse版本信息
SELECT version()
┌─version()─┐
│ 20.8.3.18 │
└───────────┘

SET allow_experimental_database_materialize_mysql = 1
–该功能目前还处于实验阶段，在使用之前需要开启
select * from system.settings where name =‘allow_experimental_database_materialize_mysql’;

–创建一个复制管道
CREATE DATABASE clickhouse_mysql
ENGINE = MaterializeMySQL(‘127.0.0.1:3306’, ‘clickhouse_test’, ‘root’, ‘xxxxxxx’)

SHOW DATABASES;
USE clickhouse_mysql;
SHOW TABLES;

SELECT * FROM scene;
┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 1 │ 1001 │ aaa │ 2021-02-23 15:18:18 │
│ 2 │ 1002 │ bbb │ 2021-02-23 15:18:23 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │
└────┴──────┴───────┴─────────────────────┘

–尝试更新mysql表中数据，ClickHouse数据变化：_sign = 1 , _version ++
mysql> update scene set title=‘abc’ where code=1001;
mysql> select * from scene;
+----±-----±------±--------------------+
| id | code | title | updatetime |
+----±-----±------±--------------------+
| 1 | 1001 | abc | 2021-02-23 15:18:18 |
| 2 | 1002 | bbb | 2021-02-23 15:18:23 |
| 3 | 1003 | ccc | 2021-02-23 15:18:29 |
| 4 | 1004 | ddd | 2021-02-23 15:18:34 |
+----±-----±------±--------------------+

SELECT * FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │
| 2 | 1002 | bbb | 2021-02-23 15:18:23 |
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │
└────┴──────┴───────┴─────────────────────┘

SELECT *,_version,_sign FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 1 │ 1001 │ aaa │ 2021-02-23 15:18:18 │ 1 │ 1 │
│ 2 │ 1002 │ bbb │ 2021-02-23 15:18:23 │ 1 │ 1 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │ 1 │ 1 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │ 1 │ 1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │ 2 │ 1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘

–尝试删除mysql表中数据，ClickHouse数据变化: _sign = -1 , _version ++
mysql> delete from scene where code=1002;
mysql> select * from scene;
+----±-----±------±--------------------+
| id | code | title | updatetime |
+----±-----±------±--------------------+
| 1 | 1001 | abc | 2021-02-23 15:18:18 |
| 3 | 1003 | ccc | 2021-02-23 15:18:29 |
| 4 | 1004 | ddd | 2021-02-23 15:18:34 |
+----±-----±------±--------------------+

SELECT * FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │
└────┴──────┴───────┴─────────────────────┘

SELECT *,_version,_sign FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 1 │ 1001 │ aaa │ 2021-02-23 15:18:18 │ 1 │ 1 │
│ 2 │ 1002 │ bbb │ 2021-02-23 15:18:23 │ 1 │ 1 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │ 1 │ 1 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │ 1 │ 1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │ 2 │ 1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 2 │ 1002 │ bbb │ 2021-02-23 15:18:23 │ 3 │ -1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘

-----------------------------------------------------------------------
ClickHouse 支持更新和删除，但是性能之差；MySQL修改、删除之后ClickHouse怎么做的？

SELECT FROM scene;
等同于
select from scene final where _sign = 1;

修改的数据用final去重；
删除的数据用_sign = 1 过滤；
-----------------------------------------------------------------------

–尝试追加mysql表中数据，ClickHouse数据变化：_sign = 1 , _version ++
mysql> INSERT INTO scene(code, title, updatetime) VALUES(1005,‘eee’,NOW());
mysql> INSERT INTO scene(code, title, updatetime) VALUES(1006,‘fff’,NOW());
mysql> INSERT INTO scene(code, title, updatetime) VALUES(1007,‘ggg’,NOW());
mysql> INSERT INTO scene(code, title, updatetime) VALUES(1008,‘hhh’,NOW());
mysql> select * from scene;
+----±-----±------±--------------------+
| id | code | title | updatetime |
+----±-----±------±--------------------+
| 1 | 1001 | abc | 2021-02-23 15:18:18 |
| 3 | 1003 | ccc | 2021-02-23 15:18:29 |
| 4 | 1004 | ddd | 2021-02-23 15:18:34 |
| 5 | 1005 | eee | 2021-02-23 16:05:23 |
| 6 | 1006 | fff | 2021-02-23 16:06:34 |
| 7 | 1007 | ggg | 2021-02-23 16:06:34 |
| 8 | 1008 | hhh | 2021-02-23 16:06:35 |
+----±-----±------±--------------------+

select from scene;
┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │
│ 5 │ 1005 │ eee │ 2021-02-23 16:05:23 │
│ 6 │ 1006 │ fff │ 2021-02-23 16:06:34 │
│ 7 │ 1007 │ ggg │ 2021-02-23 16:06:34 │
│ 8 │ 1008 │ hhh │ 2021-02-23 16:06:35 │
└────┴──────┴───────┴─────────────────────┘
select , _version,_sign from scene;
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│ 1 │ 1001 │ abc │ 2021-02-23 15:18:18 │ 2 │ 1 │
│ 2 │ 1002 │ bbb │ 2021-02-23 15:18:23 │ 3 │ -1 │
│ 3 │ 1003 │ ccc │ 2021-02-23 15:18:29 │ 1 │ 1 │
│ 4 │ 1004 │ ddd │ 2021-02-23 15:18:34 │ 1 │ 1 │
│ 5 │ 1005 │ eee │ 2021-02-23 16:05:23 │ 4 │ 1 │
│ 6 │ 1006 │ fff │ 2021-02-23 16:06:34 │ 5 │ 1 │
│ 7 │ 1007 │ ggg │ 2021-02-23 16:06:34 │ 6 │ 1 │
│ 8 │ 1008 │ hhh │ 2021-02-23 16:06:35 │ 7 │ 1 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘

–在MySQL中执行删除表，ClickHouse也会删除表：
drop table scene

# 此时在clickhouse处会同步删除对应表，如果查询会报错
DB::Exception: Table scene_mms.scene doesn’t exist..

–在mysql客户端新增一张表，clickhouse处也可以实时生成对应的数据表
–在mysql客户端添加列与删除列，clickhouse处也可以实时生成对应的列

原文链接
https://blog.csdn.net/weixin_45320660/article/details/114451162