MySQL到clickhouse 数据库迁移 大量 clickhouse mysql 增量

一、MaterializeMySQL database engine 支持的情况

使用MaterializeMySQL存储引擎，需要一下先决条件

1.支持mysql 库级别的数据同步，暂不支持表级别的。
2.MySQL 库映射到clickhouse中自动创建为ReplacingMergeTree 引擎的表
3.支持全量和增量同步，首次创建数据库引擎时进行一次全量复制，之后通过监控binlog变化进行增量数据同步
4.支持的MySQL版本：5.6 5.7 8.0
5.支持的操作:insert，update，delete，alter，create，drop，truncate等大部分DDL操作

二、使用MaterializeMySQL 引擎的先决条件

1、MySQL部分

1）开启binlog并设置为row格式：

在MySQL配置文件/etc/my.cnf中加入

log-bin=mysqlbin.log
binlog_format=ROW

2）开启GTID模式

在MySQL配置文件/etc/my.cnf中加入

gtid_mode=on

enforce_gtid_consistency=1

不开启GTID模式则会报错

ch查询创建MaterializeMySQL引擎的表

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_PERMISSIVE instead of ON…

3）localhost连接

如果只是自己在一台服务器做测试，使用localhost来创建MaterializeMySQL，那么默认去找/tmp/mysql.sock，而不是MySQL配置文件中的sock

MySQL配置文件中的sock配置如下

解决办法：修改MySQL sock配置，重启MySQL

重启MySQL后，不能再用localhost登录，直接用127.0.0.1

在创建MaterializeMySQL

4）MySQL表必须要有主键，否则无法同步到Clickhouse中

2、Clickhouse部分

users.xml配置文件添加

<allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>

并重启clickhouse

否则报错

Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: MaterializeMySQL is an experimental database engine. Enable allow_experimental_database_materialize_mysql to use it…

三、clickhouse创建MaterializeMySQL

1、MySQL创建测试DB与表，并插入数据（表必须有主键）

mysql> use mych;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>
mysql>
mysql> create table chtomysql(id int auto_increment primary key,name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into chtomysql values (1,'xxa'),(2,'acscas');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>
mysql>
mysql> select * from chtomysql;
+----+--------+
| id | name |
+----+--------+
| 1 | xxa |
| 2 | acscas |
+----+--------+
2 rows in set (0.00 sec)

mysql>

2、Clickhouse创建MaterializeMySQL引擎的DB

ch01 :) create database mych ENGINE = MaterializeMySQL('192.168.88.128:3306', 'mych', 'root', 'VoracletestA@1');

CREATE DATABASE mych
ENGINE = MaterializeMySQL('192.168.88.128:3306', 'mych', 'root', 'VoracletestA@1')

Ok.

0 rows in set. Elapsed: 0.014 sec.

ch01 :) use mych;

USE mych

Ok.

0 rows in set. Elapsed: 0.001 sec.

ch01 :) show tables;

SHOW TABLES

┌─name──────┐
│ chtomysql │
└───────────┘

1 rows in set. Elapsed: 0.005 sec.

ch01 :) select * from chtomysql;

SELECT *
FROM chtomysql

┌─id─┬─name───┐
│ 1 │ xxa │
│ 2 │ acscas │
└────┴────────┘

2 rows in set. Elapsed: 0.009 sec.

ch01 :)

四、Clickhouse相关测试

支持的操作:insert，update，delete，alter，create，drop，truncate等大部分DDL操作

1、insert测试

MySQL insert 数据

clickhouse查看响应数据

2、update测试

mysql update数据

clickhouse查看响应数据

3、delete测试

mysql delete数据

clickhouse查看响应数据

4、 alter测试

1）增加字段、带默认值

2）增加字段、无默认值

3）删除字段

5、create测试

MySQL creaet 表

clickhouse查看响应：MySQL新创建的表mytab，并未同步过来，看网上文章有的可以同步过来

6、drop测试

mysql drop table

clickhouse 查看表响应：表已被删除

7、truncate测试

MySQL truncate 表

clickhouse查看响应：ch的表数据也被truncate

---