MaterializedMySQL是一个用于从MySQL数据库中获取数据的表引擎。它的工作原理是:

• MaterializedMySQL会定期从MySQL拉取数据,同步到云数据库ClickHouse本地。
• 数据同步后,会存储在本地云数据库ClickHouse表中,表引擎设置为合适的引擎(如MergeTree)。
• 查询时,云数据库ClickHouse不会访问MySQL,而是直接从本地已同步的表中读取数据。
• 同步的时间间隔可以通过 mysql_materialized_tables_ttl参数设置,默认10分钟同步一次。

MaterializedMySQL的主要优点是:

• 减少了跨网络访问MySQL的开销,查询性能更好。
• 可以对同步的数据进行云数据库ClickHouse的查询优化,实现更快的分析。
• 支持增量同步,只同步新增或修改的数据,而不是整表同步。
• 支持只同步指定的列,以减少不需要的数据量。
• 本地表引擎可自定义,使存储方式更灵活。

创建数据库

语法

创建使用MaterializedMySQL引擎的表的语法:

CREATE TABLE [IF NOT EXISTS] table_name 
ENGINE = MaterializedMySQL(`mysql_source_host`, `mysql_source_database`, `mysql_source_table`, `clickhouse_sink_database`, `clickhouse_sink_table`, `clickhouse_engine`)

• mysql_source_host:MySQL的源主机地址
• mysql_source_database:MySQL源数据库名
• mysql_source_table:MySQL源表名
• clickhouse_sink_database:云数据库ClickHouse目标数据库名
• clickhouse_sink_table:云数据库ClickHouse目标表名
• clickhouse_engine:云数据库ClickHouse目标表的引擎

示例

-- 从MySQL sync_test库的data表同步到ClickHouse的sync数据库sync_data表

CREATE TABLE sync.sync_data
ENGINE = MaterializedMySQL('localhost', 'sync_test', 'data', 'sync', 'sync_data', 'MergeTree() order by id');

这个表会定期从MySQL localhost上的sync_test库的data表同步数据到云数据库ClickHouse的sync库的sync_data表,目标表的引擎是MergeTree。

MaterializedMySQL提供了从MySQL增量同步数据的便捷途径,可以让云数据库ClickHouse对MySQL数据进行更优化的存储和查询。

MySQL服务器端配置

为了 MaterializedMySQL的正确工作，有一些必须设置的 MySQL端配置设置:

• default_authentication_plugin = mysql_native_password，因为 MaterializedMySQL 只能授权使用该方法。
• gtid_mode = on，因为基于GTID的日志记录是提供正确的 MaterializedMySQL复制的强制要求。

                
说明
                
当打开 gtid_mode 时需要指定 enforce_gtid_consistency = on。
              

支持的数据类型

MySQL中的Time 类型，会被云数据库ClickHouse转换成微秒来存储。如果MySQL表包含此类类型的列，云数据库ClickHouse抛出异常"Unhandled data type"并停止复制。

规范和推荐用法

兼容性限制

除了数据类型的限制之外，还有一些限制与 MySQL数据库相比有所不同，这应该在复制之前解决:

• MySQL 中的每个表都应该包含 PRIMARY KEY。
• 对于表的复制，那些包含 ENUM 字段值超出范围的行(在 ENUM 签名中指定)将不起作用。

DDL Queries

MySQL DDL 语句会被转换成对应的云数据库ClickHouse DDL 语句，比如： (ALTER, CREATE, DROP, RENAME). 如果云数据库ClickHouse 无法解析某些语句DDL 操作，则会跳过。

数据复制

MaterializedMySQL不支持直接的 INSERT， DELETE 和 UPDATE 查询。然而，它们在数据复制方面得到了支持:

• MySQL INSERT查询被转换为 _sign=1的INSERT查询。
• MySQL DELETE查询被转换为 INSERT，并且 _sign=-1。
• 如果主键被修改了，MySQL的 UPDATE 查询将被转换为 INSERT 带 _sign=1 和INSERT 带有_sign=-1;如果主键没有被修改，则转换为 INSERT和 _sign=1。

MaterializedMySQL 数据表查询

SELECT 查询从 MaterializedMySQL表有一些细节:

• 如果在SELECT查询中没有指定 _version，则FINAL修饰符被使用，所以只有带有 MAX(_version)的行会返回每个主键值。
• 如果在SELECT查询中没有指定 _sign，则默认使用 WHERE _sign=1。所以被删除的行不是 包含在结果集中。
• 结果包括列注释，以防MySQL数据库表中存在这些列注释。

索引转换

在云数据库ClickHouse表中，MySQL的 PRIMARY KEY 和 INDEX 子句被转换为 ORDER BY 元组。

云数据库ClickHouse只有一个物理排序，由 order by 条件决定。要创建一个新的物理排序，请使用materialized views。

                
说明
                
_sign=-1 的行不会被物理地从表中删除。
级联 UPDATE/DELETE 查询不支持 MaterializedMySQL 引擎。
MaterializedMySQL 受 optimize_on_insert 设置的影响。当MySQL服务器中的一个表发生变化时，数据会合并到 MaterializedMySQL 数据库中相应的表中。
              

使用示例

MySQL 查询语句:

mysql>CREATE DATABASE db;
mysql>CREATE TABLE db.test (a INTPRIMARYKEY, 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 ADDCOLUMN c VARCHAR(16);
mysql>UPDATE db.test SET c='Wow!', b=222;
mysql>SELECT * FROM test;

┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

云数据库ClickHouse中的数据库，与MySQL服务器交换数据:

创建的数据库和表:

CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306','db','user','***');
SHOW TABLES FROM mysql;

┌─name─┐
│ test │
└──────┘

数据插入之后：

SELECT * FROM mysql.test;

┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘

删除数据后，添加列并更新:

SELECT * FROM mysql.test;

┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

完整同步示例

CREATE TABLE mysql_sync.full_table 
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'products_mv', 'MergeTree() order by id');  

这会完整同步MySQL server，其ip地址为 A.A.A.A 上的sales库中products表到云数据库ClickHouse中的sync库的products_mv表。

增量同步示例

CREATE TABLE mysql_sync.updated_data
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_updates', 'MergeTree() order by id', 1);

该表仅同步products表中新增或修改过的数据。第6个参数 1表示启用增量同步。

只同步部分列示例

CREATE TABLE mysql_sync.product_names
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_names', 'MergeTree() order by id', 0, ['name', 'description']);

这里只同步products表的name和description两列。第7个参数定义同步的列。

设置同步周期示例

CREATE TABLE mysql_sync.orders
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'store', 'orders', 'sync', 'orders_mv', 'MergeTree() order by id', 0, [], 3600);

这里通过第8个参数将同步周期设置为3600秒,即每小时同步一次。