MySQL迁移到ClickHouse

     ClickHouse 新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据，提升数仓的查询性能和数据同步的时效性；原有mysql中承担的数据分析工作可交由clickhouse去做，这么做可显著降低线上mysql的负载，从此OLTP与OLAP业务实现完美融合。

   MaterializeMySQL 同步流程：

1.创建MySQL的映射表，在MaterializeMySQL引擎中填写MySQL连接信息(ip地址+端口+数据库名称+用户名+密码)，ClickHouse会创建一个引擎为ReplacingMergeTree 的数据表,其中MySQL表的 PRIMARY KEY 作为了 ReplacingMergeTree 的 PARTITION BY，并且按照类型大小除以1000整除;
2.此时ClickHouse会拉取MySQL对象库下的所有表结构，并且添加_sign和_version字段，_sign字段表示数据是否删除，_version字段表示数据最新版本；
3.首次同步采用全量同步MySQL中的表数据，_version版本全为1；
4.后续采用增量同步消费binlog中的SQL,当ClickHouse监听到 insert、update 和 delete 事件时，利用_version号在 databse 内全局自增记录最新数据，当ClickHouse同步到delete语句时，更新_sign字段的值为-1（此时，熟悉MySQL的肯定会想到伪删除，对没错，它就是利用伪删除）。

   

在mysql的服务器的my.cnf , 增加log-bin配置

server-id=1
log_bin=binlog.bin
expire_logs_days=15
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true

测试结果如下：

• 在MySQL上创建一个表。

mysql> create database db1;mysql> create table test1 (a INT PRIMARY KEY, b INT);

• 在ClickHouse上新建MaterializeMySQL的表。

# 这里要专门设置以便clickhouse能使用这个特性
ck> set allow_experimental_database_materialized_mysql=1;  
## 这里创建连接到user@172.30.1.17:3306/db1的database连接，命名为db1_ckck> CREATE DATABASE db1_ck ENGINE = MaterializeMySQL('172.30.1.17:3306', 'db1', 'root', '***');
## 进入db1_ck这个数据库ck> use db1_ck;
## 查看db1_ck库下的表，可以看到test1这个表ck> show tables;

• 查看test1这个表在ck上的表结构。

ck> show create table test1;
CREATE TABLE db1_ck.test1(    `a` Int32,    `b` Nullable(Int32),    `_sign` Int8 MATERIALIZED 1,    `_version` UInt64 MATERIALIZED 1,    INDEX _version _version TYPE minmax GRANULARITY 1)ENGINE = ReplacingMergeTree(_version)PARTITION BY intDiv(a, 4294967)ORDER BY tuple(a)SETTINGS index_granularity = 8192

test1表使用的是ReplacingMergeTree引擎，表结构新增了两个字段_sign和_version作为隐藏字段，用于标识该行是否删除和版本号。这样将MySQL的update和delete统一转换成insert，充分利用ClickHouse快速导入数据、update和delete慢且容易出问题的特性。内部实现原理的细节和好处，我们将在另外一篇文章里面介绍。

• 在MySQL上操作，看数据会不会同步到ClickHouse上。

mysql> insert into test1 values (1,11),(2,22),(3,33);mysql> update test1 set b=77 where a=1;mysql> delete from test1 where a=2;
ck> select * from db1_ck.test1;┌─a─┬──b─┐│ 1 │ 77 ││ 3 │ 33 │└───┴────┘

可以看到，数据基本都正确地同步到ClickHouse上去了。

查看所有数据

select *, _version,_sign  from test1;

┌─a─┬──b─┬─_version─┬─_sign─┐
│ 2     │ 22     │        4 │    -1 │
└───┴────┴──────────┴───────┘
┌─a─┬──b─┬─_version─┬─_sign─┐
│ 1      │ 77 │        3 │     1 │
└───┴────┴──────────┴───────┘
┌─a─┬──b─┬─_version─┬─_sign─┐
│ 1     │ 11 │        2 │     1 │
│ 2     │ 22 │        2 │     1 │
│ 3     │ 33 │        2 │     1 │
└───┴────┴──────────┴───────┘