只需五步，ByteHouse实现MaterializedMySQL能力增强 - 文章 - 开发者社区

picture.image

社区版ClickHouse推出了 MaterializedMySQL数据库引擎，用于将MySQL中的表映射到ClickHouse中。ClickHouse服务作为MySQL副本，读取Binlog并执行DDL和DML请求，实现了基于MySQL Binlog机制的业务数据库实时同步功能。 这样不依赖其他数据同步工具，就能将MySQL整库数据实时同步到ClickHouse，从而能基于ClickHouse构建实时数据仓库。

ByteHouse是基于ClickHouse增强自研的云原生数据仓库，在社区版ClickHouse的MaterializedMySQL之上进行了功能增强， 让数据同步更稳定，支持便捷地处理同步异常问题。

picture.image

文 | 溪溪

来自火山引擎ByteHouse团队

picture.image

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端几乎是毫秒和秒级延迟，时效体验非常好。

picture.image

社区版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_patte rns参数，用1个或多个正则表达式将匹配的DDL语句过滤掉，从而避免了报错和中断同步任务。

●

系统日志表

ByteHouse提供两个系统表：system.materialize_mysql_status，system.materialize_mysql_log，分别记录了每个同步任务的状态，参数设置和运行日志。便于实时查看同步状态和排查异常问题。

● 出错后运维

当同步任务出现了同步异常后，通过查看运行日志系统表定为问题。针对性处理了异常问题后，通过resync 命令重启同步任务。

/ 分布式模式 /

社区版MaterializedMySQL的每个同步任务会将源端的一个库同步至ClickHouse的某个节点，不支持按分片逻辑将数据分布到所有节点，无法利用ClickHouse集群的分布式计算存储能力；如果在集群中每个节点都建一个同步库，则源端一份数据会被同步一份全量至每个ClickHouse节点，既浪费了存储空间，降低了查询性能，又会对源端产生巨大的压力。

ByteHouse支持构建分布式模式的MaterializedMySQL库，将每个表都对应同步至ByteHouse的一个分布式表， 数据不重复存储，能充分利用分布式集群的计算能力，又降低了对源端的同步压力。

/ 可视化运维 /

ByteHouse同时提供了 可视化运维模块， 能实时查看同步状态，暴露同步异常，支持在线修复同步异常问题和重启同步任务。

picture.image

下面将演示 将MySQL库中的若干张表同步至ByteHouse 的全过程。

/ 源端配置 /

在MySQL数据库端需要配置的参数如下：

1. 开启Binlog



Parameter

Value


log_bin

| ON

| | binlog _forma t

| 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数据库里面创建一个data base，创建两张表，并插入若干数据。


        
 
  `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 库，并查看库同步状态。

picture.image

/ 同步任务管理 /

1. 库-停止同步/开始同步

● 创建库后默认是同步状态

● 可以手动停止同步

● 停止中的库可以手动开始同步

picture.image

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;`

picture.image

恢复办法： 在ByteHouse界面上进入表详情，点击重新同步按钮。

picture.image

进入库详情页面，点击开始同步按钮，即可恢复同步。

picture.image

在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级别数据，通过多种自研表引擎，灵活支持各类数据分析和应用；云数仓版作为云原生的数据分析平台，实现统一的离线和实时数据分析，并通过弹性扩展的计算层和分布式存储层，有效降低企业大数据分析。后台回复数字“6”了解产品

picture.image