概述
MySQL用户群体很大,为了增强数据的实时性,很多解决方案会利用binlog将数据写入到ClickHouse。为了能够监听binlog事件,我们需要利用类似canal这样的中间件,但这样会增加系统的复杂度。
ClickHouse20.8.2.3版本新增了MaterializeMySQL这一数据库引擎,该引擎能够把ClickHouse中某个数据库映射到MySQL中的某个数据库,并自动在ClickHouse中创建对应的ReplacingMergeTree
。ClickHouse以此作为MySQL的副本,读取Binlog并执行DDL和DML,实现基于MySQL Binlog机制的业务数据库实时同步功能。
特点
- MaterializeMySQL支持全量和增量同步,在数据库创建之初会全量同步MySQL中的表和数据,之后则会通过binlog进行增量同步;
- MaterializeMySQL数据库为其所创建的每张
ReplacingMergeTree
表自动增加_sig
和_version
字段:前者用于标记数据是否被删除(-1为已删除,1为没有删除);后者用作ReplacingMergeTree
的版本参数,每当监听到对某行数据的增删改操作时,该行数据对应的_version
字段在数据库内全局自增。
目前,MaterializeMySQL支持以下四种binlog事件:
MYSQL_WRITE_ROWS_EVENT
:_sign
=1,_version
++;
2.MYSQL_DELETE_ROWS_EVENT
:_sign
=-1,_version
++;MYSQL_UPDATE_ROWS_EVENT
:_sign
=1, 新数据MYSQL_QUERY_ROWS_EVENT
:支持对表的创建、删除、重命名等;
使用细则
DDL查询
MySQL DDL查询会被转换成相应的ClickHouse DDL查询(alter
、create
、drop
、rename
),如果某DDL不能被ClickHouse解析,该查询将会被忽略;
数据复制
MaterializeMySQL不支持直接插入、删除和更新查询,而是将DDL语句进行相应转换:
MySQL insert
会被转换成insert with _sign=1
;
MySQL delete
会被转换成insert with _sign=-1
;
MySQL update
会被转换成insert with sign = 1
和insert with sign=-1
。
select查询
如果select
查询中没有指定_version
,则会使用final
修饰符,以返回_version
最大值对应的数据,即最新数据;
如果select
查询中没有指定_sign
,则默认使用where _sign=1
,即返回未删除状态的数据
索引转换
ClickHouse数据库表会自动将MySQL主键和索引子句转换成order by
语句。ClickHouse只有一个物理顺序,且由order by
子句决定。如果需要创建新的物理顺序,则需使用物化视图。
案例
配置MySQL-Windows
打开MySQL配置文件my.ini,开启binlog,且格式为row
:
server-id=1
log-bin=mysql-bin
binlog-format=Row
开启GTID模式:
gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1
保存文件,重启MySQL:计算机管理->服务和应用程序->服务->找到MySQL,右击重启:
在MySQL中创建数据库、表和数据:
mysql> create database testck;
Query OK, 1 row affected (0.00 sec)
mysql> 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;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO testck.t_organization (code, name,updatetime)
-> VALUES(1000,'Realinsight',NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO testck.t_organization (code, name,updatetime)
-> VALUES(1001, 'Realindex',NOW());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO testck.t_organization (code, name,updatetime)
-> VALUES(1002,'EDT',NOW());
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `testck`.`t_user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `code` int,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO testck.t_user (code) VALUES(1);
Query OK, 1 row affected (0.01 sec)
ClickHouse端
首先开启MySQL物化引擎:
scentos :) set allow_experimental_database_materialized_mysql=1;
然后创建复制管道:
scentos :) CREATE DATABASE test_binlog ENGINE =
MaterializeMySQL('192.168.31.60:3306','testck','root','root');
参数分别是:ip:端口、远程数据库名、用户名和密码;
在ClickHouse端查看MySQL数据:
scentos :) use test_binlog;
USE test_binlog
Query id: c6aabf92-035c-4588-98e5-b9b729e7f6a7
Ok.
0 rows in set. Elapsed: 0.001 sec.
scentos :) show tables;
SHOW TABLES
Query id: a3abdde3-a54e-4383-88d0-d5ee928ad995
┌─name───────────┐
│ t_organization │
│ t_user │
└────────────────┘
2 rows in set. Elapsed: 0.002 sec.
至此,MySQL和ClickHouse双端配置成功。
数据的修改和删除
MySQL端修改数据:
mysql> update t_organization set name = CONCAT(name,'-v1') where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ClickHouse端查看:
scentos :) select * from t_organization;
SELECT *
FROM t_organization
Query id: 49b13240-482a-4485-b832-70d31badfcae
┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│ 1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │
│ 2 │ 1001 │ Realindex │ 2021-12-12 15:07:32 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│ 3 │ 1002 │ EDT │ 2021-12-12 15:07:39 │
└────┴──────┴──────┴─────────────────────┘
3 rows in set. Elapsed: 0.004 sec.
MySQL端删除数据:
mysql> DELETE FROM t_organization where id = 2;
Query OK, 1 row affected (0.00 sec)
ClickHouse端查看:
scentos :) select * from t_organization;
SELECT *
FROM t_organization
Query id: 00319dc9-8703-4d57-9ccb-26696f08b93e
┌─id─┬─code─┬─name───────────┬──────────updatetime─┐
│ 1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │
└────┴──────┴────────────────┴─────────────────────┘
┌─id─┬─code─┬─name─┬──────────updatetime─┐
│ 3 │ 1002 │ EDT │ 2021-12-12 15:07:39 │
└────┴──────┴──────┴─────────────────────┘
2 rows in set. Elapsed: 0.003 sec.
在方才的查询中增加_sign和_version虚拟字段:
scentos :) select *,_sign,_version from t_organization order by _sign
:-] desc,_version desc;
SELECT
*,
_sign,
_version
FROM t_organization
ORDER BY
_sign DESC,
_version DESC
Query id: 236f35b3-8652-4228-9a4a-d65d5c7fc6cb
┌─id─┬─code─┬─name───────────┬──────────updatetime─┬─_sign─┬─_version─┐
│ 1 │ 1000 │ Realinsight-v1 │ 2021-12-12 15:07:24 │ 1 │ 2 │
└────┴──────┴────────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name────────┬──────────updatetime─┬─_sign─┬─_version─┐
│ 1 │ 1000 │ Realinsight │ 2021-12-12 15:07:24 │ 1 │ 1 │
│ 2 │ 1001 │ Realindex │ 2021-12-12 15:07:32 │ 1 │ 1 │
│ 3 │ 1002 │ EDT │ 2021-12-12 15:07:39 │ 1 │ 1 │
└────┴──────┴─────────────┴─────────────────────┴───────┴──────────┘
┌─id─┬─code─┬─name──────┬──────────updatetime─┬─_sign─┬─_version─┐
│ 2 │ 1001 │ Realindex │ 2021-12-12 15:07:32 │ -1 │ 3 │
└────┴──────┴───────────┴─────────────────────┴───────┴──────────┘
5 rows in set. Elapsed: 0.006 sec.
对于已经被删除的数据,_sign
=-1,ClickHouse会自动重写SQL,将_sign
为-1的数据过滤掉。如果涉及到修改的数据,ClickHouse会为SQL加上fina
l修饰符,并将SQL重写成如下形式:
select * from t_organization final where _sign = 1;
表的删除和增加
MySQL端删除一张表:
mysql> drop table t_user;
Query OK, 0 rows affected (0.01 sec)
ClickHouse端可以看到:
scentos :) show tables;
SHOW TABLES
Query id: cd1e8bb0-a07f-43ba-a0a1-0fcd71d75e4d
┌─name───────────┐
│ t_organization │
└────────────────┘
1 rows in set. Elapsed: 0.002 sec.
MySQL端新建表和数据:
mysql> CREATE TABLE `testck`.`t_user` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `code` int,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO testck.t_user (code) VALUES(1);
Query OK, 1 row affected (0.01 sec)
ClickHouse端同样可以看到:
scentos :) show tables;
SHOW TABLES
Query id: aaedc66f-fec6-48fd-a23d-274c0f6a9ae9
┌─name───────────┐
│ t_organization │
│ t_user │
└────────────────┘
2 rows in set. Elapsed: 0.002 sec.
scentos :) select * from t_user;
SELECT *
FROM t_user
Query id: 938aa8ec-2fda-4e12-b842-1dca83fa38c3
┌─id─┬─code─┐
│ 1 │ 1 │
└────┴──────┘
1 rows in set. Elapsed: 0.004 sec.