mysql数据库数据增量同步到导入ClickHouse

ClickHouse 可以挂载为 MySQL 的一个从库 ，先全量再增量的实时同步 MySQL 数据，这个功能可以说是今年最亮眼、最刚需的功能，基于它我们可以轻松的打造一套企业级解决方案，让 OLTP 和 OLAP 的融合从此不再头疼。

目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。

Mysql设置

设置/etc/my.cnf

[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
socket=/tmp/mysql.sock
character-set-server=UTF8MB4


#开启日志
log_bin = /usr/local/mysql-8.0.20/data/mysql‐bin
#设置服务id
server_id = 1
#不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了
binlog_format = ROW

gtid-mode=on
enforce-gtid-consistency=1    # 设置为主从强一致性
log-slave-updates=1   # 记录日志

#配置插件为了clickhouse可以建表
default_authentication_plugin=mysql_native_password

重启mysql

service mysql restart

创建数据库和表，并写入数据:

mysql> create database ckdb;
mysql> use ckdb;
mysql> create table t1(a int not null primary key, b int);
mysql> insert into t1 values(1,1),(2,2);
mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

clickhouse设置

目前以 database 为单位进行复制，不同的 database 可以来自不同的 MySQL master，这样就可以实现多个 MySQL 源数据同步到一个 ClickHouse 做 OLAP 分析功能。

首先开启体验开关:

clickhouse :) SET allow_experimental_database_materialize_mysql=1;

创建一个复制通道：

clickhouse :) CREATE DATABASE ckdb ENGINE = MaterializeMySQL('172.17.0.2:3306', 'ckdb', 'root', '123');
clickhouse :) use ckdb;
clickhouse :) show tables;
┌─name─┐
│ t1   │
└──────┘
clickhouse :) select * from t1;
┌─a─┬─b─┐
│ 1 │ 1 │
└───┴───┘
┌─a─┬─b─┐
│ 2 │ 2 │
└───┴───┘

2 rows in set. Elapsed: 0.017 sec.

看下 ClickHouse 的同步位点：
cat ckdatas/metadata/ckdb/.metadata

Version:	1
Binlog File:	mysql-bin.000001
Binlog Position:	913
Data Version:	0

Delete

首先在 MySQL Master 上执行一个删除操作：

mysql> delete from t1 where a=1;
Query OK, 1 row affected (0.01 sec)

然后在 ClickHouse Slave 侧查看记录：

clickhouse :) select * from t1;

SELECT *
FROM t1

┌─a─┬─b─┐
│ 2 │ 2 │
└───┴───┘

1 rows in set. Elapsed: 0.032 sec.

此时的 metadata 里 Data Version 已经递增到 2:

cat ckdatas/metadata/ckdb/.metadata 
Version:	1
Binlog File:	mysql-bin.000001
Binlog Position:	1171
Data Version:	2

Update

执行mysql :

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 2 |    2 |
+---+------+
1 row in set (0.00 sec)

mysql> update t1 set b=b+1;

mysql> select * from t1;
+---+------+
| a | b    |
+---+------+
| 2 |    3 |
+---+------+
1 row in set (0.00 sec)

ClickHouse Slave:

clickhouse :) select * from t1;

SELECT *
FROM t1

┌─a─┬─b─┐
│ 2 │ 3 │
└───┴───┘

1 rows in set. Elapsed: 0.023 sec.

问题：
clikhouse创建复制通道时会报错：

Received exception from server (version 21.8.4):
Code: 537. DB::Exception: Received from localhost:9000. DB::Exception: Illegal MySQL variables, the MaterializeMySQL engine requires default_authentication_plugin='mysql_native_password'.

解决：
在mysql的/etc/my.cnf中添加

#配置插件为了clickhouse可以建表
default_authentication_plugin=mysql_native_password