ClickHouse 的 MaterializeMySQL 实现Mysql复制
众所周知,MySQL 的用户群体很大,但是MySQL和ClickHouse是两个完全不一样的数据库,为了能够增强数据的实时性,很多解决方案会利用 binlog 将数据写入到 ClickHouse。
ClickHouse在2020下半年新增了一个名为 MaterializeMySQL 的 database 引擎,该 database 能映射到 MySQL 中的某个 database,并自动在 ClickHouse 中创建对应的 ReplacingMergeTree,实现了将ClickHouse作为MySQL的从库。
MaterializeMySQL 支持全量和增量同步,首次创建数据库引擎时进行一次全量复制,之后通过监控binlog变化进行增量数据同步;该引擎支持mysql 5.6/5.7/8.0版本数据库,兼容insert,update,delete,alter,create,drop,truncate等大部分DDL操作。
MaterializeMySQL实现流程:
- MaterializeMySQL支持数据库级别的复制。
- 当在Clickhouse中创建库级别复制后,clickhouse通过我们指定的数据库账号通过TCP/IP连接到数据,对数据库执行Flush table with read lock 并获取相关的binlog、表结构元数据信息;元数据复制完毕后释放全局只读锁,并开始通过select * from table_name开始复制表数据信息。
- 对于后续的增量数据的同步,MaterializeMySQL通过对binlog event的解析来实现的实时同步
- 对于DDL操作,MaterializeMySQL默认将MySQL表数据的主键作为CK表的排序键和分区键,但是由于Clickhouse与MySQL的数据定义有区别,DDL语句也会进行相应的转换
- 对于Update/Delete操作,MaterializeMySQL引入version的隐藏字段,用来做版本控制,并结合sign字段标记数据的有效性
目前 MaterializeMySQL 支持如下几种 binlog 事件:
1
2
3
4
51. MYSQL_QUERY_EVENT -- DDL 2. MYSQL_WRITE_ROWS_EVENT -- insert 3. MYSQL_UPDATE_ROWS_EVENT -- update 4. MYSQL_DELETE_ROWS_EVENT -- delete
事务提交后,MySQL 将执行过的 SQL 处理 BinLog Event,并持久化到 BinLog 文件
ClickHouse通过消费BinLog达到数据同步,过程中主要考虑3个方面问题:
- DDL兼容:由于ClickHouse和MySQL的数据类型定义有区别,DDL语句需要做相应转换
- Delete/Update 支持:引入_version字段,控制版本信息
- Query 过滤:引入_sign字段,标记数据有效性
1
2
3
4
5
6
7确保 MySQL 开启了 binlog 和gtid功能,且格式为 ROW log-bin=mysql-bin gtid_mode=ON enforce-gtid-consistency=1 # 设置为主从强一致性 binlog_format=ROW
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29--Mysql 创建数据库and数据表, CREATE DATABASE test_binlog; --table1 CREATE TABLE `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 t_organization (code, name,updatetime) VALUES(1000,'Realinsight',NOW()); INSERT INTO t_organization (code, name,updatetime) VALUES(1001,'Realindex',NOW()); INSERT INTO t_organization (code, name,updatetime) VALUES(1002,'EDT',NOW()); --table2 CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `code` int, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO t_test (code) VALUES(1);
1
2
3
4--ClickHouse CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('127.0.0.1:3306','test_binlog','dr','123'); //其中 4 个参数分别是 MySQL地址、databse、username 和 password。
注意:使用MaterialzeMySQL引擎的话,需要开启参数,该参数默认为不开启,每次重新进入client都需要重新开启
1
2set allow_experimental_database_materialize_mysql=1 ;
否则会报错:
1
2复制通道创建之后,我们去观察一下clickhouse日志:
1
2
3
4
5
6
7可以看到,ClickHouse dump 出了 MySQL 的表结构,并将其转换成了 ReplacingMergeTree。 在这个过程中,不仅各字段的数据类型进行了映射,还多出了 _sign 和 _version 两个字段。 MySQL 表的 PRIMARY KEY 作为了 ReplacingMergeTree 的 PARTITION BY, MySQL 表的 PRIMARY KEY 同时也作为了 ReplacingMergeTree 的 ORDER BY,如果 MySQL 表中还有 UNIQUE KEY (此例中的 code 字段),它也会一同被加入到 ORDER BY。
现在我们查询 ClickHouse 的 test_binlog 数据库
MySQL 的表已经被同步过来了,接着试试查询数据
接下来进一步测试 binlog 的同步功能。
在Mysql中update数据,并查看:
查看 ClickHouse 日志会发现 binlog 监听事件:
在ClickHouse查看数据,id=1的数据也被修改了。
现在再次回到 MySQL,尝试删除数据:
同样的,查看binlog日志,同样会发现 DeleteRows 的 binlog 监听事件::
再去ClickHouse中查看数据:
在刚才的查询中增加 _sign 和 _version 虚拟字段,就很容易理解了。
在查询时,对于Update操作Clickhouse的做法其实是自动重写 SQL,将变更后的记录进行写入,并标记sign=1,version=++;并为其增加 FINAL 修饰符。
对于已经被删除的数据,可以发现更新前数据其实并没有进行物理删除,而是新增一行需要删除的行记录数据,并标记_sign=-1、_version=++,查询时将 _sign = -1 的数据过滤掉;所以目前在ReplacingMergeTree 中被删除的数据只是被过滤掉了,并没有物理删除。
1
2
3
4select * from t_organization 等同于 select * from t_organization final where _sign = 1
最后
以上就是缓慢悟空最近收集整理的关于ClickHouse 的 MaterializeMySQLClickHouse 的 MaterializeMySQL 实现Mysql复制的全部内容,更多相关ClickHouse内容请搜索靠谱客的其他文章。
发表评论 取消回复