clickhouse 同步mysql数据

clickhouse 版本

docker 21.12.3.32
安装步骤请看上篇文章 docker 部署 clickhouse

配置 mysql 开启bin log

config

[mysqld]
server_id=1
log-bin=binlog
binlog_format=ROW

## 需要产生 bin log 日志的 库名 ,默认为空 所有库都产生bin log 记录
binlog_do_db= mydatebase1
binlog_do_db= mydatebase2

1
2
3
4
5
6
7
8
9

查看验证

SHOW VARIABLES LIKE '%log%bin%';
show variables like 'server_id';

1
2
3

配置 mysql GTID模式

#  需要 MySQL 5.7 以上 不用重启 启用
 SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'WARN';
 SET GLOBAL ENFORCE_GTID_CONSISTENCY = 'ON';
 SET GLOBAL GTID_MODE = 'OFF_PERMISSIVE';
 SET GLOBAL GTID_MODE = 'ON_PERMISSIVE';
 SET GLOBAL GTID_MODE = 'ON';

## 重启 启用 
[mysqld]
gtid_mode=on
enforce_gtid_consistency=on

1
2
3
4
5
6
7
8
9
10
11
12

验证

show variables like 'gtid_mode';
show variables like 'ENFORCE_GTID_CONSISTENCY';
1
2

idea 连接 clickhouse

clickhouse 开启物化引擎

set allow_experimental_database_materialized_mysql=1;

select * from system.settings where name ='allow_experimental_database_materialized_mysql';
1
2
3

clickhouse中创建同步表

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 (…)]

CREATE DATABASE test_bi
ENGINE = MaterializeMySQL('localhost:3306', 'test_bi', 'root', 'root')
1
2