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
