陕西建设厅网站首页,公众平台号,网页已改版,资源网搭建源码1 概述 MySQL 的用户群体很大#xff0c;为了能够增强数据的实时性#xff0c;很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件#xff0c;我们需要用到类似 canal 这样的第三方中间件#xff0c;这无疑增加了系统的复杂度。 ClickHouse 20.… 1 概述 MySQL 的用户群体很大为了能够增强数据的实时性很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件我们需要用到类似 canal 这样的第三方中间件这无疑增加了系统的复杂度。 ClickHouse 20.8.2.3版本新增加了 MaterializeMySQL 的 database 引擎该 database 能映射到 MySQL 中的某个 database并自动在 ClickHouse 中创建对应的 ReplacingMergeTree。ClickHouse服务做为MySQL副本读取Binlog并执行DDL和DML请求实现了基于MySQL Binlog机制的业务数据库实时同步功能。 1.1 特点 1MaterializeMySQL 同时支持全量和增量同步在 database 创建之初会全量同步 MySQL 中的表和数据之后则会通过 binlog 进行增量同步。 2MaterializeMySQL 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 1MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE等。 1.2 使用细则 1DDL查询 MySQL DDL查询被转换成相应的ClickHouse DDL查询ALTER, CREATE, DROP, RENAME。如果ClickHouse不能解析某些DDL查询该查询将被忽略。 2数据复制 MaterializeMySQL不支持直接插入、删除和更新查询而是将DDL语句进行相应转换 MySQL INSERT查询被转换为INSERT with _sign1。 MySQL DELETE查询被转换为INSERT with _sign-1。 MySQL UPDATE查询被转换成INSERT with _sign1和INSERT with _sign-1。 3SELECT查询 如果在SELECT查询中没有指定_version则使用FINAL修饰符返回_version的最大值对应的数据即最新版本的数据。 如果在SELECT查询中没有指定_sign则默认使用WHERE _sign1即返回未删除状态_sign1)的数据。 4索引转换 ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。 ClickHouse只有一个物理顺序由ORDER BY子句决定。如果需要创建新的物理顺序请使用物化视图。 2 案例实操 2.1 MySQL开启binlog和GTID模式 1确保 MySQL 开启了 binlog 功能且格式为 ROW 打开/etc/my.cnf,在[mysqld]下添加 server-id1 log-binmysql-bin binlog_formatROW 2开启GTID模式 如果如果clickhouse使用的是20.8 prestable之后发布的版本那么MySQL还需要配置开启GTID模式, 这种方式在mysql主从模式下可以确保数据同步的一致性(主从切换时)。 gtid-modeon enforce-gtid-consistency1 # 设置为主从强一致性 log-slave-updates1 # 记录日志 GTID 是 MySQL 复制增强版从 MySQL 5.6 版本开始支持目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一ID和序号我们可以不用关心 MySQL 集群主从拓扑结构直接告知 MySQL 这个 GTID 即可。 3重启MySQL sudo systemctl restart mysqld 2.2 准备MySQL表和数据 1在 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)
) ENGINEInnoDB;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());2创建第二张表 CREATE TABLE testck.t_user (id int(11) NOT NULL AUTO_INCREMENT,code int,PRIMARY KEY (id)
) ENGINEInnoDB;INSERT INTO testck.t_user (code) VALUES(1);2.3 开启ClickHouse物化引擎 set allow_experimental_database_materialize_mysql1; 2.4 创建复制管道 1ClickHouse中创建 MaterializeMySQL 数据库 CREATE DATABASE test_binlog ENGINE MaterializeMySQL(hadoop1:3306,testck,root,000000); 其中 4 个参数分别是 MySQL地址、databse、username 和 password。 2查看ClickHouse的数据 use test_binlog; show tables; select * from t_organization; select * from t_user; 2.5 修改数据 1在 MySQL 中修改数据: update t_organization set name CONCAT(name,-v1) where id 1 2查看clickhouse日志可以看到binlog监听事件查询clickhouse select * from t_organization; 2.6 删除数据 1MySQL删除数据: DELETE FROM t_organization where id 2; 2ClicKHouse日志有 DeleteRows 的 binlog 监听事件查看数据 select * from t_organization; 3在刚才的查询中增加 _sign 和 _version 虚拟字段 select *,_sign,_version from t_organization order by _sign desc,_version desc; 在查询时对于已经被删除的数据_sign-1ClickHouse 会自动重写 SQL将 _sign -1 的数据过滤掉; 对于修改的数据则自动重写 SQL为其增加 FINAL 修饰符。 select * from t_organization 等同于 select * from t_organization final where _sign 1 2.7 删除表 1在mysql执行删除表 drop table t_user; 2此时在clickhouse处会同步删除对应表如果查询会报错 show tables; select * from t_user; DB::Exception: Table scene_mms.scene doesnt exist.. 3mysql新建表clickhouse可以查询到 CREATE TABLE testck.t_user (id int(11) NOT NULL AUTO_INCREMENT,code int,PRIMARY KEY (id)
) ENGINEInnoDB;INSERT INTO testck.t_user (code) VALUES(1);#ClickHouse查询
show tables;
select * from t_user;
