Mysql主数据库OLTP-Clickhouse从数据库OLAP

引言

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语法并拥有开箱即用等诸多优点。

• Row Store & Column Store

  MySQL存储采用的是Row Store，表中数据按照 Row 为逻辑存储单元在存储介质中连续存储。这种存储方式适合随机的增删改查操作，对于按行查询较为友好。但如果选择查询的目标只涉及一行中少数几个属性，Row 存储方式也不得不将所有行全部遍历再筛选出目标属性，当表属性较多时查询效率通常较低。尽管索引以及缓存等优化方案在 OLTP 场景中能够提升一定的效率，但在面对海量数据背景的 OLAP 场景就显得有些力不从心了。

  ClickHouse 则采用的是 Column Store，表中数据按照Column为逻辑存储单元在存储介质中连续存储。这种存储方式适合采用 SIMD (Single Instruction Multiple Data) 并发处理数据，尤其在表属性较多时查询效率明显提升。列存方式中物理相邻的数据类型通常相同，因此天然适合数据压缩从而达到极致的数据压缩比。

  

使用方法

• 部署Master-MySQL 开启BinLog功能：ROW模式 开启GTID模式：解决位点同步时MySQL主从切换问题（BinLog reset导致位点失效）

 # my.cnf关键配置
 gtid_mode=ON
 enforce_gtid_consistency=1
 binlog_format=ROW

• 部署Slave-ClickHouse

mysql8数据库配置

 sudo mkdir -p /home/mysql8dir
 sudo mkdir -p /home/mysql8dir/data
 sudo mkdir -p /home/mysql8dir/conf
 sudo mkdir -p /home/mysql8dir/logs
 ​
 user@:/home/mysql8dir/conf$ cat docker.cnf 
 [mysqld]
 skip-host-cache
 skip-name-resolve
 ​
 user@:/home/mysql8dir$ cat conf/mysql.cnf 
 [mysql]
 ​
 [mysqld]
 default_authentication_plugin = mysql_native_password
 ​
 # 开启GTID模式：解决位点同步时MySQL主从切换问题
 server_id=98
 gtid_mode=on
 enforce_gtid_consistency=on
 ​
 # 开启BinLog功能：ROW模式
 binlog_format=row
 log_bin=mysql-bin
 ​
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 lower_case_table_names=1

 sudo docker pull mysql:8.0.18
 sudo docker run --name mysql8018 -v /home/mysql8dir/data:/var/lib/mysql:rw -v /home/mysql8dir/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=xxxxxx --restart always -p 3306:3306 --privileged=true -d mysql:8.0.18

数据库dump和restore

 docker exec mysql8018 sh -c 'exec mysqldump --all-databases -u root -p"$MYSQL_ROOT_PASSWORD"' > /home/dump.sql
 ​
 docker exec -i mysql8018 sh -c 'exec mysql -u root -p"$MYSQL_ROOT_PASSWORD"' < /home/dump.sql

Mysql57数据库配置

 user:/home/mysqldir$ cat conf/docker.cnf
 [mysqld]
 skip-host-cache
 skip-name-resolve
 ​
 user:/home/mysqldir$ cat conf/mysql.cnf
 [mysql]
 ​
 ​
 [mysqld]
 # 开启GTID模式：解决位点同步时MySQL主从切换问题
 server-id=1
 gtid_mode=ON
 enforce_gtid_consistency=1
 # 开启BinLog功能：ROW模式
 log_bin=mysql_bin
 binlog_format=row
 ​
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 lower_case_table_names=1

数据库dump和restore

 # 导出gasv4数据库到本地dump文件
 sudo docker exec mysql5730 sh -c 'exec mysqldump --databases gasv4 -u root -p"$MYSQL_ROOT_PASSWORD"' > /home/dump.sql
 ​
 # 导入本地gasv4数据库dump文件到数据库
 sudo docker exec -i mysql5730 sh -c 'exec mysql -u root -p"$MYSQL_ROOT_PASSWORD"' < /home/dump.sql

clickhouse数据库配置

 sudo mkdir -p /home/clickhouse2156dir
 sudo mkdir -p /home/clickhouse2156dir/data
 sudo mkdir -p /home/clickhouse2156dir/cfg

 <?xml version="1.0"?>
 <yandex>
     <!-- Profiles of settings. -->
     <profiles>
         <!-- Default settings. -->
         <default>
             <!-- Maximum memory usage for processing single query, in bytes. -->
             <max_memory_usage>10000000000</max_memory_usage>
 ​
         <allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>
         
             <load_balancing>random</load_balancing>
         </default>
 ​
         <!-- Profile that allows only read queries. -->
         <readonly>
             <readonly>1</readonly>
         </readonly>
     </profiles>
 ​
     <!-- Users and ACL. -->
     <users>
         <!-- If user name was not specified, 'default' user is used. -->
         <default>
         <password>xxxxxx</password>
 
             <networks>
                 <ip>::/0</ip>
             </networks>
 ​
             <!-- Settings profile for user. -->
             <profile>default</profile>
 ​
             <!-- Quota for user. -->
             <quota>default</quota>
 ​
             <!-- User can create other users and grant rights to them. -->
             <!-- <access_management>1</access_management> -->
         </default>
     </users>
 ​
     <!-- Quotas. -->
     <quotas>
         <!-- Name of quota. -->
         <default>
             <!-- Limits for time interval. You could specify many intervals with different limits. -->
             <interval>
                 <!-- Length of interval. -->
                 <duration>3600</duration>
 ​
                 <!-- No limits. Just calculate resource usage for time interval. -->
                 <queries>0</queries>
                 <errors>0</errors>
                 <result_rows>0</result_rows>
                 <read_rows>0</read_rows>
                 <execution_time>0</execution_time>
             </interval>
         </default>
     </quotas>
 </yandex>

 sudo docker pull yandex/clickhouse-server:21.5.6
 ​
 $ sudo docker run -d --name clickhouse2156 --restart always -p 8123:8123 -p 9000:9000 --ulimit nofile=262144:262144 -v /home/clickhouse2156dir/data:/var/lib/clickhouse  -v /home/clickhouse2156dir/cfg/users.xml:/etc/clickhouse-server/users.xml yandex/clickhouse-server:21.5.6 
 # 占用端口情况：
 # 8123 --- HTTP
 # 9010 --- TCP
 # 9004 --- mysql
 # 9009 --- 集群内部数据交换备份

clickhouse_slave 创建语法

 # 语法
 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
 ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

引擎参数说明

参数	说明
host:port	MySQL数据库的URL和端口号。
database	MySQL数据库名称。
user	MySQL数据库账号。
password	MySQL数据库账号的密码。

默认新增字段

使用MaterializeMySQL数据库引擎，在ClickHouse集群上新建ReplacingMergeTree引擎的表，会默认在表中增加两个隐藏字段：

字段	说明
_version	事务计数器，记录数据版本信息。UInt64类型。
_sign	删除标记，标记该行是否删除。TypeInt8类型。可选值：1：该行未删除；-1：该行已删除。

支持的类型对应

MySQL	ClickHouse
TINY	Int8
SHORT	Int16
INT24	Int32
LONG	UInt32
LONG	UInt64
FLOAT	Float32
DOUBLE	Float64
DECIMAL，NEWDECIMAL	Decimal
DATE，NEWDATE	Date
DATETIME，TIMESTAMP	DateTime
DATETIME2，TIMESTAMP2	DateTime64
STRING	String
VARCHAR，VAR_STRING	String
BLOB	String
BIT	UInt64
SET	UInt64
ENUM	Enum16
JSON	String
YEAR	String
TIME	String
GEOMETRY	String

其他的MySQL数据类型将全部都转换为字符串，同时以上的所有类型均支持可为空。

使用细则

DDL查询

MySQL DDL查询被转换成相应的ClickHouse DDL查询（ALTER, CREATE, DROP, RENAME）。如果ClickHouse不能解析某些DDL查询，该查询将被忽略。

数据复制

MaterializeMySQL不支持直接插入、删除和更新查询，而是将DDL语句进行相应转换：

• MySQL INSERT查询被转换为INSERT with _sign=1。

• MySQL DELETE查询被转换为INSERT with _sign=-1。

• MySQL UPDATE查询被转换成INSERT with _sign=-1和INSERT with _sign=-1。

SELECT查询

• 如果在SELECT查询中没有指定version，则使用FINAL修饰符，返回version的最大值对应的数据，即最新版本的数据。

• 如果在SELECT查询中没有指定sign，则默认使用WHERE _sign=1，即返回未删除状态（sign=1)的数据。

索引转换

• ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。

• ClickHouse只有一个物理顺序，由ORDER BY子句决定。如果需要创建新的物理顺序，请使用物化视图。

说明

• 带有_sign=-1的行不会从表中物理删除。

• MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。

• 复制很容易被破坏。

• 禁止对数据库和表进行手动操作。

• MaterializeMySQL受optimize_on_insert设置的影响，当MySQL服务器中的一个表发生变化时，数据被合并到MaterializeMySQL数据库中相应的表中。

工作原理

• BinLog Event MySQL中BinLog Event主要包含以下几类：

   1. MYSQL_QUERY_EVENT　　　　-- DDL
   2. MYSQL_WRITE_ROWS_EVENT　-- insert
   3. MYSQL_UPDATE_ROWS_EVENT -- update
   4. MYSQL_DELETE_ROWS_EVENT -- delete

  事务提交后，MySQL 将执行过的 SQL 处理 BinLog Event，并持久化到 BinLog 文件 ClickHouse通过消费BinLog达到数据同步，过程中主要考虑３个方面问题： 1、DDL兼容：由于ClickHouse和MySQL的数据类型定义有区别，DDL语句需要做相应转换 2、Delete/Update 支持：引入_version字段，控制版本信息 3、Query 过滤：引入_sign字段，标记数据有效性

• DDL操作 对比一下MySQL的DDL语句以及在ClickHouse端执行的DDL语句：

   mysql> show create table runoob_tbl\G;
   *************************** 1. row ***************************
   Table: runoob_tbl
   Create Table: CREATE TABLE `runoob_tbl` (
     `runoob_id` int unsigned NOT NULL AUTO_INCREMENT,
     `runoob_` varchar(100) NOT NULL,
     `runoob_author` varchar(40) NOT NULL,
     `submission_date` date DEFAULT NULL,
     PRIMARY KEY (`runoob_id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
   1 row in set (0.00 sec)
   ---------------------------------------------------------------
   cat /metadata/slave_db/runoob_tbl.sql
   ATTACH TABLE _ UUID '14dbff59-930e-4aa8-9f20-ccfddaf78077'
   (
       `runoob_id` UInt32,
       `runoob_` String,
       `runoob_author` String,
       `submission_date` Nullable(Date),
       `_sign` Int8 MATERIALIZED 1,
       `_version` UInt64 MATERIALIZED 1
   )
   ENGINE = ReplacingMergeTree(_version)
   PARTITION BY intDiv(runoob_id, 4294967)
   ORDER BY tuple(runoob_id)
   SETTINGS index_granularity = 8192

  可以看到： 1、在DDL转化时默认增加了2个隐藏字段：_sign(-1删除, 1写入) 和 _version(数据版本) 2、默认将表引擎设置为 ReplacingMergeTree，以 _version 作为 column version 3、原DDL主键字段 runoob_id 作为ClickHouse排序键和分区键 此外还有许多DDL处理，比如增加列、索引等，相应代码在Parsers/MySQL 目录下。

• Delete/Update操作

  Update：

   # Mysql端：
   UPDATE runoob_tbl set runoob_author='Mike' where runoob_id=2;
   ​
   mysql> select * from runoob_tbl;
   +-----------+----------------+---------------+-----------------+
   | runoob_id | runoob_title   | runoob_author | submission_date |
   +-----------+----------------+---------------+-----------------+
   |         1 | MySQL-learning | Bob           | 2021-01-06      |
   |         2 | MySQL-learning | Mike          | 2021-01-06      |
   +-----------+----------------+---------------+-----------------+
   2 rows in set (0.00 sec)
   ​
   ----------------------------------------------------------------
   # ClickHouse端：
   DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id;
   ​
   SELECT
       *,
       _sign,
       _version
   FROM runoob_tbl
   ORDER BY runoob_id ASC
   ​
   Query id: c5f4db0a-eff6-4b49-a429-b55230c26301
   ​
   ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
   │         1 │ MySQL-learning │ Bob           │      2021-01-06 │     1 │        2 │
   │         2 │ MySQL-learning │ Mike          │      2021-01-06 │     1 │        4 │
   │         2 │ MySQL-learning │ Tim           │      2021-01-06 │     1 │        3 │
   └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
   3 rows in set. Elapsed: 0.003 sec.

  可以看到，ClickHouse数据也实时同步了更新操作。

• Delete:

   # Mysql端
   mysql> DELETE from runoob_tbl where runoob_id=2;
   ​
   mysql> select * from runoob_tbl;
   +-----------+----------------+---------------+-----------------+
   | runoob_id | runoob_title   | runoob_author | submission_date |
   +-----------+----------------+---------------+-----------------+
   |         1 | MySQL-learning | Bob           | 2021-01-06      |
   +-----------+----------------+---------------+-----------------+
   1 row in set (0.00 sec)
   ​
   ----------------------------------------------------------------
   # ClickHouse端
   DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id;
   ​
   SELECT
       *,
       _sign,
       _version
   FROM runoob_tbl
   ORDER BY runoob_id ASC
   ​
   Query id: e9cb0574-fcd5-4336-afa3-05f0eb035d97
   ​
   ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
   │         1 │ MySQL-learning │ Bob           │      2021-01-06 │     1 │        2 │
   └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
   ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
   │         2 │ MySQL-learning │ Mike          │      2021-01-06 │    -1 │        5 │
   └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
   ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐
   │         2 │ MySQL-learning │ Mike          │      2021-01-06 │     1 │        4 │
   │         2 │ MySQL-learning │ Tim           │      2021-01-06 │     1 │        3 │
   └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘
   4 rows in set. Elapsed: 0.002 sec.

  可以看到，删除id为2的行只是额外插入了_sign == -1的一行记录，并没有真正删掉。

• 日志回放

  MySQL 主从间数据同步时Slave节点将 BinLog Event 转换成相应的SQL语句，Slave 模拟 Master 写入。类似地，传统第三方插件沿用了MySQL主从模式的BinLog消费方案，即将 Event 解析后转换成 ClickHouse 兼容的 SQL 语句，然后在 ClickHouse 上执行（回放），但整个执行链路较长，通常性能损耗较大。不同的是，MaterializeMySQL 引擎提供的内部数据解析以及回写方案隐去了三方插件的复杂链路。回放时将 BinLog Event 转换成底层 Block 结构，然后直接写入底层存储引擎，接近于物理复制。此方案可以类比于将 BinLog Event 直接回放到 InnoDB 的 Page 中。

参考

MaterializeMySQL引擎 - 云数据库 ClickHouse - 阿里云

MySQL到ClickHouse的高速公路-MaterializeMySQL引擎-云社区-华为云