一、背景
公司使用MariaDB作为数据库,没啥问题能解决很大一部分系统问题,能搞主从,能做备份,一切都可以正常运行。
直到新的需求来临,新的需求就是大规模的报表数据,主管决定用ClickHouse数据库来做报表部分功能,于是就开始了数据同步的研究,查阅了很多资料,没办法直接同步MariaDB的数据,在经历了长达4天的煎熬之后,真正的解决方案他来了,实时同步,增量同步。
二、总览
为什么要绕这么大一圈
1、ClickHouse支持MaterializeMySQL数据库引擎,可以直接在ClickHouse端和MySQL端配置即可实现数据实时同步,但是MariaDB不支持;
2、MariaDB->MySQL->ClickHouse,MySQL和ClickHouse可以实时同步,实现同步所需要的MySQL配置不支持MariaDB和MySQL创建主从连接;
3、Canal可以连接MySQL和ClickHouse,实现数据实时同步,但是MariaDB不支持,可以创建连接,但是有些SQL语法不支持,会报异常;
4、最终版解决方案,通过Canal将MariaDB的数据实时同步至MySQL,再通过ClickHouse的MaterializeMySQL数据库引擎和MySQL创建数据同步的连接,从而实现MariaDB到ClickHouse的数据实时同步。
三、环境准备
MariaDB版本:10.5.17-MariaDB
Canal下载地址及版本:Releases · alibaba/canal · GitHub
MySQL下载地址及版本:MySQL :: Download MySQL Community Server
ClickHouse下载地址及版本:packages.clickhouse.com/tgz/stable/
四、安装
MySQL、MariaDB网上都是由教程的,百度一搜一大片,
Canal网上找了个 (47条消息) canal实现mysql同步到clickhouse_canal clickhouse_Younger成的博客-CSDN博客
ClickHouse我自己也有装过,贴个链接:https://mp.csdn.net/mp_blog/creation/editor/130788668
MySQL创建数据库用户供ClickHouse使用,root/123456
五、配置
MariaDB无需做特殊的配置
MySQL需要做和ClickHouse创建链接的配置
Canal需要做的配置就是链接MariaDB和MySQL
ClickHouse的配置就是开启物化数据库引擎,即MaterializeMySQL数据库引擎
MySQL:
server-id=1
log-bin=mysql-bin
binlog-format=Row
gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1
ClickHouse:
开启物化引擎:
set allow_experimental_database_materialized_mysql=1;
创建用来同步数据的表:
create database ccc engine = MaterializeMySQL('MySQL安装IP地址','MySQL数据库名称','root','123456');
到这一步MySQL和ClickHouse已经建立了连接,可进行测试,在MySQL中的aaa表中添加数据、删除数据、修改数据,ClickHouse这边都是同步过来了的;
接下来就是MariaDB和MySQL怎么用Canal来搭建连接并实现数据实时同步。
Canal-deployer:
canal.properties,主要指定实例文件
example/instance.properties
Canal-adapter:
adapter/conf/application.yml
adapter/conf/rdb/example.yml
六、重启所有服务
MySQL、MariaDB、ClickHouse、Canal-deployer、Canal-adapter重启
七、验证
在MariaDB数据库中的aaa表插入数据:
在MySQL中查询:
在ClickHouse中查询:
在MariaDB中修改数据:
在MySQL中查询:
在ClickHouse中查询:
八、总结
这一套数据同步目前只是实现了单表的实时同步,暂时还未研究通过多表或者整个库的实时同步,而且在做这一套东西的时候,想必也已经知道了ClickHouse在某些SQL语句上面还是有一些差异,所以这一套数据同步的中间件组合,是否支持所有的SQL语句,目前增删改是没有问题的,但是如果是修改表、drop、delete、truncate等操作呢,数据是否会同步,有待验证。
九、使用总结
SQL行为 | MariaDB | MySQL | ClickHouse | 时效性 | ||
insert | 新增数据 | -> | 新增成功 | -> | 新增成功 | 实时同步 |
delete | 删除数据 | -> | 删除成功 | -> | 删除成功 | 实时同步 |
update | 修改数据 | -> | 修改成功 | -> | 修改成功 | 实时同步 |
新增表字段 | 新增字段 | -> | 字段新增失败 | -> | 字段新增失败 | 重启前新增字段值为null 重启canal-adapter后生效 |
删除表字段 | 删除字段 | -> | 字段删除失败 | -> | 字段删除失败 | 重启canal-adapter后不生效 新增数据时该字段值为null |
drop | 删除表 | -> | 删除失败 | -> | 删除失败 | 重启canal-adapter后不生效 |