云数据库 ClickHouse：MaterializeMySQL引擎

背景信息

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

功能优势

注意事项

• 云数据库ClickHouse的社区兼容版集群仅20.8及以上版本支持MaterializeMySQL引擎。
• 使用MaterializeMySQL引擎查询物化库的表时需要添加FINAL修饰符以确保查询的结果与RDS MySQL一致。
• MaterializeMySQL引擎无法同步以下两种类型的表：无主键表和create as select语法创建的表。
• 数据源RDS MySQL集群和目标ClickHouse集群必须属于同一个VPC网络。
• 数据源RDS MySQL集群需要保证参数log_bin和gtid_mode为开启状态时SELECT查询才能生效。
  说明 RDS MySQL集群log_bin和gtid_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中的表结构和数据。
• 云数据库ClickHouse的DateTime类型数据的时间范围为[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 ]

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

使用细则

虚拟字段

DDL语句转换

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的字段类型对应关系。

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

查询同步详情

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

常见问题

• 问题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日志保留时间：

    show variables like 'expire_logs_days';
    set global expire_logs_days=7;

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

    show variables like '%binlog_expire_logs_seconds%';
    set global binlog_expire_logs_seconds=604800;