mysql实时同步clickhouse

部署Master-MySQL

开启BinLog功能：ROW模式
开启GTID模式：解决位点同步时MySQL主从切换问题（BinLog reset导致位点失效）

# my.cnf关键配置
gtid_mode=ON
enforce_gtid_consistency=1
binlog_format=ROW

部署Slave-ClickHouse

参考docker安装clickhouse

创建 Slave-ClickHouse 中 MaterializedMySQL database

# 查询
ca766e6b6e77 :) select * from system.settings where name ='allow_experimental_database_materialized_mysql';

SELECT *
FROM system.settings
WHERE name = 'allow_experimental_database_materialized_mysql'

Query id: 0f9597d4-1ffc-48d7-a961-43d9e9c58054

┌─name───────────────────────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type─┐
│ allow_experimental_database_materialized_mysql │ 0     │       0 │ Allow to create database with Engine=MaterializedMySQL(...). │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool │
└────────────────────────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴──────┘

1 rows in set. Elapsed: 0.002 sec.



# 开启materialized同步功能
ca766e6b6e77 :) set allow_experimental_database_materialized_mysql=1;

SET allow_experimental_database_materialized_mysql = 1

Query id: f3d1c0fb-9044-4d9a-bb90-3c2470630c4c

Ok.

0 rows in set. Elapsed: 0.001 sec.


# 创建slave库，参数分别是("mysqld服务地址", "待同步库名", "授权账户", "密码")
ca766e6b6e77 :) CREATE DATABASE pdf ENGINE = MaterializeMySQL('localhost:3306', 'pdf', 'root', 'password')

CREATE DATABASE pdf
ENGINE = MaterializeMySQL('localhost:3306', 'pdf', 'root', 'password')

Query id: a808e71c-521b-4419-831f-800c7d153060

Ok.

0 rows in set. Elapsed: 0.036 sec.

注意：网络上很多参数设置错误，错误如下：

# 设置参数出错，未知参数
Code: 115. DB::Exception: Received from localhost:9000. DB::Exception: Unknown setting allow_experimental_database_materialize_mysql. (UNKNOWN_SETTING)

# 问题解决
# https://github.com/ClickHouse/ClickHouse/issues/30163
# Rename `MaterializeMySQL` to `MaterializedMySQL`

DBeaver连接

数据库选择

地址和账号密码

测试连接

插入数据

clickhouse 查询

mysql 查询

本作品采用《CC 协议》，转载必须注明作者和本文链接