Skip to main content

[experimental] MaterializedMySQL

这是一个实验性的特性,不应该在生产中使用。

创建ClickHouse数据库,包含MySQL中所有的表,以及这些表中的所有数据。

ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。

这个功能是实验性的。

创建数据库

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

引擎参数

  • host:port — MySQL服务地址
  • database — MySQL数据库名称
  • user — MySQL用户名
  • password — MySQL用户密码

引擎配置

  • max_rows_in_buffer — 允许数据缓存到内存中的最大行数(对于单个表和无法查询的缓存数据)。当超过行数时,数据将被物化。默认值: 65505
  • max_bytes_in_buffer — 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。当超过行数时,数据将被物化。默认值: 1048576.
  • max_rows_in_buffers — 允许数据缓存到内存中的最大行数(对于数据库和无法查询的缓存数据)。当超过行数时,数据将被物化。默认值: 65505.
  • max_bytes_in_buffers — 允许在内存中缓存数据的最大字节数(对于数据库和无法查询的缓存数据)。当超过行数时,数据将被物化。默认值: 1048576.
  • max_flush_data_time — 允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。当超过这个时间时,数据将被物化。默认值: 1000.
  • max_wait_time_when_mysql_unavailable — 当MySQL不可用时重试间隔(毫秒)。负值禁止重试。默认值: 1000.
  • allows_query_when_mysql_lost — 当mysql丢失时,允许查询物化表。默认值: 0 (false).
CREATE DATABASE mysql ENGINE = MaterializeMySQL('localhost:3306', 'db', 'user', '***') 
SETTINGS
allows_query_when_mysql_lost=true,
max_wait_time_when_mysql_unavailable=10000;

MySQL服务器端配置

为了MaterializeMySQL正确的工作,有一些强制性的MySQL侧配置设置应该设置:

  • default_authentication_plugin = mysql_native_password,因为MaterializeMySQL只能使用此方法授权。
  • gtid_mode = on,因为要提供正确的MaterializeMySQL复制,基于GTID的日志记录是必须的。注意,在打开这个模式On时,你还应该指定enforce_gtid_consistency = on

虚拟列

当使用MaterializeMySQL数据库引擎时,ReplacingMergeTree表与虚拟的_sign_version列一起使用。

  • _version — 同步版本。 类型UInt64.
  • _sign — 删除标记。类型 Int8. Possible values:
    • 1 — 行不会删除,
    • -1 — 行被删除。

支持的数据类型

MySQLClickHouse
TINYInt8
SHORTInt16
INT24Int32
LONGUInt32
LONGLONGUInt64
FLOATFloat32
DOUBLEFloat64
DECIMAL, NEWDECIMALDecimal
DATE, NEWDATEDate
DATETIME, TIMESTAMPDateTime
DATETIME2, TIMESTAMP2DateTime64
ENUMEnum
STRINGString
VARCHAR, VAR_STRINGString
BLOBString
BINARYFixedString

不支持其他类型。如果MySQL表包含此类类型的列,ClickHouse抛出异常"Unhandled data type"并停止复制。

Nullable已经支持

使用方式

兼容性限制

除了数据类型的限制外,与MySQL数据库相比,还存在一些限制,在实现复制之前应先解决这些限制:

  • MySQL中的每个表都应该包含PRIMARY KEY

  • 对于包含ENUM字段值超出范围(在ENUM签名中指定)的行的表,复制将不起作用。

DDL查询

MySQL DDL查询转换为相应的ClickHouse DDL查询(ALTER, CREATE, DROP, RENAME)。如果ClickHouse无法解析某个DDL查询,则该查询将被忽略。

Data Replication

MaterializeMySQL不支持直接INSERT, DELETEUPDATE查询. 但是,它们是在数据复制方面支持的:

  • MySQL的INSERT查询转换为INSERT并携带_sign=1.

  • MySQL的DELETE查询转换为INSERT并携带_sign=-1.

  • MySQL的UPDATE查询转换为INSERT并携带_sign=-1, INSERT_sign=1.

查询MaterializeMySQL表

SELECT查询MaterializeMySQL表有一些细节:

  • 如果_versionSELECT中没有指定,则使用FINAL修饰符。所以只有带有MAX(_version)的行才会被选中。

  • 如果_signSELECT中没有指定,则默认使用WHERE _sign=1。因此,删除的行不会包含在结果集中。

  • 结果包括列中的列注释,因为它们存在于SQL数据库表中。

Index Conversion

MySQL的PRIMARY KEYINDEX子句在ClickHouse表中转换为ORDER BY元组。

ClickHouse只有一个物理顺序,由ORDER BY子句决定。要创建一个新的物理顺序,使用materialized views

Notes

  • 带有_sign=-1的行不会从表中物理删除。
  • MaterializeMySQL引擎不支持级联UPDATE/DELETE查询。
  • 复制很容易被破坏。
  • 禁止对数据库和表进行手工操作。
  • MaterializeMySQLoptimize_on_insert设置的影响。当MySQL服务器中的表发生变化时,数据会合并到MaterializeMySQL数据库中相应的表中。

使用示例

MySQL操作:

mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, 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 ADD COLUMN 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 = MaterializeMySQL('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! │
└───┴─────┴──────┘

来源文章