使用ClickHouse的MySQL物化引擎实现全/增量同步

Last updated on 2022年4月10日

数据项目结束，终于有时间再补一补之前的环境配置文章了～ 😄

官方文档：https://clickhouse.com/docs/zh/engines/database-engines/materialize-mysql/

这是一个实验性的特性，不应该在生产中使用。
创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。
ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询。

一、在MySQL上操作

1.创建复制用户，或使用有slaver权限的用户

mysql> create user clickhouse@'%' identified WITH mysql_native_password by 'rpl_user';
mysql> grant ALL PRIVILEGES on . to clickhouse@'%';

2.配置 my.cnf
MySQL Server 在此扮演主库角色，因此需要按主库的要求来做配置
部署Master-MySQL，确保以下参数正确：

[mysqld]
binlog_format = ROW # binglog日志格式为row
gtid_mode = ON # 基于GTID(全局事务ID)的指定方式，开启GTID模式：解决位点同步时MySQL主从切换问题（BinLog reset导致位点失效）
log_bin_use_v1_row_events = OFF # 该参数代表binlog的版本信息，从5.6默认使用version2，所以该参数默认OFF
enforce_gtid_consistency = ON # 强一致性校验
default_authentication_plugin = mysql_native_password # MySQL的连接加密方式

如果出现报错信息1：the MaterializedMySQL engine requires log_bin_use_v1_row_events=’OFF’，说明你没有按上述配置正确。

二、在ClickHouse 上操作（version：v22.3-lts）

开启物化引擎体验特性

当前连接中开启：

SET allow_experimental_database_materialized_mysql = 1;

配置里开启（推荐）：
users.xml -> profiles -> default 标签中添加

<allow_experimental_database_materialized_mysql>1</allow_experimental_database_materialized_mysql>

参考issue：https://github.com/ClickHouse/ClickHouse/issues/25211

三、开始全量同步

开启后，会在全量同步结束后自动开启增量同步（这里以test数据库,clickhouse用户为例）。

ROP DATABASE test;
CREATE DATABASE test ENGINE = MaterializedMySQL('127.0.0.1:3306', 'mysql_test', 'clickhouse', '123456')
SETTINGS allows_query_when_mysql_lost=true,max_wait_time_when_mysql_unavailable=10000;

可能会遇到的报错信息：

DB::Exception: Command [ERRFMT] is not implemented. (NOT_IMPLEMENTED)

遇到错误优先查看日志，一般可看到直接信息。检查下面的限制，不满足这两个限制一般会导致同步一部分表后停止。

一些限制：
1.MySQL每个表必须有主键；
2.表字段ENUM类型必须有范围内的值。
不满足以上2个条件将导致迁移失败。