关于大数据:ByteHouse-MaterializedMySQL-增强优化

更多技术交换、求职机会，欢送关注字节跳动数据平台微信公众号，回复【1】进入官网交换群

前言

社区版 ClickHouse 推出了 MaterializedMySQL 数据库引擎，用于将 MySQL 中的表映射到 ClickHouse 中。ClickHouse 服务作为 MySQL 正本，读取 Binlog 并执行 DDL 和 DML 申请，实现了基于 MySQL Binlog 机制的业务数据库实时同步性能。

这样不依赖其余数据同步工具，就能将 MySQL 整库数据实时同步到 ClickHouse，从而能基于 ClickHouse 构建实时数据仓库。

ByteHouse 是基于 ClickHouse 加强自研的云原生数据仓库，在社区版 ClickHouse 的 MaterializedMySQL 之上进行了性能加强，让数据同步更稳固，反对便捷地解决同步异样问题。

社区版 MaterializedMySQL 简介

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 端简直是毫秒和秒级提早，时效体验十分好。

ByteHouse 性能加强

社区版 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 数据库端须要配置的参数如下。

开启 Binlog

设置默认的认证插件

开启 GTID 模式

• 用户权限  MaterializeMySQL 表引擎用户必须具备 MySQL 库的 RELOAD、REPLICATION SLAVE、REPLICATION CLIENT 以及 SELECT PRIVILEGE 权限  反对的 MySQL 版本 5.65.78.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 库，并查看库同步状态

同步工作治理

库 - 进行同步 / 开始同步

• 创立库后默认是同步状态
• 能够手动进行同步
• 进行中的库能够手动开始同步
  *

  库 - 重置同步
  抉择一个同步库，点击“重置同步”能够从头开始从新同步整库

表 - 重置同步
抉择一个同步中的表 A，点击“重置同步”按钮，零碎将执行以下行为：
1. 敞开同步工作
2. 从源端全量拉取该表的数据至长期表(A_CHTMP, 表名后缀会加上_CHTMP)
3. 删除指标端原有表 A(如果存在)
4. 将长期表 A_CHTMP RENAME 为 A
5. 开始增量同步

删除库
删除 ByteHouse 中的库以及同步关系。

异样解决

零碎运维表

在 ByteHouse 治理控制台，通过下列语句查看工作同步状态和错误信息。

select * from cluster('bytehouse','system.materialize_mysql_status',(1,2)) 
select * from cluster('bytehouse','system.materialize_mysql_log',(1,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;

其余操作

设置跳过 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';

异样报警

ByteHouse 提供监控报警性能，在库同步异样进行或单表同步失败的时候，能够向管理员发送报警信息。

点击跳转 ByteHouse 云原生数据仓库 理解更多