clickhouse支持原生Binlog同步mysql

最新推荐文章于 2024-05-21 11:07:05 发布
最新推荐文章于 2024-05-21 11:07:05 发布
阅读量2.8k 收藏 6
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45320660/article/details/114451162
版权

为了能够监听 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处也可以实时生成对应的列

​ MaterializeMySQL database engine 不支持的情况：

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

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

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

oldba.cn
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql历史数据同步到clickhouse 已测试
06-22
mysql历史数据同步到clickhouse 已测试
MySQL至ClickHouse数据实时同步（MaterializeMySQL引擎版）
qq_36803154的博客
06-21 1110
在ClickHouse中创建MaterializeMySQL数据库aaa，指向的数据源地址为MySQL中的aaa数据库，至此ClickHouse中的aaa数据库已经和MySQL中的aaa数据库创建了连接，并且拥有MySQL那边所有的表结构和数据。INSERT INTO ccc (name, age) VALUES('张三', 18);server-id=1 #MySQL Server的id，一般为服务器IP地址尾数。update ccc set name = '李四' where id = 1;
阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
最新发布
2301_81186823的博客
05-21 567
说明：这个文件是canal的基本通用配置，canal端口号默认就是 11111，修改canal的输出model，默认tcp，改为输出到 kafka多实例配置如果创建多个实例，通过前面canal架构，我们可以知道，一个canal服务中可以有多个instance，conf/下的每一个example即是一个实例，每个实例下面都有独立的配置文件。canal可以同步整个库的所有表和单表的表，配置都在这里进行配置，如果需要同步多张表的数据，就在rdb下创建多个类似的配置文件，文件名可以用表名来命名。
clickhouse 实时同步mysql binlog
MRzhenglea的博客
10-29 1356
为了能够监听 binlog 事件，之前我们需要用到类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度。 ClickHouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据，提升数仓的查询性能和数据同步的时效性；原有...
clickhouse实时同步MySQL数据
qq_36803154的博客
05-23 3787
另外，可以通过在clickhouse创建只读权限的数据库连接账号，即可控制双向数据同步时读写权限的区分了，MySQL自身就可以配置主从，如果使用MySQL从库来和clickhouse进行连接的话，必须要控制好读写权限，MySQL从库一旦写入数据之后，等到下一次主库同步数据至从库时，必然会导致MySQL主从断开，一定注意！1、使用clickhouse表引擎，直接从MySQL中读取数据（针对表），如果业务需求不是很复杂，可以选择此方式，需要哪张表就配置哪张表，操作简单，数据实时同步；4、在MySQL中查询。
clickhouse从mysql同步数据到clickhouse的几种方式
lixia0417mul2的博客
12-05 1677
从mysql同步数据到ck的几种方式
ClickHouse同步MySQL数据
赵广陆
04-22 2588
目录 1 概述 1.1 特点 1.2 使用细则 2 案例实操 2.1 MySQL 开启 binlog 和 GTID 模式 2.2 准备 MySQL 表和数据 2.3 开启 ClickHouse 物化引擎 2.4 创建复制管道 2.5 修改数据 2.6 删除数据 2.7 删除表 1 概述 MySQL 的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用 binlog 将数据写入到 ClickHouse。
horgh-replicator：从MySQL到MySQL，PostgreSQL，Vertica，Clickhouse的Golang Binlog复制
02-05
MySQL Binlog复制器基于现场查看完整的文档大师类型MySQL从机类型MySQL PostgreSQLYandex ClickHouse 惠普Vertica快速开始查看快速入门教程测验将src/.env.dist复制到src/.env并设置凭据。 将您MySQL主服务器配置为...
MySQL Binlog Digger 4.28 + Mysql Binlog分析 + 数据库
08-19
MySQL Binlog Digger是一款免费的，且基于图形界面的binlog挖掘分析工具与sql审计工具。当发生误删、误增、误改时，它可以帮助我们从binlog中快速定位到误操作的重做语句(redo sql)，同时推理出回滚语句(undo sql)。...
binlogportal:mysql binlog同步工具
05-26
binlogportalA simple mysql binlog sync toolIt has following features:1.Store binlog postion use redis2.Support springboot starter3.Easy to use and deploy项目简介一个轻量级的mysql binlog同步工具。...
mysql replication binlog php 同步工具
04-07
mysql replication binlog php 同步工具 运行环境 目前只支持数据库utf8编码 php版本>=5.4 mysql版本>=5.1 需要安装php sockets扩展 运行用户需要有创建文件的权限
8、NIFI综合应用场景-NiFi监控MySQL binlog进行实时同步到hive
05-29
8、NIFI综合应用场景-NiFi监控MySQL binlog进行实时同步到hive 网址：https://blog.csdn.net/chenwewi520feng/article/details/130620988 本文旨在介绍通过实时监控mysql的binlog实现数据的实时同步至hive中。但由于...
mysql通过binlog同步数据到redis的方法.docx
01-10
mysql通过binlog同步数据到redis的方法 本文档介绍了使用mysql的binlog机制同步数据到redis的方法。该方法通过模仿mysql的slave角色，从master获取数据库变化的数据，写入activeMQ，再同步到redis，实现数据同步的...
canal数据binlog同步demo
04-07
"canal数据binlog同步demo" 是一个关于使用Canal工具实现MySQL数据库binlog（二进制日志）同步的实例展示。Canal是阿里巴巴开源的一个数据库实时增量数据订阅与推送组件，它能监听MySQL的binlog事件，并将其转化为...
MySQL 自动清理binlog日志的方法
09-09
主要介绍了MySQL 自动清理binlog日志的方法,需要的朋友可以参考下
MySQL Binlog同步HDFS的方案
01-20
这个问题我想只要是在做数据开发的，有一定数据实时性要求、需要做数据的增量同步的公司都会遇到。 19年的时候我曾经写过一点canal的文章。 现在你只要看这个文章就可以了。 这篇文章是一个读者推荐给我的，原地址：...
mysql数据同步到clickhouse引擎——MaterializedMySQL
JhonDavis的博客
06-25 801
如果在SELECT查询中没有指定_version，则 [FINAL](/docs/zh/sql-reference/statements/select/from#select-from- FINAL)修饰符被使用，所以只有带有 MAX(_version)的行会返回每个主键值。max_bytes_in_buffer - 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。max_rows_in_buffers - 允许在内存中缓存数据的最大行数(用于数据库和无法查询的缓存数据)。
mysql binlog 同步组件_腾讯大牛教你ClickHouse实时同步MySQL数据
weixin_32233079的博客
02-01 1215
文章转载自 腾讯云数据库| 作者 史鹏宙，CSIG云与智慧产业事业群研发工程师ClickHouse作为OLAP分析引擎已经被广泛使用，数据的导入导出是用户面临的第一个问题。由于ClickHouse本身无法很好地支持单条大批量的写入，因此在实时同步数据方面需要借助其他服务协助。本文给出一种结合Canal+Kafka的方案，并且给出在多个MySQL实例分库分表的场景下，如何将多张MySQL数据表写入...
MySQL 到 ClickHouse 实时数据同步实操分享，Python开发者值得深入思考的几个问题
2401_84103675的博客
04-04 1396
4.在打开的连接信息配置页面依次输入需要的配置信息，配置完成后测试连接保存即可。第三步：选择同步模式-全量/增量/全+增进入Tapdata Cloud 操作后台任务管理页面，点击添加任务按钮进入任务设置流程根据刚才建好的连接，选定源端与目标端。根据数据需求，选择需要同步的库、表，如果你对表名有修改需要，可以通过页面中的表名批量修改功能对目标端的表名进行批量设置.在以上选项设置完毕后，下一步选择同步类型，平台提供全量同步、增量同步、全量+增量同步，设定写入模式和读取数量。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值