引言
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达到数据同步,过程中主要考虑3个方面问题: 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 中。