ClickHouse（七）MaterializeMySQL引擎 · Clickhouse学习笔记 · 看云

企业🤖AI智能体构建引擎，智能编排和调试，一键部署，支持私有化部署方案广告

<div id="article_content" class="article_content clearfix"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-25cebea3f9.css"> <div id="content_views" class="htmledit_views"> <h1><a name="t0"></a>1、概述</h1> <p>        MySQL的用户群体很大，为了能够增强护具的实时性，很多解决方案会利用<a href="https://so.csdn.net/so/search?q=binlog&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=binlog&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"binlog\"}"}" data-tit="binlog" data-pretit="binlog">binlog</a>将数据写入到ClickHouse。为了能够监听binlog事件，我们需要用到类似canal这样的第三方中间件，这无疑增加了系统的复杂度。</p> <p>        ClickHouse20.8.2.3版本新增加了MaterializeMySQL的database引擎，该database能映射到MySQL中的某个database，并自动在<a href="https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"ClickHouse\"}"}" data-tit="ClickHouse" data-pretit="clickhouse">ClickHouse</a>中创建对应的ReplacingMergeTree。ClickHouse服务做为MySQL副本，读Binlog并执行DDL和DML请求，实现了基于MySQL Binlog机制的业务数据库实时同步功能。</p> <h2><a name="t1"></a>1.1、特点</h2> <ol><li>MaterializeMySQL同时支持全量和增量同步，在database创建之初会全量同步MySQL中的表和数据，之后则会通过binlog增量同步。</li><li>MaterializeMySQL database为其所创建的每张ReplacingMergeTree自动增加了_sign和_version字段。</li></ol> <p>        其中,_version用作ReplacingMergeTree的ver版本，每当监听到insert、update和delete事件时，在database内全局自增。而_sign则用于标记会否被删除，取值1或者-1。</p> <p>        目前MaterializeMySQL支持如下几种binlog事件：</p> <ul><li>MYSQL_WRITE_ROW_EVENT：_sign=1,_version++</li><li>MYSQL_DELETE_ROW_EVENT：_sign=-1,_version++</li><li>MYSQL_UPDATE_ROW_EVENT：新数据 _sign=1</li><li>MYSQL_QUERY_EVENT：支持CREATE TABLE、DROP TABLE、RENAME TABLE等。</li></ul> <h2><a name="t2"></a>1.2、使用细则</h2> <p>        1、DDL查询</p> <p>        MySQL查询被转换成相应的ClickHouse DDL查询（ALTER,CREATE,DROP,RENAME）。如果ClickHouse并不能解析某些DLL查询，该查询将被忽略。</p> <p>        2、数据复制</p> <p>        MaterializeMySQL不支持直接插入、删除和更新查询，而是将DLL语句进行相应转换：</p> <ul><li>MySQL INSERT查询被转换为INSERT with_sign=1。</li><li>MySQL DELETE查询被转换为INSERT with_sign=-1。</li><li>MySQL UPDATE查询被转换为INSERT with_sign=1 和 INSERT with_sign=-1。</li></ul> <p>        3、SELECT查询</p> <p>        如果在SELECT查询中没有指定_version，则使用FINAL修饰符，返回_version的最大值对应的数据，即最新版本的数据。</p> <p>        如果在SELECT查询中没有指定_sign，则默认会用WHERE _sign=1,即返回未删除状态（_sign=1）的数据。</p> <p>        4、索引转换</p> <p>        ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。</p> <p>        ClickHouse只有一个物理顺序，由ORDER BY子句决定。如果需要创建新的物理顺序，请使用物化视图。</p> <h1><a name="t3"></a>2、案例实操</h1> <h2><a name="t4"></a>2.1、MySQL开启binlog和GTID模式</h2> <p>        1、确保MySQL开启了binlog功能，且格式为ROW</p> <p>        打开/etc/my.cnf，在[mysqld]下添加：</p> <pre data-index="0"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">server-id=1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">log-bin=mysql-bin</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">binlog_format=ROW</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>        2、开启GTID模式</p> <p>        如果clickhouse使用的是20.8 prestable之后发布的版本，那么MySQL还需要配置开启GTID模式，这种方式在 mysql主从模式下可以确保数据同步的一致性（主从切换时）。</p> <pre data-index="1"><code class="hljs language-cobol"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">gtid</span>-<span class="hljs-keyword">mode</span><span class="hljs-operator">=</span><span class="hljs-keyword">on</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">enforc</span>e-gtid-consistency<span class="hljs-operator">=</span><span class="hljs-number">1</span> # 设置为主从强一致性</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">log</span>-slave-updates<span class="hljs-operator">=</span><span class="hljs-number">1</span> # 记录日志</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>        GTID是MySQL复制增强版，从MySQL5.6版本开始支持，目前已经是MySQL主流复制模式。它为每个event分配一个全局唯一ID和序号，我们可以不用关心MySQL集群主从拓扑结构，直接告知MySQL这个GTID即可。</p> <p>        3、重启MySQL</p> <h2><a name="t5"></a>2.2、准备MySQL表和数据</h2> <p>        1、在MySQL中创建数据表并写入数据</p> <pre data-index="2" class="set-code-hide" name="code"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> testck.t_organization(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id <span class="hljs-type">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span> auto_increment,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> `code` <span class="hljs-type">int</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> `name` text <span class="hljs-keyword">default</span> <span class="hljs-keyword">null</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> `updatetime` datetime <span class="hljs-keyword">default</span> <span class="hljs-keyword">null</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-keyword">primary</span> key(`id`),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-keyword">UNIQUE</span> KEY(`code`)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> INNODB;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> testck.t_organization (code,name,updatetime) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1000</span>,<span class="hljs-string">'ABCDEG'</span>,NOW());</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> testck.t_organization (code,name,updatetime) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2000</span>,<span class="hljs-string">'QWER'</span>,NOW());</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> testck.t_organization (code,name,updatetime) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3000</span>,<span class="hljs-string">'TQWQE'</span>,NOW());</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> testck.t_user(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id <span class="hljs-type">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span> auto_increment,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="18"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> `code` <span class="hljs-type">int</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">null</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="19"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-keyword">primary</span> key(`id`)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="20"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> INNODB;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="21"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="22"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> testck.t_user (code) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>);</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{"spm":"1001.2101.3001.7365"}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <h2><a name="t6"></a>2.3、开启ClickHouse物化引擎</h2> <p>        set allow_experimental_database_materialize_mysq1=1;</p> <h2><a name="t7"></a>2.4、创建复制管道</h2> <p>        在ClickHouse中创建MaterializeMySQL数据库</p> <pre data-index="3"><code class="language-sql hljs"><span class="hljs-keyword">CREATE</span> DATABASE test_binlog ENGINE <span class="hljs-operator">=</span> MaterializeMySQL(<span class="hljs-string">'hadoop2:3306'</span>,<span class="hljs-string">'testck'</span>,<span class="hljs-string">'root'</span>,<span class="hljs-string">'123456'</span>);</code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>        其中4个参数分别是MySQL地址、database、username和password。</p> <h2><a name="t8"></a>2.5、修改和删除数据</h2> <p>        在MySQL中执行以下操作：</p> <pre data-index="4"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">update</span> t_organization <span class="hljs-keyword">set</span> name <span class="hljs-operator">=</span> CONCAT(`name`,<span class="hljs-string">'-concatv1'</span>) <span class="hljs-keyword">where</span> id <span class="hljs-operator">=</span> <span class="hljs-number">1</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> t_organization <span class="hljs-keyword">where</span> id <span class="hljs-operator">=</span> <span class="hljs-number">2</span>;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>        在ClickHoue中查看数据：</p> <p style="text-align:center;"><img alt="" height="553" src="https://img-blog.csdnimg.cn/08308ac4793e4ff18e0a4d82de5ef15e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAWXVhbl9DU0RG,size_20,color_FFFFFF,t_70,g_se,x_16" width="654"></p> <p>         在查询时，对于已经被删除的数据，_sign=-1，ClickHouse会自动重写SQL，将_sign=-1的数据过滤掉；</p> <p>        对于修改的数据，则自动重写SQL，只查询版本号最大的。</p> <h2><a name="t9"></a>2.6、新建表和删除表</h2> <p>        在mysql中执行新建表和删除表，会实时同步到ClickHouse。</p> <h1><a name="t10"></a>3、和Engine=MySQL的区别</h1> <p>        Engine=MySQL可以在ClickHouse中对MySQL数据进行操作。MaterializeMySQL主是用于实时同步MySQL数据，无法对MySQL进行操作。</p> <p>        </p> </div><div data-report-view="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/122549789","extend1":"pc","ab":"new"}"><div></div></div> </div>