Mysql数据同步Clickhouse

原创已于 2023-06-24 15:21:27 修改 · 707 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #clickhouse #服务器

于 2023-06-19 22:14:00 首次发布

clickhouse 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了如何使用ClickHouse的[experimental] MaterializedMySQL引擎将MySQL数据同步到ClickHouse。需要在MySQL服务器端配置default_authentication_plugin为mysql_native_password和gtid_mode为on，并确保enforce_gtid_consistency开启。在ClickHouse端，此功能标记为实验性的，需要注意配置。MaterializedMySQL不支持直接的INSERT、DELETE和UPDATE操作，但复制过程中会处理这些操作。提供了多个参考资料以供进一步阅读。

使用[experimental] MaterializedMySQL引擎同步Mysql数据

ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询

官网相关配置链接
MySQL服务器端配置

为了MaterializedMySQL的正确工作，有一些必须设置的MySQL端配置设置:

default_authentication_plugin = mysql_native_password ，因为 MaterializedMySQL 只能授权使用该方法。
gtid_mode = on，因为基于GTID的日志记录是提供正确的 MaterializedMySQL复制的强制要求。
:::info “注意” 当打开gtid_mode时，您还应该指定enforce_gtid_consistency = on。
注意 ubuntu配置 default_authentication_plugin = mysql_native_password
需要再 mysql.conf.d 文件夹下的 my.cnf 里面配置

root@ugmwygs2obenrie:/etc/mysql# ls
conf.d  debian.cnf  debian-start  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d
root@ugmwygs2obenrie:/etc/mysql# cd mysql.conf.d
root@ugmwygs2obenrie:/etc/mysql/mysql.conf.d# ls
mysql.cnf  mysqld.cnf  # 两个文件
root@ugmwygs2obenrie:/etc/mysql/mysql.conf.d# vim mysqld.cnf
root@ugmwygs2obenrie:/etc/mysql/mysql.conf.d# vim mysql.cnf
root@ugmwygs2obenrie:/etc/mysql/mysql.conf.d# vim mysqld.cnf
root@ugmwygs2obenrie:/etc/mysql/mysql.conf.d# sudo service mysql restart   # 重启Mysql

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql
default_authentication_plugin = mysql_native_password   # 添加这句话

mysql 配置和查看代码

show global variables like '%gtid_%'; 

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';


set GLOBAL log_bin_use_v1_row_events = on;
show global variables like '%log_bin%';
set GLOBAL log_bin_use_v1_row_events='OFF';
set default_authentication_plugin='mysql_native_password';   # 无法设置需要到my.cnf下配置

show variables like 'default_authentication_plugin';  # 这句可以检测密码验证是否配置成功

show variables like 'ENFORCE_GTID_CONSISTENCY';

ClickHouse端设置

注意这个功能是[experimental] 需要再clickhouse-client中设置
进入client命令

WUYING\obenrie:~$ clickhouse-client -u default --password 123456  # 如果没有密码 clickhouse-client
ClickHouse client version 22.2.2.1.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.2.2 revision 54455.

WUYING.LOCAL :) SET allow_experimental_database_materialized_mysql = 1

SET allow_experimental_database_materialized_mysql = 1
Query id: 70114b3b-d2e5-4810-a40f-67559813b319
Ok.
0 rows in set. Elapsed: 0.001 sec. 

# 设置完成mysql配置完成即可在ClickHouse端创建表
WUYING.LOCAL :) CREATE DATABASE my_demo_bin 
		ENGINE = MaterializedMySQL('127.0.0.1:3306', 'demo', 'root', '123456');
		# host 不能用localhost

通过以上配置即可同步mysql数据表

MaterializedMySQL不支持直接的 INSERT， DELETE 和 UPDATE 查询。然而，它们在数据复制方面得到了支持:

MySQL INSERT查询被转换为_sign=1的INSERT查询。
MySQL DELETE查询被转换为INSERT，并且_sign=-1。
如果主键被修改了，MySQL的 UPDATE 查询将被转换为 INSERT 带 _sign=1 和INSERT 带有_sign=-1;如果主键没有被修改，则转换为INSERT和_sign=1。

其他注意事项参见官网

参考链接
https://blog.csdn.net/vkingnew/article/details/108829047
https://blog.csdn.net/u011110301/article/details/116238905
https://www.cnblogs.com/kerrycode/p/9749096.html
https://www.cnblogs.com/xibuhaohao/articles/14368292.html#_label1_0