Clickhouse与mysql主从复制模式MaterializeMySQL引擎实战

1、前言

clickhouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据，极大提升了数仓的查询性能和数据同步的时效性；原有mysql中承担的数据分析工作 可交由clickhouse去做，这么做可显著降低线上mysql的负载，从此OLTP与OLAP业务实现完美融
目前 MaterializeMySQL database engine 还不支持表级别的同步操作，需要将整个mysql database映射到clickhouse，映射过来的库表会自动创建为ReplacingMergeTree表engine。
MaterializeMySQL 支持全量和增量同步，首次创建数据库引擎时进行一次全量复制，之后通过监控binlog变化进行增量数据同步；该引擎支持mysql 5.6/5.7/8.0版本数据库，兼容insert，update，delete，alter，create，drop，truncate等大部分DDL操作。

2、数据库版本要求

4、Clickhouse与mysql主从配置方法

A B 为主从集群，C为CH从B

什么是GTID模式？

1、全局唯一，一个事务对应一个GTID
2、替代传统的binlog+pos复制；使用master_auto_position=1自动匹配GTID断点进行复制
3、MySQL5.6开始支持
4、在传统的主从复制中，slave端不用开启binlog；但是在GTID主从复制中，必须开启binlog
5、slave端在接受master的binlog时，会校验GTID值，则slave需要存储硬盘比之前更大！
6、为了保证主从数据的一致性，多线程同时执行一个GTID
Master_UUID:序列号举例：ceb0ca3d-8366-11e8-ad2b-000c298b7c9a:1-5ceb0ca3d-8366-11e8-ad2b-000c298b7c9a其实就是master的uuid值；1-5是序列号，每次一个事务完成都会自增1，也就是说下一次为1-6。

工作原理

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描

3、 配置方法

第一步：确认主从2个MySQL都打开binlog，并且把之前的主从配置都清空

 show variables like 'log_bin';

第二步：2个MYSQL

vim /etc/my.cnf

增加

gtid-mode=ON
enforce-gtid-consistency=ON

重启服务
第三步：
主从数据人工处理保持一致
主库：

SHOW MASTER STATU

获取取binlog文件名跟位置

从库

CHANGE MASTER TO
MASTER_HOST='192.168.31.82',
MASTER_USER='nasen',
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='binlog.000006',
MASTER_LOG_POS=856;
start slave;

A与B只主从某些常用表，不做全库。
例如：配置中指定清楚那些表

replicate-do-table=test.nasen_test1
replicate-do-table=test.nasen_test2
replicate-do-table=test.nasen_test3

另外提前配置好MYSQL的密码模式

default_authentication_plugin=mysql_native_password

配置好之后，暂时不配置主从
先按表逐个还原到B
此处我们用到按表导出的命令
此处要建表语句
参数

mysqldump -h 192.168.3.200 -P 3306 -uroot -p123456 --default-character-set=utf8 数据库名称 表A 表B> /home/迁移表.sql

先把备份文件保存再B从数据库的/home下暂时不执行恢复数据，先设置与clickhouse的主从配置如下
第1步：进到CK机器C用本机客户端打开
执行命令：
根据你之前设置的用户名密码

clickhouse-client --user default --password 123456

执行开关命令

SET allow_experimental_database_materialize_mysql = 1

执行命令
指定主从那个数据库，需要用户名密码

CREATE database  test ENGINE = MaterializeMySQL('192.168.3.203:3306','test','root','123456');

这个SQL与MYSQL建立主从关系，用户名密码从哪个数据库

执行失败报错
修改MYSQL的配置文件
在[mysqld]中添加下边的代码

default_authentication_plugin=mysql_native_password

用数据库工具查询刷新之后
存在了一个数据库，但是这个数据库是空的

配置到这一步之后，B与C 的数据库此时都是空的。
下一步：
在B上面执行source命令恢复之前备份的表SQL文件。
执行过程中，观察CK会逐渐随着B的表增加而增加
恢复完成之后
B与C建立了主从关系。
最后打开A与B的主从配置。验证主从是否正常。

5、测试验证

在A上面，插入数据，或删除，更新。
C的CK的中间表1的数据跟着自动变化。验证成功！

最后

以上就是个性朋友为你收集整理的Clickhouse与mysql主从复制模式MaterializeMySQL引擎实战的全部内容，希望文章能够帮你解决Clickhouse与mysql主从复制模式MaterializeMySQL引擎实战所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。