©2020-2024 ioDraw All rights reserved,
Privacy Policy
<>前言
通过前面的篇章，我们了解到clickhouse是一款性能很高的OLAP数据存储、数据分析型数据库引擎，主要用于在线分析处理查询（OLAP），能够使用 SQL
查询实时生成分析数据报告，基于此，在大数据领域也成为一匹新生的黑马。
既然可以作为数据分析使用，基于其本身的特性，生产中有这么一种场景，当关系型数据库，比如mysql数据量非常庞大，但这些数据更多的作用在于分析、统计、汇总等方面的业务时，将这些数据迁移到mysql是一种非常好的选择，下面来聊聊如何将clickhouse对接mysq数据从而实现数据的同步
<>MaterializeMySQL 引擎
ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎，通过该引擎，能够将该database
映射到MySQL中的某个database ，并 自 动 在 ClickHouse中创建对应的ReplacingMergeTree。ClickHouse
服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。
特点：
* MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步
MySQL 中的表和数据，之后则会通过 binlog 进行增量同步
* MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了
_sign 和 _version 字段。
* 其中，_version 用作 ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete
事件时，在 databse 内全局自增。而 _sign 则用于标记是否被删除，取值 1 或 者 -1
使用细则：
DDL 查询
DDL 查询 ，MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME）。如果
ClickHouse 不能解析某些 DDL 查询，该查询将被忽略
数据复制
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，即返回未删除状态（_sign=1)的数据；
索引转换
ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组
ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序，请使用物化视图
<>同步原理
核心同mysql主从复制原理基本一致，clickhouse也是如此，底层通过监听binlog日志数据的变化实现数据的同步写入、变更等
<>环境准备
* clickhouse服务（安装并启动）
* mysql服务（安装并启动）
<>搭建步骤
1、开启mysql的binlog配置
[mysqld] server-id=1 log-bin=mysql-bin binlog_format=row binlog-do-db=bank1
#具体同步的数据库名称 #同步到clickhouse的配置 gtid-mode=on enforce-gtid-consistency=1 ##
设置为主从强一致性 log-slave-updates=1
说明：
如果是mysql到mysql的数据同步，“#同步到clickhouse的配置”
这段注释里面的几个配置非必须，但是clickhouse的话，这里必须做配置，因为GTID 是 MySQL 复制增强版，从 MySQL 5.6
版本开始支持，目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一 ID 和序号，我们可以不用关心 MySQL
集群主从拓扑结构，直接告知 MySQL 这个 GTID 即可
2、重启mysql服务
sudo systemctl restart mysqld
3、mysql的bank1数据库下创建两张表，用于测试
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
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()); CREATE TABLE `t_user` ( `id` int(11) NOT NULL
AUTO_INCREMENT,`code` int, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO
testck.t_user(code) VALUES(1);
INSERT INTO t_user (code) VALUES(1);
注意：此处创建的测试表，必须要有主键字段
4、开启 ClickHouse 物化引擎
在clickhouse的shell客户端窗口，执行下面的命令
set allow_experimental_database_materialize_mysql=1;
5、创建复制管道
ClickHouse 中创建 MaterializeMySQL 数据库，在clickhouse的shell客户端窗口，执行下面的命令
其中 4 个参数分别是 MySQL 地址、databse、username 和 password
上面的命令执行完毕后，可以查看下 ClickHouse 的数据库是否有上面的库名
然后切换到 mysql_sync 数据库下，可以看到，在未做任何操作的情况下，mysql中创建的两张表和数据已经同步过来了，说明首次做了全量数据同步
5、mysql中修改数据
在mysql中执行下面的修改语句
update t_organization set name = CONCAT(name,'-v1') where id = 1
查看 clickhouse 日志可以看到 binlog 监听事件，这时候再次查询 clickhouse中上面的表数据，可以看到数据几乎实时同步过来
MySQL 删除数据
DELETE FROM t_organization where id = 2;
ClicKHouse，日志有 DeleteRows 的 binlog 监听事件，查看数据，id为2的数据被删掉了
在刚才的查询中增加 _sign 和 _version 虚拟字段，再次看看查询结果
select *,_sign,_version from t_organization order by _sign desc,_version desc;
在上面，我们删除了 id为2的数据，所以 _ sign 字段被标记为 -1 ，说明这条数据并不是真正删除了，而只是在使用正常 select
语句出来的结果中不展示而已，同时，通过后面的version字段可以发现，每次对数据操作一次，version字段会不断递增