全部产品
Search
文档中心

:MaterializeMySQL引擎

更新时间:Sep 05, 2023

MaterializeMySQL引擎支持将MySQL(自建MySQL、RDS MySQL或PolarDB MySQL )数据同步到云数据库ClickHouse,且注意事项和操作步骤一致。本文以云数据库RDS为例介绍如何从RDS MySQL中同步数据至云数据库ClickHouse。

背景信息

为了强化实时数仓的能力,云数据库ClickHouse推出了MaterializeMySQL数据库引擎,云数据库ClickHouse服务作为MySQL副本,读取Binlog并执行DDL和DML请求,实现了基于MySQL Binlog机制的业务数据库实时同步功能。

功能优势

云数据库ClickHouse自主研发了如下功能。

  • 通过配置同步表名单或排除表名单,实现部分数据库表的同步。

  • 修改同步配置项。

  • 跳过同步错误。

  • 跳过不支持的表结构。

  • 控制查询物化库的表时是否添加FINAL修饰符。

  • 控制云数据库ClickHouse表结构ORDER BY的生成规则。

  • 控制物化库是否按照分布式模式将MySQL数据同步至云数据库ClickHouse

  • 控制全量同步时每秒最多拉取的行数。

注意事项

  • 云数据库ClickHouse的社区兼容版集群仅20.8及以上版本支持MaterializeMySQL引擎。

  • 使用MaterializeMySQL引擎查询物化库的表时需要添加FINAL修饰符以确保查询的结果与RDS MySQL一致。

  • MaterializeMySQL引擎无法同步以下两种类型的表:无主键表和create as select语法创建的表。

  • 数据源RDS MySQL集群和目标ClickHouse集群必须属于同一个VPC网络。

  • 数据源RDS MySQL集群需要保证参数log_bingtid_mode为开启状态时SELECT查询才能生效。

    说明

    RDS MySQL集群log_bingtid_mode参数默认为开启状态。如何查看集群参数,具体操作请参见查看实例参数

  • 数据源RDS MySQL集群的内核版本是5.6及以上版本,且必须开启全局事务标识(GTID)。

  • 数据源RDS MySQL的DDL语句如果不是标准的MySQL语法,有可能导致无法同步。

  • 同步RDS MySQL数据时需要将ClickHouse集群所使用的交换机网段添加到RDS MySQL的白名单中。更多信息,请参见设置IP白名单

  • 配置MaterializeMySQL引擎所使用的MySQL账号必须具备MySQL库的RELOAD、REPLICATION SLAVE、REPLICATION CLIENT以及SELECT PRIVILEGE权限。

    说明

    您可以通过GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'your-user-name';命令获取MySQL库的相关权限。

  • 不能随意修改云数据库ClickHouse中的表结构和数据。

  • 云数据库ClickHouseDateTime类型数据的时间范围为[1970-01-01 00:00:00,2106-02-07 06:28:15],如果RDS MySQL中的时间超出当前范围,会导致同步到云数据库ClickHouse的时间值不正确。

使用MaterializeMySQL引擎

为了将MySQL服务器中的表映射到云数据库ClickHouse中,您需要创建MaterializeMySQL引擎的数据库。

语法

您可以在创建数据库时配置数据库引擎类型为MaterializeMySQL并配置相关参数,语法如下。

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

where SETTINGS are:
[ { include_tables | exclude_tables } ]
[ skip_error_count ]
[ skip_unsupported_tables ]
[ order_by_only_primary_key ]
[ shard_model ]
[ rate_limiter_row_count_per_second ]
[ allow_query_with_final_auto ]

引擎参数说明

参数

说明

host:port

MySQL数据库的URL和端口号。

说明

如果数据源为PolarDB时,只支持使用主库的连接地址(也就是主地址)连接集群。

database

MySQL数据库名称。

user

MySQL数据库账号。

password

MySQL数据库账号的密码。

引擎配置项说明

配置项

类型

说明

include_tables

String

配置同步表名单。配置同步表名单后,则只有表名单内的表会被同步。多个表之间,用","分隔。

模糊匹配规则:

  • *:替换除/和空字符串以外的任意数量的任何字符。

  • ?:替换任意单个字符。

  • {N..M} :替换N到M范围内的任何数字,包括两个边界。

exclude_tables

String

配置排除表名单。排除表名单内的表不进行同步。多个表之间,用","分隔。

模糊匹配规则:

  • *:替换除/和空字符串以外的任意数量的任何字符。

  • ?:替换任意单个字符。

  • {N..M} :替换N到M范围内的任何数字,包括两个边界。

说明

exclude_tables和include_tables两个配置项不能同时使用。

skip_error_count

Int

是否跳过同步错误。取值范围如下:

  • -9223372036854775808~-1:跳过所有错误。

  • 0:默认值,不跳过任何一个错误。

  • 1~9223372036854775807:跳过对应数字的错误。

skip_unsupported_tables

Boolean

是否跳过MaterializeMySQL引擎目前无法同步的MySQL表。取值范围如下:

  • 0:不跳过不支持的表结构,即如果遇到不能同步的表结构,同步会失败。

  • 1:默认值,跳过不支持的表结构,即如果遇到不能同步的表结构,跳过并继续进行同步。

说明

该参数优先级高于skip_error_count参数,如果设置了skip_unsupported_tables,skip_error_count将不再生效。

order_by_only_primary_key

Boolean

云数据库ClickHouse表结构ORDER BY的生成规则。取值范围如下:

  • 0:将MySQL主键和索引都转换为ORDER BY元组。

  • 1:默认值,只将MySQL主键转换为ORDER BY元组。

shard_model

Boolean

物化库是否按照分布式模式将MySQL数据同步至云数据库ClickHouse。取值范围如下:

  • 0:默认值,一个MySQL表对应一个云数据库ClickHouse本地表。适用于只有一个节点的云数据库ClickHouse集群。

    这种模式下,如果云数据库ClickHouse集群有多个节点,那么MySQL服务器会同步数据到每个节点,导致MySQL服务器压力较大。

  • 1:分布式模式,一个MySQL表对应一个云数据库ClickHouse本地表和一个分布式表,其中分布式表和MySQL表同名。查询同步数据时,需要查询分布式表。适用于有多个节点的云数据库ClickHouse集群。

rate_limiter_row_count_per_second

Int

全量同步时每秒最多拉取的行数。取值范围如下:

  • 0:默认值,不限制拉取的行数。

  • 1~9223372036854775807:每秒最多拉取对应数字的行数。例如取值为1,最多拉取1行。

allow_query_with_final_auto

Boolean

查询物化库的表时是否主动添加FINAL修饰符。取值范围如下:

  • 0:不添加FINAL修饰符。

  • 1:默认值,即查询时默认添加FINAL修饰符。

    该参数可以通过set allow_query_with_final_auto = 0语法进行session级别设置。

说明
  • 22.8版本的云数据库ClickHouse集群不支持配置该参数。

  • FINAL默认单线程,因此查询速度慢。

  • FINAL修饰符只有在指定列查询时生效,例如select verison fromselect * from不会生效。

说明

MaterializeMySQL引擎所有的配置项都可以进行修改,修改后立即生效。语法如下。

ALTER DATABASE <database> MODIFY SETTING

示例

CREATE DATABASE IF NOT EXISTS db_name ON CLUSTER cluster
ENGINE = MaterializeMySQL('rm-bp1h40w639t19****.mysql.rds.aliyuncs.com:3306', 'database', 'user', 'password') 
SETTINGS
include_tables ='a,b,c';

使用细则

虚拟字段

使用MaterializeMySQL在云数据库ClickHouse集群上新建ReplacingMergeTree引擎表时,会默认在表中增加两个虚拟字段。

字段

类型

说明

_version

UInt64

事务计数器,记录数据版本信息。

_sign

Int8

删除标记,标记该行是否删除。取值范围如下:

  • 1:该行未删除。

  • -1:该行已删除。

    说明

    带有_sign=-1的行不会从表中物理删除。

DDL语句转换

MaterializeMySQL不支持直接插入、删除和更新查询,MySQL DDL语句将被转换成相应的ClickHouse DDL语句:

  • MySQL INSERT查询被转换为INSERT with _sign=1

  • MySQL DELETE查询被转换为INSERT with _sign=-1

  • MySQL UPDATE查询被转换成INSERT with _sign=1。当修改主键的时候会另外增加一条INSERT with _sign=-1

说明
  • 如果ClickHouse不能解析某些DDL语句,该语句将被忽略。

  • MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。

SELECT查询

  • 由于MaterializeMySQL引擎的特殊性,查询时需要指定_sign=1和final关键字。例如:select * from t1 final where _sign=1;

  • 21.8及以下版本的云数据库ClickHouse集群查询时默认遵循以下原则:

    • 如果在SELECT查询中没有指定_version,则使用FINAL修饰符,返回_version的最大值对应的数据,即最新版本的数据。

    • 如果在SELECT查询中没有指定_sign,则默认使用WHERE _sign=1,即返回未删除状态(_sign=1)的数据。

索引转换

  • ClickHouse数据库表会自动将MySQL主键和索引子句转换为ORDER BY元组。

  • ClickHouse只有一个物理顺序,由ORDER BY子句决定。如果需要创建新的物理顺序,请使用物化视图。

字段类型对照表

下表介绍MySQL与云数据库ClickHouse的字段类型对应关系。

MySQL

ClickHouse

TINY

Int8

SHORT

Int16

INT24

Int32

LONG

UInt32

LONG

UInt64

FLOAT

Float32

DOUBLE

Float64

DECIMAL,NEWDECIMAL

Decimal

DATE,NEWDATE

Date

DATETIME,TIMESTAMP

DateTime

DATETIME2,TIMESTAMP2

DateTime64

STRING

String

VARCHAR,VAR_STRING

String

BLOB

String

BIT

UInt64

SET

UInt64

ENUM

Enum16

YEAR

String

TIME

String

GEOMETRY

String

JSON

不支持

同步RDS MySQL数据

  1. 创建数据库和表

    1. 在RDS MySQL中创建数据库和表。

      CREATE DATABASE cktest;
      CREATE TABLE cktest.test01 (a INT PRIMARY KEY, b INT);
    2. 云数据库ClickHouse中创建同步数据库。

      CREATE DATABASE mysql ENGINE = MaterializeMySQL('rm-bp1h40w639t19****.mysql.rds.aliyuncs.com:3306', 'cktest', 'user', 'password');
    3. 云数据库ClickHouse中查询同步库中的表。

      SHOW TABLES FROM mysql;

      结果如下。

      +--------+
      | name   | 
      +--------+
      | test01 | 
      +--------+
  2. 插入数据并查询

    1. 在RDS MySQL中插入数据。

      INSERT INTO cktest.test01 VALUES (1, 10), (2, 20);
    2. 云数据库ClickHouse中查询。

      SELECT * FROM mysql.test01;

      结果如下。

      +----+----+
      | a  | b  |   
      +----+----+
      | 1  | 10 |
      +----+----+
      | 2  | 20 |
      +----+----+
  3. 删除数据,添加列并查询

    1. 在RDS MySQL中删除数据,添加列并更新数据进行查询。

      DELETE FROM cktest.test01 WHERE a=1;
      ALTER TABLE cktest.test01 ADD COLUMN c VARCHAR(16);
      UPDATE cktest.test01 SET c='ok!', b=202;
      SELECT * FROM test01;

      结果如下。

      +---+-----+-----+ 
      | a | b   | c   |
      +---+-----+-----+ 
      | 2 | 202 | ok! |
      +---+-----+-----+
    2. 云数据库ClickHouse中查询。

      SELECT * FROM mysql.test01;

      结果如下。

      +----+-----+-----+ 
      | a  | b   | c   |
      +----+-----+-----+ 
      | 2  | 202 | ok! |
      +----+-----+-----+

查询同步详情

您可以通过查询系统表system.materialize_mysql,获取使用MaterializeMySQL引擎同步MySQL数据的情况。查询语句如下。

  • 集群中只有一个节点。

    select * from system.materialize_mysql;
  • 集群中有多个节点。

    select * from cluster(default, system, materialize_mysql);

查询结果如下。

+----------+----------------+-------------+------------------+--------------------+
| database |     status     | error_count | lasted_error_msg | sync_failed_tables |
+----------+----------------+-------------+------------------+--------------------+
|  mysql   | increment_sync |     0       |                  |                    |
+----------+----------------+-------------+------------------+--------------------+

查询结果字段说明如下。

字段名

说明

database

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

status

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

  • preparing_sync:同步准备阶段。

  • full_sync:全量同步阶段。

  • increment_sync:增量同步阶段。

  • stop_sync:同步停止。

error_count

同步过程中出错的次数。

lasted_error_msg

最近一次同步错误的详情。

sync_failed_tables

同步失败的表。多个表之间,以","分隔。

常见问题

  • 问题1:使用MaterializeMySQL引擎同步MySQL数据时,为什么出现如下报错:The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires

    常见原因:MaterializeMySQL引擎停止同步的时间太久,导致MySQL Binlog日志过期被清理掉。

    解决方案:删除报错的数据库,重新在云数据库ClickHouse中创建同步的数据库。

  • 问题2:使用MaterializeMySQL引擎同步MySQL数据时,为什么出现表停止同步?为什么系统表system.materialize_mysql中sync_failed_tables字段不为空?

    常见原因:同步过程中使用了云数据库ClickHouse不支持的MySQL DDL语句。

    解决方案:重新同步MySQL数据,具体步骤如下。

    1. 删除停止同步的表。

      drop table <table_name> on cluster default;
      说明

      table_name为停止同步的表名。如果停止同步的表有分布式表,那么本地表和分布式表都需要删除。

    2. 重启同步进程。

      alter database <database_name> on cluster default MODIFY SETTING skip_unsupported_tables = 1;
      说明

      <database_name>云数据库ClickHouse中同步的数据库。

  • 问题3:除了binlog日志过期以外,MySQL的磁盘达到阈值也会清理binlog导致物化库无法正常同步。如何查询和设置MySQL(自建MySQL、RDS MySQL或PolarDB MySQL )的binlog日志保留时间?

    解决方案:

    • MySQL 8.0以下版本可以通过以下语句查看和设置binlog日志保留时间:

      showvariableslike'expire_logs_days';
      setglobalexpire_logs_days=7;
    • MySQL 8.0以上版本通过以下语句查看和设置binlog日志保留时间:

      showvariableslike'%binlog_expire_logs_seconds%';
      setglobalbinlog_expire_logs_seconds=604800;