Java技术债务Java技术债务

  •  首页
  •  分类
  •  归档
  •  标签
  • 博客日志
  • 资源分享
  •  友链
  •  关于本站
注册
登录

ClickHouse之MaterializeMySQL引擎(十)

ClickHouse,数据库

文章目录


利用 binlog 将数据写入到 ClickHouse。

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

1 特点

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

2 使用细则

(1)DDL 查询

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询(ALTER, CREATE, DROP, RENAME)。如果 ClickHouse 不能解析某些 DDL 查询,该查询将被忽略。

(2)数据复制

MaterializeMySQL 不支持直接插入、删除和更新查询,而是将 DDL 语句进行相应转换:

  • MySQL INSERT 查询被转换为 INSERT with _sign=1。
  • MySQL DELETE 查询被转换为 INSERT with _sign=-1。
  • MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。

(3)SELECT 查询

如果在 SELECT 查询中没有指定_version,则使用 FINAL 修饰符,返回_version 的最大值对应的数据,即最新版本的数据。如果在 SELECT 查询中没有指定_sign,则默认使用 WHERE _sign=1,即返回未删除状态(_sign=1)的数据。

(4)索引转换

ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。ClickHouse 只有一个物理顺序,由 ORDER BY 子句决定。如果需要创建新的物理顺序, 请使用物化视图。

3 物化MySQL实操

1 MySQL 开启 binlog 和 GTID 模式

(1) 确保 MySQL 开启了 binlog 功能,且格式为 ROW

打开/etc/my.cnf,在[mysqld]下添加:

server-id=1
log-bin=mysql-bin
binlog_format=ROW

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

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

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

sudo systemctl restart mysqld

2 准备 MySQL 表和数据

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

CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int NOT NULL,
  `name` text DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1002,'EDT',NOW());

4 开启 ClickHouse 物化引擎

set allow_experimental_database_materialize_mysql=1;

5 创建复制管道

1)ClickHouse 中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE =
MaterializeMySQL('locahost:3306','testck','root','root');

其中 4 个参数分别是 MySQL 地址、databse、username 和 password。

2)查看 ClickHouse 的数据

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;

在MySQL操作增删改查,在ClickHouse查询会发现和MySQL是一样的效果。

6 删除数据

(1)MySQL 删除数据:

DELETE FROM t_organization where id = 2;

(2)ClicKHouse,日志有 DeleteRows 的 binlog 监听事件,查看数据:

 select * from t_organization;

(3)在刚才的查询中增加 _sign 和 _version 虚拟字段

select *,_sign,_version from t_organization order by _sign desc,_version desc;

ClickHouse之MaterializeMySQL引擎(十) - Java技术债务

在查询时,对于已经被删除的数据,_sign=-1,ClickHouse 会自动重写 SQL,将 _sign = -1 的数据过滤掉;

对于修改的数据,则自动重写 SQL,为其增加 FINAL 修饰符。

select * from t_organization
等同于
select * from t_organization final where _sign = 1

7 表操作也会同步到ClickHouse

在MySQL对表操作也会同步到CK,如:

  • 删除MySQL的表,在CK查询会报错,查询表不存在
  • 新建MySQL表,在CK也能立刻查询到新建的MySQL表

利用 binlog 将数据写入到 ClickHouse。

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

1 特点

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

2 使用细则

(1)DDL 查询

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询(ALTER, CREATE, DROP, RENAME)。如果 ClickHouse 不能解析某些 DDL 查询,该查询将被忽略。

(2)数据复制

MaterializeMySQL 不支持直接插入、删除和更新查询,而是将 DDL 语句进行相应转换:

  • MySQL INSERT 查询被转换为 INSERT with _sign=1。
  • MySQL DELETE 查询被转换为 INSERT with _sign=-1。
  • MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。

(3)SELECT 查询

如果在 SELECT 查询中没有指定_version,则使用 FINAL 修饰符,返回_version 的最大值对应的数据,即最新版本的数据。如果在 SELECT 查询中没有指定_sign,则默认使用 WHERE _sign=1,即返回未删除状态(_sign=1)的数据。

(4)索引转换

ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。ClickHouse 只有一个物理顺序,由 ORDER BY 子句决定。如果需要创建新的物理顺序, 请使用物化视图。

3 物化MySQL实操

1 MySQL 开启 binlog 和 GTID 模式

(1) 确保 MySQL 开启了 binlog 功能,且格式为 ROW

打开/etc/my.cnf,在[mysqld]下添加:

server-id=1
log-bin=mysql-bin
binlog_format=ROW

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

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

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

sudo systemctl restart mysqld

2 准备 MySQL 表和数据

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

CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int NOT NULL,
  `name` text DEFAULT NULL,
  `updatetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1002,'EDT',NOW());

4 开启 ClickHouse 物化引擎

set allow_experimental_database_materialize_mysql=1;

5 创建复制管道

1)ClickHouse 中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE =
MaterializeMySQL('locahost:3306','testck','root','root');

其中 4 个参数分别是 MySQL 地址、databse、username 和 password。

2)查看 ClickHouse 的数据

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;

在MySQL操作增删改查,在ClickHouse查询会发现和MySQL是一样的效果。

6 删除数据

(1)MySQL 删除数据:

DELETE FROM t_organization where id = 2;

(2)ClicKHouse,日志有 DeleteRows 的 binlog 监听事件,查看数据:

 select * from t_organization;

(3)在刚才的查询中增加 _sign 和 _version 虚拟字段

select *,_sign,_version from t_organization order by _sign desc,_version desc;

ClickHouse之MaterializeMySQL引擎(十) - Java技术债务

在查询时,对于已经被删除的数据,_sign=-1,ClickHouse 会自动重写 SQL,将 _sign = -1 的数据过滤掉;

对于修改的数据,则自动重写 SQL,为其增加 FINAL 修饰符。

select * from t_organization
等同于
select * from t_organization final where _sign = 1

7 表操作也会同步到ClickHouse

在MySQL对表操作也会同步到CK,如:

  • 删除MySQL的表,在CK查询会报错,查询表不存在
  • 新建MySQL表,在CK也能立刻查询到新建的MySQL表
完
  • 本文作者:Java技术债务
  • 原文链接: https://cuizb.top/myblog/article/1640446661
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。
阅读全文
Java技术债务

Java技术债务

Java技术债务
Java技术债务
热门文章
  1. ClickHouse使用过程中的一些查询优化(六)2003
  2. MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据764
  3. MySQL主从同步原理458
  4. 线程池的理解以及使用414
  5. Spring Cloud Gateway整合nacos实战(三)409
分类
  • Java
    30篇
  • 设计模式
    27篇
  • 数据库
    20篇
  • Spring
    18篇
  • MySQL
    13篇
  • ClickHouse
    11篇
  • Kubernetes
    10篇
  • Redis
    9篇
  • Docker
    8篇
  • SpringBoot
    7篇
  • JVM
    6篇
  • Linux
    5篇
  • Spring Cloud
    5篇
  • 多线程
    5篇
  • Netty
    4篇
  • Kafka
    4篇
  • 面经
    4篇
  • Nginx
    3篇
  • JUC
    3篇
  • 随笔
    2篇
  • 分布式
    1篇
  • MyBatis
    1篇
  • 报错合集
    1篇
  • 生活记录
    1篇
  • 源码
    1篇
  • 性能优化
    1篇

最新评论

  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-05-06
    Java技术债务:@capture 一起探讨学习,服务器被黑很正常,及时做好备份以及做好防护
  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-04-13
    capture:我的刚上线两天,网站里就两篇文章也被攻击了,纳闷
  • Java常用集合List、Map、Set介绍以及一些面试问题2022-01-18
    Java技术债务:HashSet和TreeSet 相同点:数据不能重复 不同点: 1、底层存储结构不同; HashSet底层使用HashMap哈希表存储 TreeSet底层使用TreeMap树结构存储 2、唯一性方式不同 HashSet底层使用hashcode()和equal()方法判断 TreeSet底层使用Comparable接口的compareTo判断的 3、HashSet无序,TreeSet有序
  • undefined2021-12-14
    Java技术债务:如果不指定线程池,CompletableFuture会默认使用ForkJoin线程池,如果同一时间出现大量请求的话,会出现线程等待问题,建议使用自定义线程池。。。
  • undefined2021-12-02
    you:很好,对于小白相当不错了,谢谢
  • CSDN
  • 博客园
  • 程序猿DD
  • 纯洁的微笑
  • spring4all
  • 廖雪峰的官方网站
  • 猿天地
  • 泥瓦匠BYSocket
  • crossoverJie
  • 张先森个人博客
  • 越加网

© 2021-2022 Java技术债务 - Java技术债务 版权所有
总访问量 0 次 您是本文第 0 位童鞋
豫ICP备2021034516号
Java技术债务 豫公网安备 51011402000164号

微信公众号

Java技术债务
Java技术债务

专注于Spring,SpringBoot等后端技术探索

以及MySql数据库开发和Netty等后端流行框架学习

日志
分类
标签
RSS

有不足之处也希望各位前辈指出