当前位置: 首页>后端>正文

MaterializeMySQL引擎

概述

MySQL 的用户群体很大,为了能够增强数据的实时性,很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件,我们需要用到类似 canal \maxwell\cdc这样的第三方中间件,再导到kafka,这无疑增加了系统的复杂度。ClickHouse 20.8.2.3版本新增加了 MaterializeMySQL 的 database 引擎(是个库引擎),该 database 能映射到 MySQL 中的某个 database,并自动在 ClickHouse 中创建对应的 ReplacingMergeTree。
ClickHouse服务做为MySQL副本,读取Binlog并执行DDL和DML请求,实现了基于MySQL Binlog机制的业务数据库实时同步功能。

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

1.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子句决定。如果需要创建新的物理顺序,请使用物化视图

案例使用

2.1 MySQL开启binlog和GTID模式
(1)确保 MySQL 开启了 binlog 功能,且格式为 ROW:打开/etc/my.cnf,在[mysqld]下添加:

vim /etc/my.cnf
#数据库id
server-id = 1
##启动binlog,该参数的值会作为binlog的文件名
log-bin=mysql-bin

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

vim /etc/my.cnf

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

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

systemctl restart mysqld

2.2 准备MySQL表和数据

CREATE TABLE `book_info`
(   `id`     INT(11),
    `name`   VARCHAR(20) COLLATE utf8_german2_ci DEFAULT NULL,
    `author` VARCHAR(20) COLLATE utf8_german2_ci DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_german2_ci
insert into `book_info` (`id`, `name`, `author`) values('1001','侠客行','金庸');
insert into `book_info` (`id`, `name`, `author`) values('1002','孔雀翎','古龙');
insert into `book_info` (`id`, `name`, `author`) values('1003','萍踪侠影','梁羽生');

2.3 开启ClickHouse物化引擎

set allow_experimental_database_materialize_mysql=1;

2.4 创建复制管道
ClickHouse中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE =
    MaterializeMySQL('hadoop1:3306','test','root','123465');

2.5 Mysql 更新数据
Mysql 更新数据

UPDATE book_info SET NAME = '侠客行-1980' WHERE id = '1001';
COMMIT;
SELECT *
FROM book_info;

查看 ClickHouse

select * from test_binlog.book_info
1002    孔雀翎    古龙
1003    萍踪侠影    梁羽生
1001    侠客行-1980    金庸

2.6 Mysql 删除数据

delete from  book_info   WHERE id = '1001';
COMMIT;
SELECT *
FROM book_info;

查看ClickHouse结果

select * from test_binlog.book_info
1002    孔雀翎    古龙
1003    萍踪侠影    梁羽生

_sign可以理解为是否删除,_version 版本号默认查询 _sign =1,_version 版本号最新的数据

select *,_sign,_version  from test_binlog.book_info
1001    侠客行    金庸    1    1
1002    孔雀翎    古龙    1    1
1003    萍踪侠影    梁羽生    1    1
1001    侠客行-1980    金庸    1    2
1001    侠客行-1980    金庸    -1    3

2.6 Mysql truncate

truncate table book_info;
SELECT *
FROM book_info;

ClickHouse

select * from  test_binlog.book_info;
select *,_sign,_version  from test_binlog.book_info

无数据
2.7 Mysql Drop

drop table book_info;

ClickHouse 表也没了。


https://www.xamrdz.com/backend/34a1941315.html

相关文章: