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

忘是亡心i 2022-09-15 11:54 251阅读 0赞

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