[大数据]Clickhouse基于物化引擎(MaterializedMySQL)来同步mysql

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

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

语法:

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

1,mysql开启binlog和GTID

修改配置文件/etc/mysql/my.cnf

[mysqld]
# 指定binlog日志存储位置
log_bin=/var/lib/mysql/mysql-bin
# 这里一定是row格式
binlog_format=ROW

# 开启GTID模式
gtid_mode=ON
# 设置主从强一致性
enforce_gtid_consistency=1
# 记录日志
log_slave_updates=1

2,创建复制管道

set global on cluster default max_partitions_per_insert_block = 10000;

#开启物化引擎
SET allow_experimental_database_materialized_mysql=1;

CREATE DATABASE mt ENGINE = MaterializeMySQL('localhost:3306', 'mt', 'root', '123456')

优点: 通过监听mysql的binlog文件,实现增量更新,提升了效率

数据限制:

1,同步mysql数据之前,mysql的每张表应该都有PRIMARY KEY(如果没有主键,同步会报错)

2,MaterializedMySQL是库级别的引擎,同步的时候会以全库中表数据同步

3,mysql数据同步到clickhouse后会发生索引转换:在ClickHouse表中,MySQL的 PRIMARY KEY 和 INDEX 子句被转换为 ORDER BY 元组

4,mysql在转换为clickhouse表的时候,每张表都会新增两个字段:_sign(1:写入,-1:删除), _version

5,在clickhouse中同步新增时,并没有实现物理意义上的删除,只通过_sign标志字段来实现数据过滤

6,在mysql转化clickhouse时,默认使用的ReplacingMergeTree引擎,保证没有重复数据出现

查看状态:

1、查询同步的数据库

show databses;
use mt;
show tables;

2、查看建表语句

show create table sku_info;

3、查询同步的数据

select *,_sign,_version from sku_info;

总结

• 通过上面的测试我们发现clickhouse的删除动作也是实时同步的，原因在于我们创建的MaterializeMySQL engine会默认为每一张表生成ReplacingMergeTree engine，当clickhouse遇到删除的binlog操作时，会将这条数据的_sign字段设为-1；
  目前ReplacingMergeTree还只是标记性删除，并非物理上的实际删除，索引随着删除日志的增多，查询过滤会有一定的负担。

• MaterializeMySQL DataBase中的ReplacingMergeTree Engine表查询不再需要额外添加final修饰符了：

select * from scene
##等同于
select * from scene final

• 需要注意的是20.8版本目前还不是稳定版，如果mysql中没有设置主键字段时，会在创建MaterializeMySQL数据库时报错:

DB::Exception: The db.scene cannot be materialized, because there is no primary keys.

主键字段和索引字段不允许为NULL

Rewritten MySQL DDL Query ... wasn't finished successfully: Code: 44, e.displayText() = DB::Exception: Sorting key cannot contain nullable columns

不过该ISSUES目前已被重视，20.7版本在create table 时解决了这个问题，可以通过设置allow_nullable_key=1来解决，但因为MaterializeMySQL是自动创建的数据表，所以该问题还是存在的，相信不久的版本在创建MaterializeMySQL DataBase时 也会解决这个问题

CREATE TABLE nullable_key 
      (k Nullable(int), v int) ENGINE MergeTree ORDER BY k SETTINGS allow_nullable_key = 1;

• clickhouse单线程写入能力可以达到每秒几十万，在一般业务体系下增量更新的模式是完全没有问题的。