社区版ClickHouse推出了MaterializedMySQL数据库引擎,用于将MySQL中的表映射到ClickHouse中。ClickHouse服务作为MySQL副本,读取Binlog并执行DDL和DML请求,实现了基于MySQL Binlog机制的业务数据库实时同步功能。这样不依赖其他数据同步工具,就能将MySQL整库数据实时同步到ClickHouse,从而能基于ClickHouse构建实时数据仓库。
ByteHouse是基于ClickHouse增强自研的云原生数据仓库,在社区版ClickHouse的MaterializedMySQL之上进行了功能增强,让数据同步更稳定,支持便捷地处理同步异常问题。
文 | 溪溪 来自火山引擎ByteHouse团队
ClickHouse社区版通过DDL语句在ClickHouse上创建一个database,并将MySQL中的指定的一个database的全量数据迁移至ClickHouse,并实时读取MySQL的binlog日志,将MySQL中的增量数据实时同步至ClickHouse中。
详细介绍网址:
https://clickhouse.com/docs/en/engines/database-engines/materialized-mysql
/ 同步示例 /
同步一个MySQL库至ClickHouse的示例创建语句如下:
CREATE DATABASE db_name ENGINE = MaterializedMySQL(...)
SETTINGS materialized_mysql_tables_list='user_table,catalog_sales'
TABLE OVERRIDE user_table(
COLUMNS (
userid UUID,
category LowCardinality(String),
timestamp DateTime CODEC(Delta, Default)
)
PARTITION BY toYear(timestamp)
),
TABLE OVERRIDE catalog_sales(
COLUMNS (
client_ip String TTL created + INTERVAL 72 HOUR
)
SAMPLE BY ip_hash
)
/ 功能优势 /
MaterializedMySQL 数据同步方案的优势有:
● 简单易用:使用一个DDL语句就能创建整库同步任务,能将数百数千张表一键同步至ClickHouse,操作简单。
● 架构简单:使用ClickHouse本身的计算资源进行数据增量同步,无需搭建其他的数据同步工具,数据架构简单。
● 时效性好:支持实时同步源端数据,ClickHouse端几乎是毫秒和秒级延迟,时效体验非常好。
社区版MaterializedMySQL很大程度了解决了MySQL库到ClickHouse之间的数据实时同步问题,但也存在不少问题导致其很难应用到生产应用中,主要问题如下:
● 配置选项少
社区版MaterializedMySQL不支持同步到分布式表,不支持跳过不兼容DDL等功能,缺乏这些功能很难将MaterializedMySQL用于实际应用中。
● 运维困难
社区版MaterializedMySQL不支持同步异常重新同步命令,没有同步状态和日志信息,同步任务失败后很难短时间定位问题和恢复同步。
ByteHouse的MaterializedMySQL功能针对使用过程中的问题和困难,做了多处增强,提高了易用性,降低了运维成本。
/ 数据去重 /
通过MaterializedMySQL同步到ByteHouse的表默认采用HaUniqueMergeTree 表引擎,该表引擎支持配置UNIQUE KEY 唯一键,提供 upsert 更新写语义,源端数据的更新操作在目标端可以实时去重更新。
不需要依赖_version、_sign虚拟列来标记删除更新,简化了业务逻辑,提高了易用性。
/ 同步范围 /
通过SETTINGS 参数中配置include_tables和exclude_tables 列表,指定该数据库下需要同步的表清单或者不需要同步的表清单,否则同步该库所有的表。
在实际应用中,一个数据库通常有数百乃至数千张表,其中有些表无需同步、或者数据可能存在异常,可以将这些表加入exclude_tables 清单,不影响其他表的数据同步。
/ 异常处理 /
数据同步链路无法避免发生异常情况导致同步中断,ByteHouse提高了多个功能来简化异常问题处理。
● 跳过不支持的语句
MySQL支持的DDL语句非常丰富,有很多语法与clickhouse不兼容,在ClickHouse端执行会报错中断同步任务。可以通过设置skip_ddl_patterns参数,用1个或多个正则表达式将匹配的DDL语句过滤掉,从而避免了报错和中断同步任务。
● 系统日志表
ByteHouse提供两个系统表:system.materialize_mysql_status,system.materialize_mysql_log,分别记录了每个同步任务的状态,参数设置和运行日志。便于实时查看同步状态和排查异常问题。
● 出错后运维
当同步任务出现了同步异常后,通过查看运行日志系统表定为问题。针对性处理了异常问题后,通过resync 命令重启同步任务。
/ 分布式模式 /
社区版MaterializedMySQL的每个同步任务会将源端的一个库同步至ClickHouse的某个节点,不支持按分片逻辑将数据分布到所有节点,无法利用ClickHouse集群的分布式计算存储能力;如果在集群中每个节点都建一个同步库,则源端一份数据会被同步一份全量至每个ClickHouse节点,既浪费了存储空间,降低了查询性能,又会对源端产生巨大的压力。
ByteHouse支持构建分布式模式的MaterializedMySQL库,将每个表都对应同步至ByteHouse的一个分布式表,数据不重复存储,能充分利用分布式集群的计算能力,又降低了对源端的同步压力。
/ 可视化运维 /
ByteHouse同时提供了可视化运维模块,能实时查看同步状态,暴露同步异常,支持在线修复同步异常问题和重启同步任务。
下面将演示将MySQL库中的若干张表同步至ByteHouse的全过程。
/ 源端配置 /
在MySQL数据库端需要配置的参数如下:
1. 开启Binlog
Parameter | Value |
log_bin | ON |
binlog_format | ROW |
binlog_row_image | FULL |
2. 设置默认的认证插件
Parameter | Value |
default_authentication_plugin | mysql_native_password |
3. 开启GTID模式
Parameter | Value |
gtid-mode | ON |
enforce-gtid-consistency | ON |
4.用户权限
MaterializeMySQL表引擎用户必须具备MySQL库的RELOAD、REPLICATION SLAVE、REPLICATION CLIENT以及SELECT PRIVILEGE权限。
支持的MySQL版本:5.6、5.7、8.0
/ 源端数据准备 /
在MySQL数据库里面创建一个database,创建两张表,并插入若干数据。
Show databases;
--【MySQL】Mysql中创建库
create database db;
use db;
--【MySQL】Mysql中创建表
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='user info';
CREATE TABLE `data` (
`id` bigint(20) unsigned NOT NULL,
`date_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='time';
--【MySQL】Mysql中插入数据
INSERT INTO yangxi.user(id,name) VALUES (111,'step1-1111'),(222,'step1-2222');
INSERT INTO yangxi.data(id,date_time) VALUES (111,now()),(222,now()),(333,now());
/ 创建MaterializeMySQL /
在ByteHouse的控制台数据查询窗口,创建MaterializeMySQL库。
#【worksheet】创建物化Mysql库,
--【Clickhouse】在集群名称是 bytehouse的集群上创建物化库,集群名称是个变量
CREATE DATABASE shard_mode_true_mysql_sync on cluster bytehouse
ENGINE = MaterializeMySQL('10.137.xx.xx:3309', 'db', 'username', 'password')
settings shard_mode=true,allows_query_when_mysql_lost=1,include_tables='user,data'
TABLE OVERRIDE data( PARTITION BY toYYYYMMDD(date_time)) ;
参数解释:
● shard_mode:true表示是同步至分布式表。
● allows_query_when_mysql_lost:1表示同步中断的时候也允许查询数据。
● include_tables:同步源端db库中user和data两张表,其他表跳过不同步。
● OVERRIDE :ByteHouse中的data表按照date_time字段分区。
/ 查看同步状态 /
切换到ByteHouse数据管理模块,搜索shard_mode_true_mysql_sync 库,并查看库同步状态。
/ 同步任务管理 /
1. 库-停止同步/开始同步
● 创建库后默认是同步状态
● 可以手动停止同步
● 停止中的库可以手动开始同步
2. 库-重置同步
选择一个同步库,点击“重置同步”可以从头开始重新同步整库
3. 表-重置同步
选择一个同步中的表A,点击“重置同步”按钮,系统将执行以下行为:
● 关闭同步任务
● 从源端全量拉取该表的数据至临时表(A_CHTMP,表名后缀会加上_CHTMP)
● 删除目标端原有表A(如果存在)
● 将临时表A_CHTMP RENAME为A
● 开始增量同步
4. 删除库
删除ByteHouse中的库以及同步关系。
/ 异常处理 /
1. 系统运维表
在ByteHouse管理控制台,通过下列语句查看任务同步状态和错误信息。
select * from cluster('bytehouse','system.materialize_mysql_status',(1,2))
select * from cluster('bytehouse','system.materialize_mysql_log',(1,2))
2. 单表异常恢复
在源端执行下列Alter table 语句以后,库同步会失败。
--修改字段类型
mysql> ALTER TABLE db.test ADD COLUMN h tinyint;
mysql> ALTER TABLE db.test MODIFY h int default 0;
mysql>ALTER TABLE db.test MODIFY h tinyint default 0;
恢复办法:在ByteHouse界面上进入表详情,点击重新同步按钮。
进入库详情页面,点击开始同步按钮,即可恢复同步。
在ByteHouse中执行下列语句,也可以恢复数据同步
--通过下述命令,或者可视化界面,可以重启同步
--shard_mode=true情况
:) system resync materialize mysql table on cluster bytehouse shard_mode_true_mysql_sync.test;
:) system restart sync materialize mysql on cluster bytehouse shard_mode_true_mysql_sync;
3.其他操作
● 设置跳过DDL
:) CREATE DATABASE db_mysql_sync_skip on cluster bytehouse
ENGINE = MaterializeMySQL('10.xx.xx.xx:3309', 'db', 'username', 'password')
SETTINGS include_tables='user,date_time',skip_ddl_patterns='.*add column.*,.*MODIFY.*'
TABLE OVERRIDE data( PARTITION BY toYYYYMMDD(date_time)) ;
● 修改include和exclude
通过下列语句修改include和exclude参数,来修改同步表范围。
:) alter database shard_mode_true_mysql_sync on cluster bytehouse modify setting include_tables='user,data,date_time,test';
:) alter database shard_mode_true_mysql_sync on cluster bytehouse
modify setting include_tables='',exclude_tables='test3';
4. 异常报警
ByteHouse提供监控报警功能,在库同步异常停止或单表同步失败的时候,可以向管理员发送报警信息。
产品介绍
火山引擎ByteHouse
统一的大数据分析平台。目前提供企业版和云数仓两种版本,企业版是基于开源的企业级分析型数据库,支持用户交互式分析PB级别数据,通过多种自研表引擎,灵活支持各类数据分析和应用;云数仓版作为云原生的数据分析平台,实现统一的离线和实时数据分析,并通过弹性扩展的计算层和分布式存储层,有效降低企业大数据分析。