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