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

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

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

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

ByteHouse 是基于 ClickHouse 加强自研的云原生数据仓库，在社区版 ClickHouse 的 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 端简直是毫秒和秒级提早，时效体验十分好。

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

用户权限 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());

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

:) 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 参数，来批改同步表范畴。

:) 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 云原生数据仓库理解更多

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

前言

社区版 MaterializedMySQL 简介

同步示例

性能劣势

ByteHouse 性能加强

数据去重

同步范畴

异样解决

分布式模式

可视化运维

最佳实际

源端配置

开启 Binlog

设置默认的认证插件

开启 GTID 模式

源端数据筹备

创立 MaterializeMySQL

查看同步状态

同步工作治理

异样解决

零碎运维表

单表异样复原

其余操作

设置跳过 DDL

批改 include 和 exclude

异样报警