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

背景信息

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

功能优势

云数据库ClickHouse自主研发了如下功能。
  • 通过配置同步表名单或排除表名单,实现部分数据库表的同步。
  • 修改同步配置项。
  • 跳过同步错误。
  • 跳过不支持的表结构。
  • 控制云数据库ClickHouse表结构ORDER BY的生成规则。
  • 开启Binlog预留功能,避免Binlog清理导致的无法正常同步数据的问题,从而提升MaterializeMySQL引擎的稳定性。
  • 控制物化库是否按照分布式模式将MySQL数据同步至云数据库ClickHouse
  • 控制全量同步时每秒最多拉取的行数。

注意事项

  • 云数据库ClickHouse的社区兼容版集群仅20.8及以上版本支持MaterializeMySQL引擎。
  • 使用MaterializeMySQL引擎查询物化库的表时需要添加FINAL修饰符以确保查询的结果与RDS MySQL一致。
  • MaterializeMySQL引擎无法同步以下两种类型的表:无主键表和create as select语法创建的表。
  • 数据源RDS MySQL集群和目标ClickHouse集群必须属于同一个VPC网络。
  • 数据源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 ]
[ enable_binlog_reserved ]
[ shard_model ]
[ rate_limiter_row_count_per_second ]
引擎参数说明
参数 说明
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元组。
enable_binlog_reserved Int 是否开启Binlog预留功能。取值范围如下:
  • 0:关闭Binlog预留功能。
  • 1:默认值,开启Binlog预留功能。
说明 如果开启Binlog预留功能,可能会延迟MySQL数据同步至云数据库ClickHouse
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行。
说明
  • 如果您配置如上参数时提示参数不支持一类错误,请提交工单升级版本至最新版本。
  • 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 TypeInt8 删除标记,标记该行是否删除。取值范围如下:
  • 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查询

  • 如果在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中同步的数据库。