为了强化实时数仓的能力，便于将 MySQL 中的表映射到 ByteHouse 企业版中，ByteHouse 引入了MaterializedMySQL 数据库引擎，ByteHouse 服务作为 MySQL 副本，可以读取 Binlog 并执行 DDL 和 DML 请求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能，适用于需要将 MySQL 业务数据实时同步到 ByteHouse 企业版进行分析的场景。本文介绍通过 MaterializedMySQL 引擎将 MySQL 数据导入 ByteHouse 企业版的语法、操作步骤及注意事项。

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

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

• 当前支持的 MySQL 版本为 5.6、5.7、8.0。
• 操作底表：默认禁止对 MaterializedMySQL 引擎数据库中的表直接进行操作。如需调整表内容，可在 ByteHouse 中执行查询操作（如 INSERT、ALTER 等）时，通过会话级（session）参数 set force_manipulate_materialize_mysql_table = 1, distributed_ddl_entry_format_version = 2 ** 操作底表。 调整后，仅 ByteHouse MaterializedMySQL 引擎数据库中的表受影响，MySQL 源表不受影响。
• 禁止创建底表副本：MaterializedMySQL 引擎数据库中表的 ZooKeeper path 为 /clickhouse/[database_name]/[table_name]/UUID，replica 为{replica}，UUID 是随机生成的。MaterializedMySQL 中的表默认以单副本形式存在，禁止对这些表创建其他副本以形成多副本，否则对其他新增副本的操作将会间接影响 MaterializedMySQL 中的表，可能导致数据一致性问题。

• ByteHouse 支持通过公网或私网连接 MySQL 源表，执行导入操作前，请确保 ByteHouse 与 MySQL 源之间网络通畅且稳定。
• 如果您使用公网访问，可能产生相关费用，具体计费规则以各产品官方文档及实际账单为准，请提前确认计费模式。

• 如果您使用的是 MySQL 云数据库，通常需要配置访问白名单才允许连接外部数据库。请在 MySQL 云数据库中将 ByteHouse 集群节点的 IP 地址添加至白名单中。如果您使用的是如火山引擎 RDS for MySQL，配置访问白名单操作详情请参考配置访问白名单。
  ByteHouse 节点的 IP 地址可以通过 ByteHouse 企业版控制台 > 集群管理 > 集群列表 > 节点管理 路径获取。
  
• 请确保您使用的 MySQL 用户具有以下 MySQL 库权限：
  • RELOAD
  • REPLICATION SLAVE
  • REPLICATION CLIENT
  • SELECT PRIVILEGE
  • mysql.user 表的 SELECT 权限

创建 MaterializedMySQL 数据库 #

在 ByteHouse 中新建库引擎为 MaterializedMySQL 的数据库，创建完成后，系统将自动读取源数据，语法如下：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password')
[SETTINGS ...]
[TABLE OVERRIDE table_name (
    [COLUMNS (
        [name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], ...]
        [INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, ...]
        [PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]), ...]
        )]
        [ORDER BY expr]
        [PRIMARY KEY expr]
        [PARTITION BY expr]
        [SAMPLE BY expr]
        [TTL expr]
), ...]

参数说明 #

Settings 参数说明 #

您可按需配置以下 settings 参数：

自定义 schema（表重写 TABLE OVERRIDE） #

表重写（TABLE OVERRIDE）可用于自定义 ByteHouse DDL 查询，在无需调整 MySQL 源数据的前提下，调整 ByteHouse 中 MaterializedMySQL 引擎数据库内的表结构规则。
当 MySQL 源数据的表结构、分区方式不符合 ByteHouse 性能需求时，您可以在创建 ByteHouse MaterializedMySQL 数据库时使用 TABLE OVERRIDE 针对性修改某些表的规则。该操作不会影响 MySQL 源数据，也无需修改所有同步表，您可按需指定目标表并配置规则。
该功能常见的使用场景为控制分区，例如 MySQL 源数据未对数据进行分区，同步到 ByteHouse 后想按时间分区查询效率，可使用 TABLE OVERRIDE...PARTITION BY 指定分区，提升查询性能。
您可以对 MaterializedMySQL 表重写的模式转换进行以下操作:

• 修改列类型：必须与原始类型兼容，否则复制将失败。例如，可以将 UInt32 列修改为 UInt64，不能将 String 列修改为 Array(String)。MySQL 与 ByteHouse 数据类型的映射关系请参见下文数据类型。
• 修改列级 TTL（column TTL）
• 修改列的压缩编码（column compression codec）
• 增加别名列（ALIAS columns）
• 增加条数索引（skipping indexes）
• 增加投影（projections）
• 修改分区键（PARTITION BY）
• 修改排序键（ORDER BY）
• 修改主键（PRIMARY KEY）
• 增加采样规则（SAMPLE BY）
• 增加表级 TTL（table TTL）

使用示例 #

CREATE DATABASE demo_db_mysql
ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***')
SETTINGS parallel_in_resync_task = true, shard_mode = 1
TABLE OVERRIDE table1 (
    COLUMNS (
        userid UUID,
        category LowCardinality(String),
        timestamp DateTime CODEC(Delta, Default)
    )
    PARTITION BY toYear(timestamp)
),
TABLE OVERRIDE table2 (
    COLUMNS (
        ip_hash UInt32 MATERIALIZED xxHash32(client_ip),
        client_ip String TTL created + INTERVAL 72 HOUR
    )
    SAMPLE BY ip_hash
);

修改库参数 #

ByteHouse MaterializedMySQL 引擎数据库创建完成后，您可通过 ALTER 语句修改数据库的配置参数。

ALTER DATABASE db_name ON CLUSTER cluster_name MODIFY SETTING setting_name=value [, ...] 

ALTER DATABASE demo_db_mysql ON CLUSTER bytehouse_demo_cluster MODIFY SETTING exclude_tables = 'table1,table2';

ALTER DATABASE db_name MODIFY SETTING setting_name=value [, ...]

ALTER DATABASE demo_db_mysql MODIFY SETTING exclude_tables = 'table1,table2';

手动启动/停止/重启库同步 #

ByteHouse 支持您手动启动/停止/重启库同步任务。

system start/stop/restart sync materialize mysql on cluster cluster_name db 

system start sync materialize mysql on cluster bytehouse_demo_cluster demo_db_mysql

system stop sync materialize mysql on cluster bytehouse_demo_cluster demo_db_mysql

system restart sync materialize mysql on cluster bytehouse_demo_cluster demo_db_mysql

system start/stop/restart sync materialize mysql db

system start sync materialize mysql demo_db_mysql

system stop sync materialize mysql demo_db_mysql

system restart sync materialize mysql demo_db_mysql

表同步 #

ByteHouse 支持重新开始和取消重新开始全量同步表。如果同步过程中出现表异常，任务状态进入 sync_failed，您可使用执行表同步命令，恢复该表的同步流程。ByteHouse MaterializedMySQL 引擎会维护一份待重新同步表（resync table）的列表，并有一个后台线程来异步执行重新同步过程。您可通过system.materialize_mysql_status 系统表查询同步失败的表和待重新同步表的列表，该系统表的查询方式请参见system.materialize_mysql_status。

system [stop] resync materialize mysql table on cluster cluster_name db.table

system stop resync materialize mysql table on cluster bytehouse_demo_cluster demo_db_mysql.demo_table

system resync materialize mysql table on cluster bytehouse_demo_cluster demo_db_mysql.demo_table

system [stop] resync materialize mysql table db.table

system stop resync materialize mysql table demo_db_mysql.demo_table

system resync materialize mysql table demo_db_mysql.demo_table

步骤一：准备工作 #

MySQL 数据库端需确保已配置以下参数，确保已开启 Binlog，binlog_format 为 ROW 格式，开启 GTID 模式（全局事务 ID）。

1. 登录 MySQL 并查看是否开启 Binlog 日志。

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

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

   参数	参数值
   log_bin	ON

2. 如果 binlog 已开启，请确认 binlog_format 和 binlog_row_image 为如下配置：

   mysql> show variables like 'binlog_format';
   mysql> show variables like 'binlog_row_image';
   

   参数	参数值
   binlog_format	ROW
   binlog_row_image	FULL

3. 查看默认的认证插件设置是否为 mysql_native_password。

   mysql> show variables like 'default_authentication_plugin';
   

   参数	参数值
   default_authentication_plugin	mysql_native_password

4. 确认是否开启 GTID 模式。

   mysql> show variables like 'gtid_mode';
   mysql> show variables like 'enforce_gtid_consistency';
   

   参数	参数值
   gtid_mode	ON
   enforce_gtid_consistency	ON

如果以上参数均未配置，可参考如下配置样例设置参数：
在 MySQL 的 /etc/my.cnf 文件中 [mysqld] 下写入以下配置。

[mysqld] 
# 指定一个不重名的 server-id
server-id=123 
# 配置 Binlog 的日志目录
log-bin=/var/lib/mysql/mysql-bin 
# 设置 Binlog 的格式为 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_%';
mysql> show variables like 'binlog_format';
mysql> show variables like 'binlog_row_image';
mysql> show variables like 'default_authentication_plugin';
mysql> show variables like 'gtid_mode';
mysql> show variables like 'enforce_gtid_consistency';

步骤二：创建源库 #

在 MySQL 中执行以下 SQL，创建 MySQL 源库、表。

mysql> CREATE DATABASE mysql_source;
mysql> CREATE TABLE mysql_source.test (a INT PRIMARY KEY, b INT);

步骤三：ByteHouse 连接至 MySQL #

在 ByteHouse 中创建 MaterializedMySQL 数据库并查询库，此时可查询到已同步 MySQL 源库中的表至 ByteHouse。使用时，请使用实际参数值替换命令示例中的占位符，参数配置说明请参见参数说明。

CREATE DATABASE demo_db_mysql
ENGINE = MaterializeMySQL('192.xx.xx.xxx:3306', 'mysql_source', 'user_name', 'password')
SETTINGS shard_mode = 1;

SHOW TABLES FROM demo_db_mysql;

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

步骤四：测试 MySQL 数据同步 #

• 测试创建表和插入数据
  在 MySQL 中创建表，并插入数据：

  mysql> INSERT INTO mysql_source.test VALUES (1, 11), (2, 22);
  

  在 ByteHouse 中查询同步情况，结果与 MySQL 源表的查询结果一致。

  SELECT * FROM demo_db_mysql.test;
  
  查询结果：
  ┌─a─┬──b─┐ 
  │ 1 │ 11 │ 
  │ 2 │ 22 │ 
  └───┴────┘
  

• 测试删除数据、添加列并更新数据
  在 MySQL 源表中删除数据、添加列并更新数据。

  mysql> DELETE FROM mysql_source.test WHERE a=1;
  mysql> ALTER TABLE mysql_source.test ADD COLUMN c VARCHAR(16);
  mysql> UPDATE mysql_source.test SET c='Wow!', b=222;
  mysql> SELECT * FROM mysql_source.test;
  
  查询结果：
  +---+------+------+
  | a |    b |    c |
  +---+------+------+
  | 2 |  222 | Wow! |
  +---+------+------+
  

  在 ByteHouse 中查询同步情况，结果与 MySQL 源表的查询结果一致。

  SELECT * FROM demo_db_mysql.test;
  
  查询结果：
  ┌─a─┬───b─┬─c────┐ 
  │ 2 │ 222 │ Wow! │ 
  └───┴─────┴──────┘
  

高阶用法：查看系统表 #

您可通过查看系统表，了解 MaterializedMySQL 数据库的同步信息、同步任务状态、重新同步任务状态、异常信息、日志等，协助排障，详情请参见下文相关参考章节：

• system.materialize_mysql_status：查看 MaterializedMySQL 数据库状态。
• system.materialize_mysql_resync_task_status：查看重新同步任务状态。
• system.materialize_mysql_log：查看日志信息。

数据类型 #

所有数据类型均支持设置为可为空（nullable）。

DDL #

RENAME COLUMN 和 MODIFY COLUMN 依赖于 ByteHouse 企业版引擎版本，需为 2.4 及以上版本，您可登录 ByteHouse 企业版控制台，通过 集群管理 > 集群列表 > 集群名称 > 基本信息路径，查看引擎版本是否符合要求。

DML #

ByteHouse 企业版底层引擎采用 HaUniqueMergeTree 引擎，因此直接支持 INSERT、UPDATE 和 DELETE 操作。

system.materialize_mysql_status #

您可使用该系统表查询 MaterialMySQL 数据库的状态。

SELECT * FROM system.materialize_mysql_status

分布式模式（shard_mode=1）下，只有 leader 节点可以查询到该信息，如需获取该系统表的信息需要执行以下 SQL：

SELECT * FROM CLUSTER('<cluster_name>', system.materialize_mysql_status, (1,2))

列名说明

system.materialize_mysql_resync_task_status #

您可使用该系统表查询 MaterialMySQL 数据库中数据同步任务的状态。

SELECT * FROM system.materialize_mysql_resync_task_status

分布式模式（shard_mode=1）下，只有 leader 节点可以查询到该信息，如需获取该系统表的信息需要执行以下 SQL：

SELECT * FROM CLUSTER('<cluster_name>', system.materialize_mysql_resync_task_status, (1,2))

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

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

system.materialize_mysql_log #

您可以使用该系统表查询 MaterialMySQL 数据库中数据同步任务的日志。
使用前，需要在 config.xml 文件中配置如下，详情请参考配置集群参数。

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

查询方式：

SELECT * FROM system.materialize_mysql_log ORDER BY event_time DESC limit 10;

分布式模式（shard_mode=1）下，log 只会记录在 leader 节点上，如需获取该系统表的信息需要执行以下 SQL：

SELECT * FROM CLUSTER('<cluster_name>', system.materialize_mysql_log, (1,2)) ORDER BY event_time DESC limit 10;

列名说明