通过MaterializedMySQL导入

为了强化实时数仓的能力，便于将 MySQL 中的表映射到 ByteHouse 企业版中，ByteHouse 引入了MaterializedMySQL 数据库引擎，ByteHouse 服务作为MySQL副本，可以读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。

ByteHouse 企业版在实现 MaterializedMySQL 时，底层引擎采用了自研的 HaUniqueMergeTree 引擎，支持自定义版本字段以及根据 UNIQUE KEY 实时删除数据功能，无需引入其他额外字段。同时，ByteHouse 增强了 MaterializedMySQL 引擎的稳定性和易用性。
相对于原生MaterializedMySQL引擎，ByteHouse有如下优势：

1. 简化流程：数据实时去重更新，简化业务逻辑。
2. 精确范围：数据同步范围可选可改，实际应用更便捷。
3. 稳定同步：优化异常问题处理，尽量避免异常情况导致同步任务中断。
4. 源端减压：数据能分片存储到所有节点，一库只同步一次，降低源端同步压力。
5. 直观运维：提供可视化运维模块，实时查看同步状态，支持在线修复同步异常问题和重启同步任务。

禁止操作底表

默认禁止对MaterializedMySQL Database中的表直接进行操作，可通过session参数set force_manipulate_materialize_mysql_table = 1, distributed_ddl_entry_format_version = 2操作底表。

下面将演示将MySQL库中的若干张表同步至ByteHouse的全过程。
当前支持的 MySQL 版本为 5.6、5.7、8.0。

源端配置

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

1. 开启 MySQL Binlog；

登录MySQL查看是否开启Binlog日志

[root@node1 ~]# mysql -u root -password
mysql> show variables like 'log_%';

如果查询结果 log_bin字段为off则说明没有开启Binlog日志。

2. 设置默认的认证插件为mysql_native_password；

3. 开启GTID模式；

配置样例：
在/etc/my.cnf文件中[mysqld]下写入以下配置。

[mysqld] 
#指定一个不重名的server-id
server-id=123 
#配置Binlog的日志目录
log-bin=/var/lib/mysql/mysql-bin 
#设置Binglog的格式为Row 
binlog_format=ROW 
binlog_row_image=FULL
*#设置默认的认证插件为mysql_native_password
default_authentication_plugin=mysql_native_password
#开启GTID模式
gtid-mode=on
enforce-gtid-consistency=1

重启MySQL 服务，可以重新查看Binlog日志情况。

[root@node1 ~]# service mysqld restart
[root@node1 ~]# mysql -u root -password
mysql> show variables like 'log_%';

数据准备

新建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
[TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]

引擎参数说明

引擎设置说明

自定义schema(表重写 Table Overrides)

表覆盖可用于自定义ByteHouse DDL查询，从而允许您对应用程序进行模式优化。这对于控制分区特别有用，分区对MaterializedMySQL的整体性能非常重要。
您可以对MaterializedMySQL表重写的模式转换进行以下操作:

• 修改列类型。必须与原始类型兼容，否则复制将失败。例如，可以将UInt32列修改为UInt64，不能将 String 列修改为 Array(String)。
• 修改 column TTL.
• 修改 column compression codec
• 增加 ALIAS columns
• 增加 skipping indexes
• 增加 projections
• 修改 PARTITION BY
• 修改 ORDER BY
• 修改 PRIMARY KEY
• 增加 SAMPLE BY
• 增加 table TTL

配置使用的集群名称

当使用分布式模式时，默认会使用配置文件中的第一个集群信息，也可以通过在config文件中配置以下信息来指定集群信息：

<materialize_mysql><primary_cluster_name>...</primary_cluster_name></materialize_mysql>

数据类型

以上所有类型均支持为可空(nullable)。

DDL

DML

ByteHouse企业版 底层引擎采用 HaUniqueMergeTree，因此直接支持insert、update和delete操作。

系统运维表

system.materialize_mysql_status

分布式模式下，只有leader节点可以查询到该信息，因此想要获取这个系统表的信息需要执行以下SQL：

select * from cluster('<cluster_name>', system.materialize_mysql_status, (1,2))

system.materialize_mysql_resync_task_status

部分列与 system.materialize_mysql_status 有相同的语义，但是指示范围不同。如 error_count：

• system.materialize_mysql_status 中的 error_count 包含主 sync 线程与 resync 任务出错的数量；
• system.materialize_mysql_resync_task_status 中的 error_count 仅包含当前 resync 任务出错的数量；

分布式模式下，只有leader节点可以查询到该信息，因此想要获取这个系统表的信息需要执行以下SQL：

select * from cluster('<cluster_name>', system.materialize_mysql_resync_task_status, (1,2))

system.materialize_mysql_log

需要在config文件中配置如下：

<materialize_mysql_log>
    <database>system</database>
    <table>materialize_mysql_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</materialize_mysql_log>

分布式模式下，log只会记录在leader节点上，因此想要获取这个系统表的信息需要执行以下SQL：

select * from cluster('<cluster_name>', system.materialize_mysql_log, (1,2)) order by event_time desc limit 10;

系统命令

修改库参数

shard_mode = 0: alter database dbmodify settingsetting_name=value [, ...]
shard_mode = 1: alter database dbon clustercluster_name modify setting setting_name=value [, ...]
修改include_tables和exclude_tables时，引擎会检查互斥性，即不能同时设置include_tables和exclude_tables。此外，引擎会检查参数调整后是否有新的表需要同步，如果有，操作同下面的表同步。
注意：不允许通过此方式修改shard_mode参数

表同步

shard_mode = 0: system [stop] resync materialize mysql table db.table
shard_mode = 1: system [stop] resync materialize mysql table on cluster cluster_name db.table
此操作会重新/取消全量同步表，引擎会维护一份待resync table的列表并有一个后台线程来异步执行resync过程，待resync table的列表可以通过system.materialize_mysql_status系统表查询。

库同步

shard_mode = 0: system start/stop/restart sync materialize mysql db
shard_mode = 1: system start/stop/restart sync materialize mysql on cluster cluster_name db
手动启动/停止/重启库同步任务，当库处于TableSync状态时仅会标记状态。

MySQL中查询

在MySQL中执行以下SQL：

mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, b INT);
mysql> INSERT INTO db.test VALUES (1, 11), (2, 22);
mysql> DELETE FROM db.test WHERE a=1;
mysql> ALTER TABLE db.test ADD COLUMN c VARCHAR(16);
mysql> UPDATE db.test SET c='Wow!', b=222;
mysql> SELECT * FROM test;

查询结果：
+---+------+------+
| a |    b |    c |
+---+------+------+
| 2 |  222 | Wow! |
+---+------+------+

ByteHouse 企业版与MySQL服务器交换数据

创建数据库并查询表：

CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***');
SHOW TABLES FROM mysql;

查询结果：
┌─name─┐
│ test │
└──────┘

MySQL插入数据后，在ByteHouse中查询数据：

SELECT * FROM mysql.test;

查询结果：
┌─a─┬──b─┐ 
│ 1 │ 11 │ 
│ 2 │ 22 │ 
└───┴────┘

MySQL删除数据、添加列并更新后，在ByteHouse中查询数据：

SELECT * FROM mysql.test;

查询结果：
┌─a─┬───b─┬─c────┐ 
│ 2 │ 222 │ Wow! │ 
└───┴─────┴──────┘