ClickHouse同步MySQL数据

news/2024/4/24 20:49:20/

目录

  • 1 概述
    • 1.1 特点
    • 1.2 使用细则
  • 2 案例实操
    • 2.1 MySQL 开启 binlog 和 GTID 模式
    • 2.2 准备 MySQL 表和数据
    • 2.3 开启 ClickHouse 物化引擎
    • 2.4 创建复制管道
    • 2.5 修改数据
    • 2.6 删除数据
    • 2.7 删除表


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 特点

  1. MaterializeMySQL 同时支持全量和增量同步,在 database 创建之初会全量同步MySQL 中的表和数据,之后则会通过 binlog 进行增量同步。

  2. MaterializeMySQL 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 = 1
  • MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。

1.2 使用细则

  1. DDL 查询
    MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询(ALTER, CREATE, DROP, RENAME)。如果 ClickHouse 不能解析某些 DDL 查询,该查询将被忽略。
  2. 数据复制
    MaterializeMySQL 不支持直接插入、删除和更新查询,而是将 DDL 语句进行相应转换:
    MySQL INSERT 查询被转换为 INSERT with _sign=1。
    MySQL DELETE 查询被转换为 INSERT with _sign=-1。
    MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign=-1。
  3. SELECT 查询
    如果在 SELECT 查询中没有指定_version,则使用 FINAL 修饰符,返回_version 的最大值对应的数据,即最新版本的数据。
    如果在 SELECT 查询中没有指定_sign,则默认使用 WHERE _sign=1,即返回未删除状态(_sign=1)的数据。
  4. 索引转换
    ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为ORDER BY 元组。
    ClickHouse 只有一个物理顺序,由 ORDER BY 子句决定。如果需要创建新的物理顺序,请使用物化视图。

2 案例实操

2.1 MySQL 开启 binlog 和 GTID 模式

  1. 确保 MySQL 开启了 binlog 功能,且格式为 ROW
    打开/etc/my.cnf,在[mysqld]下添加:
   server-id=1log-bin=mysql-binbinlog_format=ROW
  1. 开启 GTID 模式
    如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本,那么 MySQL 还需要配置开启 GTID 模式, 这种方式在 mysql 主从模式下可以确保数据同步的一致性(主从切换时)。
   gtid-mode=onenforce-gtid-consistency=1 # 设置为主从强一致性log-slave-updates=1 # 记录日志

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`)) 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());
  1. 创建第二张表
   CREATE TABLE `testck`.`t_user` (`id` int(11) NOT NULL AUTO_INCREMENT,`code` int,PRIMARY KEY (`id`)) ENGINE=InnoDB;INSERT INTO testck.t_user (code) VALUES(1);

2.3 开启 ClickHouse 物化引擎

set allow_experimental_database_materialize_mysql=1;

2.4 创建复制管道

  1. ClickHouse 中创建MaterializeMySQL 数据库
   CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('hadoop1:3306','testck','root','000000');

其中 4 个参数分别是 MySQL 地址、databse、username 和 password。

  1. 查看 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
    1
    
  2. 查看 clickhouse 日志可以看到 binlog 监听事件,查询clickhouse

    select * from t_organization;
    1
    

2.6 删除数据

  1. MySQL 删除数据:
   DELETE FROM t_organization where id = 2;
  1. ClicKHouse,日志有 DeleteRows 的 binlog 监听事件,查看数据:
   select * from t_organization;
  1. 在刚才的查询中增加 _sign 和 _version 虚拟字段
   select *,_sign,_version from t_organization order by _sign desc,_version desc;

在这里插入图片描述

在查询时,对于已经被删除的数据,_sign=-1,ClickHouse 会自动重写 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;
  1. 此时在 clickhouse 处会同步删除对应表,如果查询会报错
   show tables;select * from t_user;DB::Exception: Table scene_mms.scene doesn't exist.. 
  1. mysql 新建表,clickhouse 可以查询到
   CREATE TABLE `testck`.`t_user` (`id` int(11) NOT NULL AUTO_INCREMENT,`code` int,PRIMARY KEY (`id`)) ENGINE=InnoDB;INSERT INTO testck.t_user (code) VALUES(1);#ClickHouse 查询show tables;select * from t_user;

http://www.ppmy.cn/news/47800.html

相关文章

Nmap入门到高级【第二章】

预计更新Nmap基础知识 1.1 Nmap简介和历史 1.2 Nmap安装和使用方法 1.3 Nmap扫描技术和扫描选项 Nmap扫描技术 2.1 端口扫描技术 2.2 操作系统检测技术 2.3 服务和应用程序检测技术 2.4 漏洞检测技术 Nmap扫描选项 3.1 扫描类型选项 3.2 过滤器选项 3.3 探测选项 3.4 输出选项…

Python OpenCV 蓝图:1~5

原文:OpenCV with Python Blueprints 协议:CC BY-NC-SA 4.0 译者:飞龙 本文来自【ApacheCN 计算机视觉 译文集】,采用译后编辑(MTPE)流程来尽可能提升效率。 当别人说你没有底线的时候,你最好真…

一文吃透Http协议

Http 协议 1. 初始 Http Http 协议 , 是应用层最为广泛使用的协议 , Http 就是浏览器和服务器之间的桥梁. Http 是基于 TCP 协议实现的 , 通常我们输入搜索框中的网址 (URL) , 浏览器就会根据这个 URL 构造出一个 Http 请求 , 发送给服务器. 服务器就会返回一个 Http 响应(包…

Vue 消息订阅与发布

消息订阅与发布,也可以实现任意组件之间的通信。 订阅者:就相当于是我们,用于接收数据。 发布者:就相当于是媒体,用于传递数据。 安装消息订阅与发布插件: 在原生 JS 中 不太容易实现消息订阅与发布&…

C语言的Hello World的汇编剖析(64位 Intel架构)

C语言的Hello World的汇编剖析(64位 Intel架构) 文章目录 C语言的Hello World的汇编剖析(64位 Intel架构)一. 前提准备二. C转换为汇编操作准备2.1 创建目录&复制代码2.2 C文件转换为汇编文件 三. 剖析汇编文件四. 指令相关五…

DevOps系列文章 - K8S知识体系

环境搭建部分: 1、安装前的准备工作 # 关闭防火墙 systemctl stop firewalld systemctl disable firewalld# 查看hostname并修改hostname # 查看本机hostname hostnamectl set-hostname k8s-master # 把本机名设置成k8s-master hostnamectl status # 查看修改结…

精通 Python OpenCV4:第二部分

原文:Mastering OpenCV 4 with Python 协议:CC BY-NC-SA 4.0 译者:飞龙 本文来自【ApacheCN 计算机视觉 译文集】,采用译后编辑(MTPE)流程来尽可能提升效率。 当别人说你没有底线的时候,你最好真…

【Chano的SFM教程】3dmax 面部表情.VTA基本制作教程

本篇教程作者为:小鸟Chano,转载请表明作者和出处:CSDN 欢迎观看本次教程 本教程将会为你演示使用3D MAX 制作一个基本的SFM表情控制器【表情滑条】并导入SFM进行使用。 Chano自己也是近期才掌握的这项知识,所以过程中可能有很多…

netfilter filter表

iptables是linux下常用的一个防火墙软件,可以实现对网络访问的各种限制。iptables相当于防火墙的客户端,与用户进行交换,其后台依赖于内核的netfilter模块。iptables的各种配置,最终都是netfilter模块来实现的。 iptables分为4个…

李沐读论文笔记--大模型时代下做科研的四个思路

大模型时代下做科研的四个思路 0. 视频来源:1. 提高效率(更快更小)1.1 PEFT介绍(parameter efficient fine tuning)1.2 作者的方法1.3 AIM效果1.3.1AIM 在 K400 数据集上的表现1.3.2AIM 在 Something-Something 数据集、K700 数据集和 Diving…

Liunx下进程间通信

文章目录 前言1.进程间通信相关介绍2.管道1.匿名管道2.管道的原理3.通过代码来演示匿名管道4.命名管道5.命名管道的原理6.命名管道代码演示 3.System V共享内存1.共享内存原理2.相关系统接口的介绍与共享内存的代码演示3.共享内存的一些特性 4.system V消息队列与system V信号量…

【设计模式】从Mybatis源码中学习到的10种设计模式

文章目录 一、前言二、源码:学设计模式三、类型:创建型模式1. 工厂模式2. 单例模式3. 建造者模式 四、类型:结构型模式1. 适配器模式2. 代理模式3. 组合模式4. 装饰器模式 五、类型:行为型模式1. 模板模式2. 策略模式3. 迭代器模式…

Mysql的简介和选择

文章目录 前言一、为什么要使用数据库 数据库的概念为什么要使用数据库二、程序员为什么要学习数据库三、数据库的选择 主流数据库简介使用MySQL的优势版本选择四、Windows 平台下安装与配置MySQL 启动MySQL 服务控制台登录MySQL命令五、Linux 平台下安装与配置MySQL总结 前言…

基于springboot和ajax的简单项目 06 日志界面的delete功能(根据选择的checkbox)

01.这次后台开始&#xff1b; 顺序依次是dao->xml->service->serviceimpl->controller->html 02.dao接口 public int doDeleteObjects(Param("ids") Integer... ids);03.xml文件 <update id"doDeleteObjects" >delete from sys_lo…

NLP领域顶级会议和期刊汇总(附CCF最新推荐目录)

研究NLP需要关注学术界or大厂AI Lab最新的科研动态&#xff0c;了解技术发展的趋势&#xff0c;写论文才能下笔如有神。找到了论文就能找到要复现的代码和要用到的数据集。 掌握科研动态也有助于提早做好产品规划以及技术预研。 对于NLPer而言&#xff0c;了解科研动态最好的方…

GEE:去除异常值

作者&#xff1a; _养乐多_ 本文记录了在Google Earth Engine (GEE) 平台上对影像去除异常值的方法和代码。 Google Earth Engine (GEE) 提供了多种方法来去除影像异常值&#xff0c;以下是一些可能有用的技巧&#xff1a; 普通异常值过滤&#xff1a;使用 ee.Image.clip() 或…

像素是什么

像素分为设备像素和设备无关像素。 下面说说来龙去脉。 一、显示器 显示图像的电子设备。 &#xff08;一&#xff09;显示器种类 1.LCD LCD&#xff08;Liquid crystal display&#xff09;&#xff0c;是液体晶体显示&#xff0c;也就是液晶显示器&#xff0c;LCD具有功耗低…

C/C++每日一练(20230421)

目录 1. 位1的个数 &#x1f31f; 2. 递归和非递归求和 ※ 3. 俄罗斯套娃信封问题 &#x1f31f;&#x1f31f;&#x1f31f; &#x1f31f; 每日一练刷题专栏 &#x1f31f; Golang每日一练 专栏 Python每日一练 专栏 C/C每日一练 专栏 Java每日一练 专栏 1. 位1的…

企业批量寄件快递教程

了解企业批量寄快递操作流程之前&#xff0c;我们先来解答一下这个问题&#xff1a;什么样的企业需要批量寄快递&#xff1f; 电商行业就不提&#xff0c;本身二者就是相互依存的关系。就商务件来说&#xff0c;具体什么样的企业需要批量寄快递&#xff0c;其实很简单&#xf…

【倒计时4天】金融服务用户体验专场沙龙开启预约

易观&#xff1a;数智化浪潮下&#xff0c;金融业务用户运营从线下向全渠道延伸。用户体验已成为⾦融机构打造差异化的重要抓手。此外&#xff0c;随着⽤户体验的全⾯数字化和精细化&#xff0c;⽤户体验贯穿于整个客户旅程中&#xff0c;做好用户体验管理&#xff0c;体现差异…