关于数据库:MySQL-到-ClickHouse-的高速公路

41次阅读

共计 6390 个字符，预计需要花费 16 分钟才能阅读完成。

2000 年至今，MySQL[1] 始终是寰球最受欢迎的 OLTP（联机事务处理）数据库，ClickHouse[2] 则是近年来受到高度关注的 OLAP（联机剖析解决）数据库。那么二者之间是否会碰撞出什么火花呢？

本文将率领大家突破异构数据库壁垒，将 MySQL 数据同步至 ClickHouse。对 QingCloud MySQL Plus[3] 平台与 MaterializeMySQL[4] 引擎进行了具体介绍，并进一步简述了 HTAP 利用场景。

图 1-1 MySQL 复制的倒退历程

图 1 - 1 具体列举了 MySQL 复制的倒退历程。

2001 年的 MySQL 3.23 版本就曾经反对了同构数据库异步复制；因为是异步复制，根本无法在理论生产中大批量应用。

2013 年 MySQL 5.7.2 版本反对加强半同步复制能力，才勉强算得上是企业级可用的数据同步计划。

2016 年 MySQL 5.7.17 反对了 MGR，并一直地倒退成熟，变成了一个金融级别可用的数据同步计划。

而对于同构的 MySQL 数据同步，接下来要做的就是一直地优化体验，晋升同步时效性，解决网络异样下的各类问题。

基于此，各大厂商也开始做本人的高可用同步组件。例如 QingCloud MySQL Plus，就具备了真正的强一致性和高可用能力。

图 1-2 MySQL Plus 架构图

图 1-2 中的 Xenon 是由类 Raft 算法来实现的高可用组件，用来治理 MySQL 选举和探活，并勘误数据准确性。MySQL 数据同步则仍然应用 Semi-Sync Replication 或者 MGR，从而达到数据强一致性、无中心化主动选主且主从秒级切换，以及依靠于云的跨区容灾能力。

QingCloud MySQL Plus 采纳一主两从的初始节点架构设计，并通过 MySQL 5.7 版本中的 Semi-Sync 个性实现数据的多正本同步复制，确保至多一个从节点与主节点始终保持数据的完全一致，提供金融级数据强一致性。多个从节点的设置将极大地屏蔽掉单点故障带来的影响，确保集群内始终有从节点保有全量数据。

节点之间应用 Raft 协定进行治理，当主节点呈现故障不可用时，集群会秒级响应并选出新的主节点（与主节点数据齐全同步的从节点），立刻接管读写申请，确保业务的间断高可用。这一过程，用户齐全无需关怀后端集群中各节点的角色如何设置，所有由零碎主动治理。

可实现多可用区主从部署，具备跨可用区容灾能力，晋升数据安全性及容灾能力。

MySQL 有了高可用能力之后，能够通过减少只读实例的形式来加强 AP 能力。然而 MySQL 数据结构和散布形式决定了其 AP 能力绝对较弱，那么如何减速 OLAP 查问呢？

为了减速 OLAP 查问，QingCloud MySQL Plus 借用 ClickHouse 来同步 MySQL 数据。

图 2 -1 ClickHouse 产品图

ClickHouse 是一个用于联机剖析 (OLAP) 的列式数据库管理系统 (DBMS)。ClickHouse 构思于 2008 年，最后是为 YandexMetrica（世界第二大 Web 剖析平台）而开发的。多年来始终作为该零碎的外围组件被该零碎继续应用着，并于 2016 年发表开源。

图 2-2 ClickHouse 热度趋势图

从目前最新的 DB-Engines 中能够看到其排名曲线一路低落，并且各大厂在重要业务上曾经大量部署，这是一个很显著的趋势。因而，咱们仿佛能够认定 ClickHouse 的炽热并不只是一时景象，它将短暂地存活上来。而且，ClickHouse 灵便的内部表引擎，可轻松实现与 MySQL 的数据同步，接下来让咱们理解一下。

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 表的映射。从 MySQL 的 information_schema 中获取对应表的构造，将其转换为 ClickHouse 反对的数据结构，此时在 ClickHouse 端，表构造建设胜利。然而此时，并没有真正去同步数据。只有向 ClickHouse 中的该表发动申请时，才会被动的拉取要同步的 MySQL 表的数据。

MySQL Table Engine 应用起来十分简陋，但它是十分有意义的。因为这是第一次买通 ClickHouse 和 MySQL 的数据通道。然而，毛病异样显著：

仅仅是对 MySQL 表关系的映射；
查问时传输 MySQL 数据到 ClickHouse，会给 MySQL 可能造成未知的网络压力和读压力，可能影响 MySQL 在生产中失常应用。

基于 MySQL Table Engine 只能映射 MySQL 表关系的毛病，QingCloud ClickHouse 团队实现了 MySQL Database Engine。

MySQL Database Engine 的个性

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

MySQL Database Engine 是库级别的映射，仍然要从 information_schema 中拉取待同步库中蕴含的所有 MySQL 表的构造，解决了须要建设多表的问题。但依然还有和 MySQL Table Engine 一样的毛病：查问时传输 MySQL 数据到 ClickHouse，给 MySQL 可能造成未知的网络压力和读压力，可能影响 MySQL 在生产中失常应用。

图 2-3 借用第三方软件同步数据

除去下面提到的 MySQL Table Engine、MySQL Database Engine 两种形式，还有能够采纳第三方软件来同步数据，比方 Canal 或者 Kafka，通过解析 MySQL binlog，而后编写程序管制向 ClickHouse 写入。这样做有很大的劣势，即同步流程自主可控。然而也带来了额定的问题：

减少了数据同步的复杂度。
减少了第三方软件，使得运维难度指数级减少。

基于此，咱们又能够思考一个问题，ClickHouse 是否被动同步并订阅 MySQL 数据呢？

为了解决 MySQL Database Engine 仍然存留的问题，反对 ClickHouse 被动同步并订阅 MySQL 数据，QingCloud ClickHouse 团队自主研发了 MaterializeMySQL 引擎。

MaterializeMySQL 引擎是由 QingCloud ClickHouse 团队自主研发的库引擎，目前作为试验个性合并到 ClickHouse 20.8 版本中，是对 MySQL 库级别关系的映射，通过生产 binlog 存储到 MergeTree 的形式来订阅 MySQL 数据。

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

具体应用形式就是一条简略的 CREATE DATABASE SQL 示例：

172.17.0.3:3306 – MySQL 地址和端口
demo – MySQL 库的名称
root – MySQL 同步账户
123 – MySQL 同步账户的明码

Check MySQL Vars
Select history data
Consume new data

MaterializeMySQL 的设计思路如下：

a. 首先测验源端 MySQL 参数是否符合规范。
b. 再将数据依据 GTID 宰割为历史数据和增量数据。
c. 同步历史数据至 GTID 点。
d. 继续生产增量数据。

图 3 -1 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。

_sign：值只有 1 和 -1。其中，1 代表这行数据存在，-1 代表这行数据被删除。
_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);
}

图 4-1 Materialize 实现 HTAP 架构图

当咱们买通了 ClickHouse 和 MySQL 的复制通道，而 ClickHouse 的剖析能力又是如此让人惊喜，那么咱们是不是能够用 MySQL Plus + ClickHouse 实现 HTAP 呢？

在图 4-1 中的架构，仍然应用高可用组件 Xenon 来治理 MySQL 复制，同时 Xenon 减少了对 ClickHouse 的监管，通过 MaterializeMySQL 来同步 MySQL 数据。

在之前的 MySQL Plus 架构图中，应用 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/pro…

[4]. MaterializeMySQL：https://clickhouse.tech/docs/…

正文完

数据库

发表至：数据库

2021-05-19

0

关于数据库:万物皆为向量爱奇艺在线向量召回工程服务化实践

关于数据库:使用模板窗口生成测试数据

关于数据库:CCKS2023基于企业数仓和大语言模型构建面向场景的智能应用

关于数据库:从-Delta-20-开始聊聊我们需要怎样的数据湖

关于模型:看MindSpore加持下如何炼出首个千亿参数中文预训练语言模型

关于数据库:MySQL-到-ClickHouse-的高速公路

背景

1、MySQL 复制的倒退历程

2、QingCloud MySQL Plus

多正本同步复制，确保金融级强一致性

无中心化主动选主且主从秒级切换

跨区容灾能力

ClickHouse 同步 MySQL 数据

1、ClickHouse 概述

2、MySQL Table Engine

3、MySQL Database Engine

4、借用第三方软件同步

Materialize MySQL

1、简述 MaterializeMySQL

2、MaterializeMySQL 的设计思路

3、MaterializeMySQL 的函数流程

HTAP 利用场景

Just My Socks（注册教程内含优惠码）

关于数据库:MySQL-到-ClickHouse-的高速公路

背景

1、MySQL 复制的倒退历程

2、QingCloud MySQL Plus

多正本同步复制，确保金融级强一致性

无中心化主动选主且主从秒级切换

跨区容灾能力

ClickHouse 同步 MySQL 数据

1、ClickHouse 概述

2、MySQL Table Engine

3、MySQL Database Engine

4、借用第三方软件同步

Materialize MySQL

1、简述 MaterializeMySQL

2、MaterializeMySQL 的设计思路

3、MaterializeMySQL 的函数流程

HTAP 利用场景

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）