You need to enable JavaScript to run this app.
导航
通过MaterializedMySQL导入
最近更新时间:2023.08.07 18:03:42首次发布时间:2023.08.07 18:03:42

为了强化实时数仓的能力,便于将 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日志。

Parameter

Value

log_bin

ON

binlog_format

ROW

binlog_row_image

FULL

  1. 设置默认的认证插件为mysql_native_password;

Parameter

Value

default_authentication_plugin

mysql_native_password

  1. 开启GTID模式;

Parameter

Value

gtid-mode

ON

enforce-gtid-consistency

ON

配置样例:
在/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_%';

注意

  1. MaterializeMySQL表引擎用户必须具备MySQL库的以下权限:
  • RELOAD
  • REPLICATION SLAVE
  • REPLICATION CLIENT
  • SELECT PRIVILEGE
  1. 当前用户需要拥有mysql.user表的select权限,否则请使用管理员账户通过grand命令(或云数据库后台,如火山引擎MySQL云数据库-修改账号权限)对当前用户授权。
  2. 使用MySQL云数据库(如火山引擎RDS for MySQL)时,通常需要配置访问白名单才允许连接数据库。如果上述授权完成后,仍无法连接数据库,请尝试给云数据库配置访问白名单,ByteHouse节点的IP地址可以在 ByteHouse控制台-集群管理-集群列表-节点管理 查询。

图片

数据准备

新建数据库

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 (...)]

引擎参数说明

参数

说明

host:port

MySQL数据库的URL和端口号

database

MySQL数据库名称

user

MySQL数据库账号

password

账号密码

引擎设置说明

设置

类型

默认值

说明

max_rows_in_buffer

UInt64

65505

内存中允许缓存数据的最大行数(针对单表且缓存数据无法查询)。 当超过这个数字时,数据就会被具体化。 默认值:65505

max_bytes_in_buffer

UInt64

1048576

允许数据在内存中缓存的最大字节数(针对单表且缓存数据无法查询)。 当超过这个数字时,数据就会被具体化。 默认值:1048576

max_flush_data_time

UInt64

1000

允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。 当超过这个时间时,数据就会被具体化。 默认值:1000

max_wait_time_when_mysql_unavailable

Int64

1000

MySQL不可用时的重试间隔(毫秒)。 负值禁用重试。 默认值:1000

allows_query_when_mysql_lost

Bool

true

允许在 MySQL 丢失时查询物化表。 默认值:0(false)

skip_error_count

Int64

0

跳过MaterializedMySQL数据同步中的DML和DDL错误。负值将跳过所有错误。其他值将跳过特定错误。默认值:0

include_tables

String

""

如果配置了该参数,则只会同步符合条件的表。 表名称以逗号分隔。 表名支持正则表达式。 用户可以设置 include_tables 或 except_tables,如果同时设置这两个参数,将会抛出错误。 如果这两个参数都没有设置,数据库将同步所有表。 默认:''
当通过修改数据库设置cmd修改此设置时,它将重新检查新表以执行重新同步操作。

exclude_tables

String

""

如果配置了该参数,则不会同步所有符合条件的表。 表名称以逗号分隔。 表名支持正则表达式。 用户可以设置 include_tables 或 except_tables,如果同时设置这两个参数,将会抛出错误。 如果这两个参数都没有设置,数据库将同步所有表。 默认:''
当通过修改数据库设置cmd修改此设置时,它将重新检查新表以执行重新同步操作。

skip_ddl_patterns

String

""

如果配置了该参数,则所有符合条件的DDL查询都不会被执行。 DDL 模式以逗号分隔。 DDL 模式支持正则表达式并且不区分大小写。 如果该参数未设置或为空,则将执行所有 DDL 查询。

skip_unsupported_ddl

Bool

true

是否跳过不支持的 ddl(CREATE/DROP/RENAME/TRUNCATE TABLE 除外)。

skip_unsupported_tables

Bool

true

是否跳过不支持的表,例如没有主键。

skip_sync_failed_tables

Bool

false

是否跳过同步失败的表,以免阻止整个同步过程。

resync_table_task_schedule_time_ms

UInt64

1000

安排重新同步表任务的毫秒间隔。

resync_table_task_fail_retry_time

UInt64

5

重新同步表失败时的重试时间。

max_insert_wait_seconds_for_unique_table_leader

UInt64

30

unique表成为领导者的最大等待时间。

shard_mode

Bool

false

物化数据库是否分布式同步MySQL数据到ByteHouse

zookeeper_session_expiration_check_period_s

UInt64

60

分片模式下ZooKeeper会话过期检查周期,单位为秒。

execute_dml_fail_max_retry_timeout_s

UInt64

600

执行 dml 操作失败时重试超时,以秒为单位。

retry_execute_dml_sleep_ms

UInt64

2000

重试执行 dml cmd 之间的睡眠时间

inner_query_distributed_ddl_task_timeout

UInt64

30

集群中所有主机的 DDL 查询响应超时。 如果未在所有主机上执行 ddl 请求,则响应将包含超时错误,并且请求将以异步模式执行。 负值意味着无穷大。 零表示异步模式。

max_concurrent_resync_task_num

UInt64

5

最大并发执行resync任务数(针对数据库)。
当物化库中的任务数量达到限制时,新添加的pending resync表将等待其他任务完成。

resync_table_per_task

UInt64

5

一个MaterializeMySQLResyncTask包含的最大resync表数量,每次调度时,都会从MaterializeMySQL数据库中选择resync_table_per_task数量的resync表来生成任务并进行表同步

parallel_in_resync_task

Bool

false

同一resync任务中多个表的全量同步是否可以并行

自定义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>

注意

使用的cluster_name可以通过查询系统运维表system.materialize_mysql_status来获取。

支持情况

数据类型

MySQL

ByteHouse 企业版

TINY

Int8

SHORT,SMALLINT

Int16

MEDIUMINT,INT,INTEGER,INT32

Int32

BIGINT

Int64

LONG

UInt32

LONGLONG

UInt64

FLOAT

Float32

DOUBLE

Float64

DECIMAL,NEWDECIMAL

Decimal

DATE,NEWDATE

Date

DATETIME,TIMESTAMP

DateTime

DATETIME2,TIMESTAMP2

DateTime64

ENUM

Enum

STRING

String

VARCHAR,VAR_STRING

String

BLOB

String

BINARY

FixedString

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

DDL

MySQL DDL

ByteHouse 企业版

CREATE TABLE

✔️

DROP TABLE

✔️

RENAME TABLE

✔️

ADD COLUMN

✔️

DROP COLUMN

✔️

RENAME COLUMN

MODIFY COLUMN

DML

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

系统运维表

system.materialize_mysql_status

配置项

类型

说明

mysql_info

String

MySQL连接信息,格式为ip:port

mysql_database

String

被同步的mysql库名

database

String

云数据库ByteHouse中同步的数据库名

shard_mode

UInt8

是否为分布式模式

cluster_name

String

选择的集群名称

zookeeper_path

String

zookeeper路径

sync_type

String

同步状态,取值说明如下:

  1. PreparingSync:同步准备阶段
  2. FullSync:全量同步阶段
  3. IncrementSync:增量同步阶段
  4. ManualStopSync:手动同步停止
  5. ExceptionStopSync:异常同步停止
  6. MergeResyncTaskSync:执行 resync 任务到主 sync 线程的合并

include_tables

Array

同步包含的表名

exclude_tables

Array

同步排除的表名

resync_tables

Array

正在resync的表

seconds_behind_mysql

Int64

和mysql的主从延时,含义同mysql的Seconds_Behind_Master。当库处于非IncrementSync状态时值为-1

last_committed_position

String

最后commit的binlog位置

last_executed_event

String

最后执行的binlog event内容

total_position

String

MySQL中binlog的最新位置

error_count

UInt64

同步过程中出错的次数

already_skip_errors

UInt64

已经跳过的错误数

last_error_msg

String

最后一次错误的信息

last_error_time

DataTime

最后一次错误的发生时间

sync_failed_tables

Array

同步失败的表

skipped_unsupported_tables

Array

已跳过的不支持的表

分布式模式下,只有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 任务出错的数量;

配置项

类型

说明

mysql_info

String

MySQL连接信息,格式为ip:port

mysql_database

String

被同步的mysql库名

database

String

云数据库ByteHouse中同步的数据库名

shard_mode

UInt8

是否为分布式模式

cluster_name

String

选择的集群名称

zookeeper_path

String

zookeeper路径

sync_type

String

同步状态,取值说明如下:

  1. PreparingSync:同步准备阶段
  2. FullSync:全量同步阶段
  3. IncrementSync:特指增量同步阶段

last_committed_position

String

最后commit的binlog位置

last_executed_event

String

最后执行的binlog event内容

total_position

String

MySQL中binlog的最新位置

error_count

UInt64

同步过程中出错的次数

already_skip_errors

UInt64

已经跳过的错误数

last_error_msg

String

最后一次错误的信息

last_error_time

DataTime

最后一次错误的发生时间

resync_tables_in_task

Array

MaterializeMySQLResyncTask 任务中的待 resync table

sync_failed_tables_in_task

Array

任务中同步失败的表

skipped_unsupported_tables_in_task

Array

任务中已跳过的不支持的表

分布式模式下,只有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>

配置项

类型

说明

database

String

云数据库ByteHouse中同步的数据库。

event_type

String

日志类型,取值说明如下:

  1. ERROR:错误日志
  2. START_SYNC:启动同步
  3. MANUAL_STOP_SYNC:手动停止同步
  4. EXCEPTION_STOP_SYNC:异常停止同步
  5. RESYNC_TABLE:重新同步表

event_date

Date

发生日期

event_time

DateTime

发生时间

resync_table

String

resync表名

exception

String

详细的异常信息

分布式模式下,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系统表查询。

注意

若执行此命令时表处于FullSync状态会跳过此命令。

库同步

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! │ 
└───┴─────┴──────┘