更多技术沟通、求职时机，欢迎重视字节跳动数据平台微信公众号，回复【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 中。

具体介绍：[experimental] MaterializedMySQL | ClickHouse Docs

同步示例

同步一个 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 一起供给了可视化运维模块，能实时检查同步状况，露出同步反常，支撑在线修正同步反常问题和重启同步使命。

ByteHouse MaterializedMySQL 增强优化

ByteHouse MaterializedMySQL 增强优化

最佳实践

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

源端装备

在 MySQL 数据库端需求装备的参数如下。

敞开 Binlog

ByteHouse MaterializedMySQL 增强优化

设置默许的认证插件

ByteHouse MaterializedMySQL 增强优化

敞开 GTID 形式

ByteHouse MaterializedMySQL 增强优化

  • 用户权限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 库，并检查库同步状况

ByteHouse MaterializedMySQL 增强优化

同步使命办理

库-中止同步/开端同步

  • 创立库后默许是同步状况
  • 能够手动中止同步
  • 中止中的库能够手动开端同步

ByteHouse MaterializedMySQL 增强优化

库-重置同步

挑选一个同步库，点击“重置同步”能够从头开端从头同步整库

表-重置同步

挑选一个同步中的表 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 MaterializedMySQL 增强优化

康复方法：

在 ByteHouse 界面上进入表概况，点击从头同步按钮。

ByteHouse MaterializedMySQL 增强优化

进入库概况页面，点击开端同步按钮，即可康复同步。

ByteHouse MaterializedMySQL 增强优化

在 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云原生数据仓库 了解更多