一、概述
ClickHouse 20.8.2.3 版本新增加了MaterializeMySQL的database引擎,该database能
映射到MySQL中的某个database ,并自动在ClickHouse中创建对应的ReplacingMergeTree。ClickHouse服务做为MySQL 副本,读取Binlog并执行DDL和DML请求,实现了基于MySQL Binlog机制的业务数据库实时同步功能。MaterializeMySQL同时支持全量和增量同步,在database创建之初会全量同步MySQL中的表和数据,之后则会通过binlog进行增量同步。
二、使用先决条件
2.1 MySQL部分
(1)确保MySQL开启了binlog功能,且格式为ROW
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog_format=ROW
(2)开启GTID部分
gtid_mode=on
enforce_gtid_consistency=1
如果不开启GTID会报错:
Code: 1002. DB::Exception: Received from localhost:9000. DB::Exception: The replication sender thread cannot start in AUTO_POSITION mode: this server has GTID_MODE = OFF instead of ON.. (UNKNOWN_EXCEPTION)
执行上面的操作后需要重启mysql
systemctl restart mysqld
2.2 clickhouse部分
编辑user.xml文件并重启clickhouse
vim /etc/clickhouse-server/user.xml
<allow_experimental_database_materialized_mysql>1</allow_experimental_database_materialized_mysql>
要不然会报错如下:
Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it.. (UNKNOWN_DATABASE_ENGINE)
三、测试部分
3.1 在MySQL中创建数据表并写入数据
CREATE DATABASE ggl_test;
CREATE TABLE `ggl_test`.`t_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int NOT NULL,
`name` text DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO ggl_test.t_organization (code, name,updatetime)
VALUES(1000,'Realinsight',NOW());
INSERT INTO ggl_test.t_organization (code, name,updatetime)
VALUES(1001, 'Realindex',NOW());
INSERT INTO ggl_test.t_organization (code, name,updatetime)
VALUES(1002,'EDT',NOW());
CREATE TABLE `ggl_test`.`t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO ggl_test.t_user (code) VALUES(1);
INSERT INTO ggl_test.t_user (code) VALUES(2);
3.2 ClickHouse中创建MaterializeMySQL数据库
CREATE DATABASE test_binlog ENGINE = MaterializeMySQL('ggl203:3306','ggl_test','root','123456');
3.3 查询测试