ClickHouse王炸功能来袭之同步Binlog

ClickHouse周边 2021-03-05

1728

为了能够监听 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 测试实例，配置my.cnf

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版本信息(目前支持mysql5.6,5.7,8.0)
select version() ;
+------------+
| version()  |
+------------+
| 5.7.28-log |
+------------+

在 MySQL 中创建数据表并写入数据

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;
 
begin;
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 中创建映射表并检查数据同步情况

--查询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处也可以实时生成对应的列

MaterializeMySQL database engine 不支持的情况：

1.MySQL中修改表名，ClickHouse不会同步，且查询报错；

2.修改列名称也是不支持的，如果该这种情况，需要删除通道重建；

数据库 clickhouse

文章转载自ClickHouse周边，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

ClickHouse王炸功能来袭之同步Binlog

评论