​​​​​​​​​​​​​​​​​【摘要】 MySQL到ClickHouse資料同步原理及實踐。

引言

熟悉MySQL的朋友應該都知道，MySQL叢集主從間資料同步機制十分完善。令人驚喜的是，ClickHouse作為近年來炙手可熱的大資料分析引擎也可以掛載為MySQL的從庫，作為MySQL的 "協處理器"面向OLAP場景提供高效資料分析能力。早先的方案比較直截了當，透過第三方外掛將所有MySQL上執行的操作進行轉化，然後在ClickHouse端逐一回放達到資料同步。終於在2020年下半年，Yandex 公司在 ClickHouse 社群釋出了MaterializeMySQL引擎，支援從MySQL全量及增量實時資料同步。MaterializeMySQL引擎目前支援 MySQL 5.6/5.7/8.0 版本，相容 Delete/Update 語句，及大部分常用的 DDL 操作。

基礎概念MySQL& ClickHouse

MySQL一般特指完整的MySQL RDBMS，是開源的關係型資料庫管理系統，目前屬於Oracle公司。MySQL憑藉不斷完善的功能以及活躍的開源社群，吸引了越來越多的企業和個人使用者。

ClickHouse是由Yandex公司開源的面向OLAP場景的分散式列式資料庫。ClickHouse具有實時查詢，完整的DBMS及高效資料壓縮，支援批次更新及高可用。此外，ClickHouse還較好地相容SQL語法並擁有開箱即用等諸多優點。

RowStore & Column Store

MySQL儲存採用的是Row Store，表中資料按照 Row 為邏輯儲存單元在儲存介質中連續儲存。這種儲存方式適合隨機的增刪改查操作，對於按行查詢較為友好。但如果選擇查詢的目標只涉及一行中少數幾個屬性，Row 儲存方式也不得不將所有行全部遍歷再篩選出目標屬性，當表屬性較多時查詢效率通常較低。儘管索引以及快取等最佳化方案在 OLTP 場景中能夠提升一定的效率，但在面對海量資料背景的 OLAP 場景就顯得有些力不從心了。

ClickHouse則採用的是Column Store，表中資料按照Column為邏輯儲存單元在儲存介質中連續儲存。這種儲存方式適合採用 SIMD (Single Instruction Multiple Data) 併發處理資料，尤其在表屬性較多時查詢效率明顯提升。列存方式中物理相鄰的資料型別通常相同，因此天然適合資料壓縮從而達到極致的資料壓縮比。

使用方法部署Master-MySQL開啟BinLog功能：ROW模式開啟GTID模式：解決位點同步時MySQL主從切換問題（BinLogreset導致位點失效）部署Slave-ClickHouse

獲取 ClickHouse/Master 程式碼編譯安裝

推薦使用GCC-10.2.0，CMake3.15，ninja1.9.0及以上

建立Master-MySQL中database及table建立Slave-ClickHouse 中 MaterializeMySQL database

此時可以看到ClickHouse中已經有從MySQL中同步的資料了：

工作原理BinLogEvent

MySQL中BinLog Event主要包含以下幾類：

事務提交後，MySQL 將執行過的 SQL 處理 BinLog Event，並持久化到 BinLog 檔案

ClickHouse透過消費BinLog達到資料同步，過程中主要考慮３個方面問題：

1、DDL相容：由於ClickHouse和MySQL的資料型別定義有區別，DDL語句需要做相應轉換

2、Delete/Update支援：引入_version欄位，控制版本資訊

3、Query過濾：引入_sign欄位，標記資料有效性

DDL操作

對比一下MySQL的DDL語句以及在ClickHouse端執行的DDL語句：

可以看到：

2、預設將表引擎設定為 ReplacingMergeTree，以 _version 作為 column version

3、原DDL主鍵欄位 runoob_id 作為ClickHouse排序鍵和分割槽鍵

此外還有許多DDL處理，比如增加列、索引等，相應程式碼在Parsers/MySQL 目錄下。

Delete/Update操作

Update：

可以看到，ClickHouse資料也實時同步了更新操作。

Delete:日誌回放

MySQL主從間資料同步時Slave節點將 BinLog Event 轉換成相應的SQL語句，Slave 模擬 Master 寫入。類似地，傳統第三方外掛沿用了MySQL主從模式的BinLog消費方案，即將 Event 解析後轉換成 ClickHouse 相容的 SQL 語句，然後在 ClickHouse 上執行（回放），但整個執行鏈路較長，通常效能損耗較大。不同的是，MaterializeMySQL 引擎提供的內部資料解析以及回寫方案隱去了三方外掛的複雜鏈路。回放時將 BinLog Event 轉換成底層 Block 結構，然後直接寫入底層儲存引擎，接近於物理複製。此方案可以類比於將 BinLog Event 直接回放到 InnoDB 的 Page 中。

同步策略日誌回放

v20.9.1版本前是基於位點同步的，ClickHouse每消費完一批 BinLog Event，就會記錄 Event 的位點資訊到 .metadata 檔案:

這樣當 ClickHouse 再次啟動時，它會把{‘mysql-bin.000003’, 355005999} 二元組透過協議告知MySQL Server，MySQL 從這個位點開始傳送資料：

存在問題：

如果MySQL Server是一個叢集，透過VIP對外服務，MaterializeMySQL建立 database 時 host指向的是VIP，當叢集主從發生切換後，{Binlog File, BinlogPosition} 二元組不一定是準確的，因為BinLog可以做reset操作。

為了解決這個問題，v20.9.1版本後上線了 GTID 同步模式，廢棄了不安全的位點同步模式。

GTID同步

GTID模式為每個 event 分配一個全域性唯一ID和序號，直接告知 MySQL 這個 GTID 即可，於是

.metadata變為:

其中0857c24e-4755-11eb-888c-00155dfbdec7 是生成 Event的主機UUID，1-783是已經同步的event區間

於是流程變為:

原始碼分析概述

在最新原始碼 (v20.13.1.1) 中，ClickHouse 官方對 DatabaseMaterializeMySQL 引擎的相關原始碼進行了重構，並適配了 GTID 同步模式。ClickHouse 整個專案的入口 main函式在 /ClickHouse/programs/main.cpp檔案中，主程式會根據接收指令將任務分發到 ClickHouse/programs目錄下的子程式中處理。本次分析主要關注 Server 端 MaterializeMySQL引擎的工作流程。

原始碼目錄

與 MaterializeMySQL 相關的主要原始碼路徑：

服務端主要流程

ClickHouse使用 POCO 網路庫處理網路請求，Client連線的處理邏輯在 ClickHouse/src/Server/*Handler.cpp 的 hander方法裡。以TCP為例，除去握手，初始化上下文以及異常處理等相關程式碼，主要邏輯可以抽象成:

資料同步預處理

Client傳送的SQL在executeQuery函式處理，主要邏輯簡化如下：

主要有三點：

1、解析SQL語句並生成語法樹 AST

2、InterpreterFactory 工廠類根據 AST 生成執行器

3、interpreter->execute()

跟進第三點，看看 InterpreterCreateQuery 的 excute() 做了什麼：

這裡註釋很明顯，主要執行 CREATE 或 ATTACH DATABASE，繼續跟進 createDatabase() 函式：

到這裡，相當於將任務分發給DatabaseMaterializeMySQL處理，接著跟蹤 loadStoredObjects 函式：

跟進startSynchronization() 繫結的執行函式：

全量同步

MaterializeMySQLSyncThread::prepareSynchronized負責DDL和全量同步，主要流程簡化如下：

ClickHouse作為MySQL從節點，在MaterializeMetadata建構函式中對MySQL端進行了一系列預處理：

1、將開啟的表關閉，同時對錶加上讀鎖並啟動事務

2、TablesCreateQuery透過SHOW CREATE TABLE 語句獲取MySQL端的建表語句

3、獲取到建表語句後釋放表鎖

繼續往下走，執行到 metadata.transaction() 函式，該呼叫傳入了匿名函式作為引數，一直跟進該函式會發現最終會執行匿名函式，也就是cleanOutdatedTables以及dumpDataForTables函式，主要看一下 dumpDataForTables 函式：

繼續跟蹤 tryToExecuteQuery 函式，會呼叫到executeQueryImpl() 函式，上文提到過這個函式，但這次我們的上下文資訊變了，生成的執行器發生變化，此時會進行 DDL 轉化以及 dump table 等操作：

此時 InterpreterFactory 返回InterpreterExternalDDLQuery，跟進去看execute 函式做了什麼：

繼續跟進去看看 getIdentifierName(arguments[1])).execute() 做了什麼事情：

進一步看 InterpreterImpl::getRewrittenQueries 是怎麼轉化 DDL 的：

完成DDL轉換之後就會去執行新的DDL語句，完成建表操作，再回到 dumpDataForTables：

增量同步

還記得startSynchronization() 繫結的執行函式嗎？全量同步分析都是在prepareSynchronized()進行的，那增量更新呢？

可以看到，while 語句裡有一個 binlog_event 的偵聽函式，用來偵聽 MySQL 端 BinLog 日誌變化，一旦MySQL 端執行相關操作，其 BinLog 日誌會更新並觸發 binlog_event，增量更新主要在這裡進行。

小結

MaterializeMySQL 引擎是 ClickHouse 官方2020年主推的特性，由於該特性在生產環境中屬於剛需且目前剛上線不久，整個模組處於高速迭代的狀態，因此有許多待完善的功能。例如複製過程狀態檢視以及資料的一致性校驗等。感興趣的話可參考Github上的2021-Roadmap，裡面會更新一些社群最近得計劃。以上內容如有理解錯誤還請指正。

引用

ClickHouse社群文件

ClickHouse社群原始碼

MySQL實時複製與實現

MaterializeMySQL引擎分析