踩Mariadb和ClickHouse的坑

一、版本信息

        Mariadb：MariaDB 10.5.17

        ClickHouse：clickhouse-server-22.2.2.1

二、背景

        系统正式环境使用的是Mariadb，大体功能开发得差不多了，紧接着就是一系列的报表需要开发，于是老大就引入了ClickHouse数据库，这玩意儿查询快，于是我们现在要解决Mariadb数据库到ClickHouse数据库的数据同步问题。

        研究了一两天，ClickHouse支持MySQL表引擎，直接连接到MySQL中，但是不推荐这种方式进行数据同步，这种方式跟直接用MySQL没有差别，体现不了ClickHouse的价值，还有一种方式是通过解析MySQL binlog的方式同步数据，这种方式简单粗暴，最开始可以全量数据同步，增量部分数据可以通过binlog日志同步，是挺香，接下来就看看踩了哪些坑。

三、坑

        1、MySQL表引擎方式

                CREATE TABLE mysql_table1 (
                        id UInt64,
                        column1 String
                )
                ENGINE = MySQL('127.0.0.1','db1','table1','mysql_clickhouse','Password123!')

参数	描述	例
host	主机名或 IP	127.0.0.1
database	MySQL 数据库名称	db1
table	MySQL 表名	table1
user	用于连接到 MySQL 的用户名	mysql_clickhouse
password	用于连接到 MySQL 的密码	Password123!

                坑：虽然这个方式实现起来简单，但是实际上没有作用，数据量一旦大起来，这种方式就跟用MySQL一样的，该有多慢就有多慢。

        2、MaterializedMySQL方式

                通过创建复制管道，获取MySQL binlog日志，通过日志解析方式进行数据同步。

                MySQL数据库需要做如下配置：

                        /etc/my.cnf文件中[mysqld]下需要添加的配置项：

                        # 指定binlog日志存储位置
                        log_bin=/var/lib/mysql/mysql-bin
                        # 这里一定是row格式
                        binlog_format=ROW# 开启GTID模式
                        gtid_mode=ON
                        # 设置主从强一致性
                        enforce_gtid_consistency=1
                        # 记录日志
                        log_slave_updates=1

                ClickHouse中需要做如下配置：

                        #开启物化引擎

                        set global on cluster default max_partitions_per_insert_block = 10000;
                        SET allow_experimental_database_materialized_mysql=1;CREATE DATABASE mt ENGINE = MaterializeMySQL('localhost:3306', 'mt', 'root', '123456')

                这种方式是全库同步，先分析一下有哪些缺点：        

                        1,同步mysql数据之前,mysql的每张表应该都有PRIMARY KEY(如果没有主键,同步会报错)

                        2,MaterializedMySQL是库级别的引擎,同步的时候会以全库中表数据同步

                        3,mysql数据同步到clickhouse后会发生索引转换:在ClickHouse表中,MySQL的 PRIMARY KEY 和 INDEX 子句被转换为 ORDER BY 元组

                        4,mysql在转换为clickhouse表的时候,每张表都会新增两个字段:_sign(1:写入,-1:删除), _version

                        5,在clickhouse中同步新增时,并没有实现物理意义上的删除,只通过_sign标志字段来实现数据过滤

                        6,在mysql转化clickhouse时,默认使用的ReplacingMergeTree引擎,保证没有重复数据出现             

                           坑：Mariadb根本就没有gtid_mode这个配置项，添加此配置项之后启动失败     

                3、Canal中间件方式

                        这种方式配置是成功了，成功启动了deployer和adapter，但是在做测试的时候，在Mariadb中insert、delete数据时，ClickHouse这边就开始报错了，类似报错如下，网上也没找到太多资料，就没研究了。

                        

 

                4、Mariadb->MySQL->ClickHouse方式 

                                安装了MySQL社区版，搭建了Mariadb至MySQL的主从，主从正常，接下来就是MySQL到ClickHouse的数据迁移工作，采用了第二个坑的方式，直接在MySQL中配置gtid_mode=ON等配置项，结束之后MySQL启动失败，主从异常，报错如下：

                意为：主数据库的GTIO_MODE配置为关闭，从数据库为开启

                         那么又回到了第二个坑，需要去开启主数据库的GTID_MODE配置，然而主数据库是Mariadb，根本没有GTID_MODE配置项。

                至此彻底放弃Mariadb到ClickHouse的数据实时同步，延迟同步做报表吧。有解决方案的伙伴可以留言哟