作者：TCeason 青雲科技資料庫研發工程師

2000 年至今，MySQL[1] 一直是全球最受歡迎的 OLTP（聯機事務處理）資料庫，ClickHouse[2] 則是近年來受到高度關注的 OLAP（聯機分析處理）資料庫。那麼二者之間是否會碰撞出什麼火花呢？

本文将帶領大家打破異構資料庫壁壘，将 MySQL 資料同步至 ClickHouse。

背景

1、MySQL 複制的發展曆程

圖 1-1 詳細羅列了 MySQL 複制的發展曆程。

2001 年的 MySQL 3.23 版本就已經支援了同構資料庫異步複制；由于是異步複制，根本無法在實際生産中大批量使用。

2013 年 MySQL 5.7.2 版本支援增強半同步複制能力，才勉強算得上是企業級可用的資料同步方案。

2016 年 MySQL 5.7.17 支援了 MGR，并不斷地發展成熟，變成了一個金融級别可用的資料同步方案。

而對于同構的 MySQL 資料同步，接下來要做的就是不斷地優化體驗，提升同步時效性，解決網絡異常下的各類問題。

基于此，各大廠商也開始做自己的高可用同步元件。例如由 QingCloud 資料庫研發團隊研發并開源的 Xenon，就具備了真正的強一緻性和高可用能力。

2、MySQL + Xenon

圖 1-2 中的 Xenon 是由類 Raft 算法來實作的高可用元件，用來管理 MySQL 選舉和探活，并訂正資料準确性。MySQL 資料同步則依然使用 Semi-Sync Replication 或者 MGR，進而達到資料強一緻性、無中心化自動選主且主從秒級切換，以及依托于雲的跨區容災能力。

ClickHouse 同步 MySQL 資料

為了加速 OLAP 查詢，QingCloud MySQL Plus[3]（MySQL + Xenon）借用 ClickHouse 來同步 MySQL 資料。

1、ClickHouse 概述

ClickHouse 是一個用于聯機分析 (OLAP) 的列式資料庫管理系統 (DBMS)。ClickHouse 構思于 2008 年，最初是為 YandexMetrica（世界第二大Web分析平台）而開發的。多年來一直作為該系統的核心元件被該系統持續使用着，并于 2016 年宣布開源。

從目前最新的 DB-Engines 中可以看到其排名曲線一路高漲，并且各大廠在重要業務上已經大量部署，這是一個很明顯的趨勢。是以，我們似乎可以認定 ClickHouse 的火熱并不隻是一時現象，它将長久地存活下去。而且，ClickHouse 靈活的外部表引擎，可輕松實作與 MySQL 的資料同步，接下來讓我們了解一下。

2、MySQL Table Engine

MySQL Table Engine 的特性。

Mapping to MySQL table
Fetch table struct from MySQL
Fetch data from MySQL when executing query

ClickHouse 最開始支援表級别同步 MySQL 資料，通過外部表引擎 MySQL Table Engine 來實作同 MySQL 表的映射。從

information_schema

表中擷取對應表的結構，将其轉換為 ClickHouse 支援的資料結構，此時在 ClickHouse 端，表結建構立成功。但是此時，并沒有真正去同步資料。隻有向 ClickHouse 中的該表發起請求時，才會主動的拉取要同步的 MySQL 表的資料。

MySQL Table Engine 使用起來非常簡陋，但它是非常有意義的。因為這是第一次打通 ClickHouse 和 MySQL 的資料通道。但是，缺點異常明顯：

i. 僅僅是對 MySQL 表關系的映射；

ii. 查詢時傳輸 MySQL 資料到 ClickHouse，會給 MySQL 可能造成未知的網絡壓力和讀壓力，可能影響 MySQL 在生産中正常使用。

基于 MySQL Table Engine 隻能映射 MySQL 表關系的缺點，QingCloud ClickHouse 團隊實作了 MySQL Database Engine。

3、MySQL Database Engine

MySQL Database Engine 的特性。

Mapping to MySQL Database
Fetch table list from MySQL

MySQL Database Engine 是庫級别的映射，要從

information_schema

中拉取待同步庫中包含的所有 MySQL 表的結構，解決了需要建立多表的問題。但仍然還有和 MySQL Table Engine 一樣的缺點：查詢時傳輸 MySQL 資料到 ClickHouse，給 MySQL 可能造成未知的網絡壓力和讀壓力，可能影響 MySQL 在生産中正常使用。

4、借用第三方軟體同步

除去上面提到的 MySQL Table Engine 、MySQL Database Engine 兩種方式，還有可以采用第三方軟體來同步資料，比如 Canal 或者 Kafka，通過解析 MySQL binlog，然後編寫程式控制向 ClickHouse 寫入。這樣做有很大的優勢，即同步流程自主可控。但是也帶來了額外的問題：

i. 增加了資料同步的複雜度。

ii. 增加了第三方軟體，使得運維難度指數級增加。

基于此，我們又可以思考一個問題，ClickHouse 能否主動同步并訂閱 MySQL 資料呢？

Materialize MySQL

為了解決 MySQL Database Engine 依然存留的問題，支援 ClickHouse 主動同步并訂閱 MySQL 資料，QingCloud ClickHouse 團隊自主研發了 MaterializeMySQL[4] 引擎。

1、簡述 MaterializeMySQL

MaterializeMySQL 引擎是由 QingCloud ClickHouse 團隊自主研發的庫引擎，目前作為實驗特性合并到 ClickHouse 20.8 版本中，是對 MySQL 庫級别關系的映射，通過消費 binlog 存儲到 MergeTree 的方式來訂閱 MySQL 資料。

具體使用方式就是一條簡單的 CREATE DATABASE SQL 示例：

CREATE DATABASE test ENGINE = MaterializeMySQL(
  '172.17.0.3:3306', 'demo', 'root', '123'
)

# 172.17.0.3:3306 - MySQL 位址和端口
# demo - MySQL 庫的名稱
# root - MySQL 同步賬戶
# 123 - MySQL 同步賬戶的密碼

2、MaterializeMySQL 的設計思路

Check MySQL Vars
Select history data
Consume new data

MaterializeMySQL 的設計思路如下：

首先檢驗源端 MySQL 參數是否符合規範;
再将資料根據 GTID 分割為曆史資料和增量資料;
同步曆史資料至 GTID 點;
持續消費增量資料。

3、MaterializeMySQL 的函數流程

如圖 3-1 所示，MaterializeMySQL 函數的主體流程為：

CheckMySQLVars -> prepareSynchronized -> Synchronized

（1）CheckMySQLVars

檢驗參數比較簡單，就是要查詢這些參數是否符合預期。

SHOW VARIABLES WHERE (Variable_name = 'log_bin'
AND upper(Value) = 'ON')
OR (Variable_name = 'binlog_format'
AND upper(Value) = 'ROW')
OR (Variable_name = 'binlog_row_image'
AND upper(Value) = 'FULL')
OR (Variable_name = 'default_authentication_plugin'
AND upper(Value) = 'MYSQL_NATIVE_PASSWORD')
OR (Variable_name = 'log_bin_use_v1_row_events'
AND upper(Value) = 'OFF');

（2）prepareSynchronized

這一步來實作曆史資料的拉取。

為先初始化 gtid 資訊；
為了保證幂等性每次重新同步時，都要清理 ClickHouse MaterializeMySQL 引擎庫下的表；
重新拉取曆史資料，并将 MySQL 表結構在 ClickHouse 端進行改寫；
建立與 MySQL 的 Binlog 傳輸通道。

std::optional<MaterializeMetadata> MaterializeMySQLSyncThread::prepareSynchronized()
{
    connection = pool.get();
    MaterializeMetadata metadata(
connection, DatabaseCatalog::instance().getDatabase(database_name)->getMetadataPath() + "/.metadata", mysql_database_name, opened_transaction);
    if (!metadata.need_dumping_tables.empty())
    {
        Position position;
        position.update(metadata.binlog_position, metadata.binlog_file, metadata.executed_gtid_set);
        metadata.transaction(position, [&]()
        {
            cleanOutdatedTables(database_name, global_context);
            dumpDataForTables(connection, metadata, query_prefix, database_name, mysql_database_name, global_context, [this] { return isCancelled(); });
         });
    }
    connection->query("COMMIT").execute();
}

在 MySQL 中，demo 庫下有一個表 t ，主鍵為 ID , 普通列 col_1。

CREATE TABLE demo.t (
  id int(11) NOT NULL,
  col_1 varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

在 ClickHouse 中，依然是id 作為主鍵列，但是，多了隐藏列 _sign 和 _version。

i. _sign：值隻有 1 和 -1。其中，1 代表這行資料存在，-1 代表這行資料被删除。

ii. _version：隻會讀到 version 高的值，會在背景不斷合并主鍵相同的行，最終保留 Version 最高的行。

CREATE TABLE test.t
(
    `id` Int32,
    `col_1` Nullable(String),
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 4294967)
ORDER BY tuple(id)

（3）Synchronized

在 prepareSynchronized 中，我們得到了曆史資料以及曆史資料位點資訊，并且獲得了與 MySQL 的 Binlog 傳輸通道。接下來就是從該位點同步增量資料。通過 readOneBinlogEvent 函數讀取每一條 binlog 内容，然後使用 onEvent 轉換成 ClickHouse 的語句格式即可。最終為了資料安全性，調用 flushBuffersData 函數将資料落盤。

client.connect();
client.startBinlogDumpGTID(randomNumber(), mysql_database_name, metadata.executed_gtid_set, metadata.binlog_checksum);
Buffers buffers(database_name);
while (!isCancelled())
{
    BinlogEventPtr binlog_event = client.readOneBinlogEvent(std::max(UInt64(1), max_flush_time - watch.elapsedMilliseconds()));
    if (binlog_event)
        onEvent(buffers, binlog_event, *metadata);
    if (!buffers.data.empty())
        flushBuffersData(buffers, *metadata);
}

HTAP 應用場景

當我們打通了 ClickHouse 和 MySQL 的複制通道，而 ClickHouse 的分析能力又是如此讓人驚喜，那麼我們是不是可以用 MySQL + ClickHouse 實作 HTAP 呢？

在圖 4-1 中的架構，依然使用高可用元件 Xenon 來管理 MySQL 複制，同時 Xenon 增加了對 ClickHouse 的監管，通過 MaterializeMySQL 來同步 MySQL 資料。

在之前的架構圖中，使用 MySQL 隻讀執行個體來進行商務分析、使用者畫像等分析業務。而現在可以直接将 ClickHouse 作為一個分析執行個體加入到 MySQL 複制中，替代一部分隻讀執行個體進行分析計算。同時 ClickHouse 本身支援了海量函數來支援分析能力的同時還支援标準 SQL，相信可以讓使用者享受到很好的體驗。

目前的 ClickHouse 可以支援同步 MySQL 5.7 和 8.0 的資料，不支援同步 MySQL 5.6 的資料。不過，作為一個實驗特性， MaterializeMySQL 的時間線相當于是 2001 年剛剛支援複制的 MySQL。歡迎大家一起來貢獻和維護 MaterializeMySQL。

[1]. MySQL : https://www.mysql.com/

[2]. ClickHouse : https://clickhouse.tech/docs/en/

[3]. MySQL Plus：https://www.qingcloud.com/products/mysql-plus/

[4]. MaterializeMySQL：https://clickhouse.tech/docs/en/engines/database-engines/materialize-mysql/

關于 RadonDB

RadonDB 開源社群是一個面向雲原生、容器化的資料庫開源社群，為資料庫技術愛好者提供圍繞主流開源資料庫（MySQL、PostgreSQL、Redis、MongoDB、ClickHouse 等）的技術分享平台，并提供企業級 RadonDB 開源産品及服務。

目前 RadonDB 開源資料庫系列産品已被光大銀行、浦發矽谷銀行、哈密銀行、泰康保險、太平保險、安盛保險、陽光保險、百年人壽、安吉物流、安暢物流、藍月亮、天财商龍、羅克佳華、升哲科技、無錫彙跑體育、北京電信、江蘇交通控股、四川航空、昆明航空、國控生物等上千家企業及社群使用者采用。

RadonDB 可基于雲平台與 Kubernetes 容器平台傳遞，不僅提供覆寫多場景的資料庫産品解決方案，而且提供專業的叢集管理和自動化運維能力，主要功能特性包括：高可用主從切換、資料強一緻性、讀寫分離、一鍵安裝部署、多元名額監控&告警、彈性擴容&縮容、橫向自由擴充、自動備份&恢複、同城多活、異地災備等。RadonDB 僅需企業及社群使用者專注于業務層邏輯開發，無需關注叢集高可用選型、管理和運維等複雜問題，幫助企業及社群使用者大幅度提升業務開發與價值創新的效率！

GitHub:

https://github.com/radondb

本文将介紹 MaterializeMySQL 引擎是如何實作 MySQL 資料同步至 ClickHouse 的。

HTAP | MySQL 到 ClickHouse 的高速公路

背景

1、MySQL 複制的發展曆程

2、MySQL + Xenon

ClickHouse 同步 MySQL 資料

1、ClickHouse 概述

2、MySQL Table Engine

3、MySQL Database Engine

4、借用第三方軟體同步

Materialize MySQL

1、簡述 MaterializeMySQL

3、MaterializeMySQL 的函數流程

（1）CheckMySQLVars

（2）prepareSynchronized

（3）Synchronized

HTAP 應用場景

關于 RadonDB

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述