clickhouse支持原生Binlog同步mysql

为了能够监听 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─┐
│  11001 │ aaa   │ 2021-02-23 15:18:18 │
│  21002 │ bbb   │ 2021-02-23 15:18:23 │
│  31003 │ ccc   │ 2021-02-23 15:18:29 │
│  41004 │ 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─┐
│  11001 │ abc   │ 2021-02-23 15:18:18|  2 | 1002 | bbb   | 2021-02-23 15:18:23 |31003 │ ccc   │ 2021-02-23 15:18:29 │
│  41004 │ ddd   │ 2021-02-23 15:18:34 │
└────┴──────┴───────┴─────────────────────┘

SELECT *,_version,_sign FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  11001 │ aaa   │ 2021-02-23 15:18:1811 │
│  21002 │ bbb   │ 2021-02-23 15:18:2311 │
│  31003 │ ccc   │ 2021-02-23 15:18:2911 │
│  41004 │ ddd   │ 2021-02-23 15:18:3411 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  11001 │ abc   │ 2021-02-23 15:18:1821 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘


--尝试删除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─┐
│  11001 │ abc   │ 2021-02-23 15:18:18 │
│  31003 │ ccc   │ 2021-02-23 15:18:29 │
│  41004 │ ddd   │ 2021-02-23 15:18:34 │
└────┴──────┴───────┴─────────────────────┘

SELECT  *,_version,_sign FROM scene
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  11001 │ aaa   │ 2021-02-23 15:18:1811 │
│  21002 │ bbb   │ 2021-02-23 15:18:2311 │
│  31003 │ ccc   │ 2021-02-23 15:18:2911 │
│  41004 │ ddd   │ 2021-02-23 15:18:3411 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  11001 │ abc   │ 2021-02-23 15:18:1821 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  21002 │ bbb   │ 2021-02-23 15:18:233-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─┐
│  11001 │ abc   │ 2021-02-23 15:18:18 │
│  31003 │ ccc   │ 2021-02-23 15:18:29 │
│  41004 │ ddd   │ 2021-02-23 15:18:34 │
│  51005 │ eee   │ 2021-02-23 16:05:23 │
│  61006 │ fff   │ 2021-02-23 16:06:34 │
│  71007 │ ggg   │ 2021-02-23 16:06:34 │
│  81008 │ hhh   │ 2021-02-23 16:06:35 │
└────┴──────┴───────┴─────────────────────┘
select *, _version,_sign  from scene;
┌─id─┬─code─┬─title─┬──────────updatetime─┬─_version─┬─_sign─┐
│  11001 │ abc   │ 2021-02-23 15:18:1821 │
│  21002 │ bbb   │ 2021-02-23 15:18:233-1 │
│  31003 │ ccc   │ 2021-02-23 15:18:2911 │
│  41004 │ ddd   │ 2021-02-23 15:18:3411 │
│  51005 │ eee   │ 2021-02-23 16:05:2341 │
│  61006 │ fff   │ 2021-02-23 16:06:3451 │
│  71007 │ ggg   │ 2021-02-23 16:06:3461 │
│  81008 │ hhh   │ 2021-02-23 16:06:3571 │
└────┴──────┴───────┴─────────────────────┴──────────┴───────┘


--在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.修改列名称也是不支持的,如果该这种情况,需要删除通道重建;

详情了解更多欢迎关注公众号
在这里插入图片描述

  • 1
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值