Clickhouse-server增量同步复制MySQL数据详解


数据复制原理:

ClickHouse 可以挂载为 MySQL 的一个从库 ,先全量再增量的实时同步 MySQL 数据,基于它我们可以轻松的打造一套企业级解决方案,让 OLTP 和 OLAP 的融合。

目前支持 MySQL 5.6/5.7/8.0 版本,兼容Delete/Update 语句,及大部分常用的 DDL 操作。

 

安装软件版本:

[root@node220 clickhouse-server]# rpm -qa | grepclickhouse

clickhouse-server-20.10.3.30-2.noarch

clickhouse-client-20.10.3.30-2.noarch

clickhouse-common-static-20.10.3.30-2.x86_64

[root@node221 ~]# rpm -qa | grep mysql

mysql-community-client-5.7.18-1.el7.x86_64

mysql-community-libs-5.7.18-1.el7.x86_64

mysql-community-server-5.7.18-1.el7.x86_64

mysql-community-common-5.7.18-1.el7.x86_64

mysql-community-libs-compat-5.7.18-1.el7.x86_64

操作系统:

[root@node221 ~]# uname -a

Linux node221 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 823:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

[root@node221 ~]# cat /etc/redhat-release

CentOS Linux release 7.6.1810 (Core)

 

Mysql-Master数据库主库配置:

开启BINLOG和GITD:

vim /etc/my.cnf

#binlog

log_bin=mysql-bin

server_id=221

#Gtid

gtid_mode=on

enforce_gtid_consistency=on

 

配置clickhouse-server连接mysql的帐户:

GRANT SELECT, RELOAD, PROCESS, FILE, SHOW DATABASES,REPLICATION SLAVE, REPLICATION CLIENT, EVENT ON *.* TO ch_reader@'%';  

                              

ClickHouse Slave配置:

目前以 database 为单位进行复制,不同的 database可以来自不同的 MySQL master,这样就可以实现多个MySQL 源数据同步到一个 ClickHouse 做OLAP 分析功能。

 

开启开关:

[root@node220 clickhouse-server]# pwd

/etc/clickhouse-server

[root@node220 clickhouse-server]# ls

config.d  config.xml  preprocessed users.d  users.xml

[root@node220 clickhouse-server]# head users.xml

<?xml version="1.0"?>

<yandex>

    <!-- Profiles of settings. -->

    <profiles>

        <!-- Default settings.-->

        <default>

   <!-- Maximummemory usage for processing single query, in bytes. -->

     <max_memory_usage>10000000000</max_memory_usage>

 <allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>

 <!-- Usecache of uncompressed blocks of data. Meaningful only for processing many ofvery short queries. --

重新启动clickhouse

systemctl restartclickhouse-server

创建一个复制通道: 

node220 :) CREATE DATABASE hexin ENGINE =MaterializeMySQL('172.16.1.221:3306';,'hexin', 'ch_reader';, 'Ch_reader123!';);

Ok.

看下 ClickHouse 的同步位点:

 

[root@node220 hexin]# cat/var/lib/clickhouse/metadata/hexin/.metadata

Version:        2

Binlog File:        mysql-bin.000006

Executed GTID:       b1ed20bd-191e-11eb-be28-000c297e52fe:1-22

Binlog Position:        5401

Data Version:        4

 

查看clickhouse从mysql复制过来的表:

 

node220 :) use hexin;

 

USE hexin

 

Ok.

 

0 rows in set. Elapsed: 0.002 sec.

 

node220 :) show tables;

 

SHOW TABLES

 

┌─name─┐

│ rsc  │

│ test │

└──────┘

 

2 rows in set. Elapsed: 0.008 sec.

 

查看mysql中的信息:

 

mysql> show processlist;

+----+-----------+--------------------+-------+------------------+------+---------------------------------------------------------------+------------------+

| Id | User      | Host             | db    | Command         | Time | State                                                      |Info            |

+----+-----------+--------------------+-------+------------------+------+---------------------------------------------------------------+------------------+

|  6 | root      | localhost         | hexin | Sleep           | 1469 |                                                            |NULL            |

| 14 | ch_reader | 172.16.1.220:35546 | hexin |Sleep            |  741 |                                                           | NULL           |

| 15 | ch_reader | 172.16.1.220:35548 | NULL  |Binlog Dump GTID |  741 | Master has sent 

all binlog to slave; waiting formore updates | NULL            |

| 17 | root      | localhost         | NULL  | Query           |    0 | starting                                                    | showprocesslist |

+----+-----------+--------------------+-------+------------------+------+---------------------------------------------------------------+------------------+

4 rows in set (0.00 sec)

clickhouse-server时时复制mysql数据库配置成功

注意:

mysql中的表一定要有主键,不然会报错,无法复制数据到clickhouse,而且即使以后再在mysql中创建或修改任何数据,clickhouse也不会同步数据了

报以下错误:

2020.10.29 11:43:52.139725 [ 17746 ] {} <Error>MaterializeMySQLSyncThread: Code: 48, e.displayText() = DB::Exception: Thehexin.gmy cannot be materialized, because there is no primary keys., Stacktrace (when copying this message, always include the lines below):


参考:https://www.cnblogs.com/dbtech/p/13499555.html

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS