本文介绍如何使用MaterializeMySQL引擎将MySQL(自建MySQL或RDS MySQL)数据同步到云数据库ClickHouse

背景信息

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

功能优势

云数据库ClickHouse自主研发了如下功能。
  • 通过配置同步表名单或排除表名单,实现部分数据库表的同步。
  • 修改同步配置项。
  • 跳过同步错误。
  • 跳过不支持的表结构。
  • 控制查询物化库的表时是否添加FINAL修饰符。
  • 控制云数据库ClickHouse表结构ORDER BY的生成规则。

注意事项

  • MaterializeMySQL引擎无法同步以下两种类型的表:无主键表和create as select语法创建的表。
  • 数据源RDS MySQL集群和目标ClickHouse集群必须属于同一个VPC网络。
  • 数据源RDS MySQL集群的内核版本是5.6及以上版本,且必须开启全局事务标识(GTID)。
  • 同步RDS MySQL数据时需要将ClickHouse集群的IP地址添加到RDS MySQL的白名单中。更多信息,请参见设置IP白名单
    说明 您可以通过 select * from system.clusters;命令查看ClickHouse集群的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中的MaterializeMySQL库及表进行手动操作。

使用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 ]
[ query_with_final] 
[ order_by_only_primary_key ]
引擎参数说明
参数 说明
host:port MySQL数据库的URL和端口号。
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 是否跳过MaterializedMySQL引擎目前无法同步的MySQL表。取值范围如下:
  • 0:默认值,不跳过不支持的表结构,即如果遇到不能同步的表结构,同步会失败。
  • 1:跳过不支持的表结构,即如果遇到不能同步的表结构,跳过并继续进行同步。
说明 该参数优先级高于skip_error_count参数,如果设置了skip_unsupported_tables,skip_error_count将不再生效。
query_with_final Boolean 查询物化库的表时是否添加FINAL修饰符。取值范围如下:
  • 0:不添加FINAL修饰符。
  • 1:默认值,按照如下规则添加FINAL修饰符:
    如果在SELECT查询中没有指定_version,则默认添加FINAL修饰符,返回_version的最大值对应的数据,即最新版本的数据。
    说明 如果在SELECT查询中指定了_version,不添加FINAL修饰符。
order_by_only_primary_key Boolean 云数据库ClickHouse表结构ORDER BY的生成规则。取值范围如下:
  • 0:默认值,将MySQL主键和索引都转换为ORDER BY元组。
  • 1:只将MySQL主键转换为ORDER BY元组。
说明
  • 如果您配置如上参数时提示参数不支持一类错误,请提交工单升级版本至最新版本。
  • MaterializeMySQL引擎所有的配置项都可以进行修改,修改后立即生效。语法如下。
    ALTER DATABASE <database> MODIFY [SETTINGS]

示例

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',
skip_error_count =0,
skip_unsupported_tables =1,
query_with_final =1,
order_by_only_primary_key =1;

使用细则

默认隐藏字段

使用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=1INSERT 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
JSON String
YEAR String
TIME String
GEOMETRY String

同步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! |
      +----+-----+-----+