关于mysql:第02期ClickHouse-单机部署以及从-MySQL-增量同步数据

6次阅读

共计 12744 个字符，预计需要花费 32 分钟才能阅读完成。

本期作者：邓亚运
37 互娱高级 DBA，负责公司 MySQL，Redis，Hadoop，Clickhouse 集群的治理和保护。

随着数据量的回升，OLAP 始终是被探讨的话题，尽管 druid，kylin 可能解决 OLAP 问题，然而 druid，kylin 也是须要和 hadoop 全家桶一起用的，异样的轻便，再说我也搞不定，那只能找我能搞定的技术。故引进 clickhouse，对于 clickhouse 在 17 年自己就开始关注，并且写了一些入门的介绍，直到 19 年 clickhouse 性能缓缓的丰盛才又缓缓的关注，并且编写了同步程序，把 mysql 数据实时同步到 clickhouse，并且最终在线上应用起来。

对于 clickhouse 是什么请自行查阅官网：

https://clickhouse.yandex/

clickhouse 官网性能测试：

https://clickhouse.yandex/ben…

clickhouse 面对海量数据，比方单表过百亿能够应用集群（复制 + 分片），如果数据量比拟小，比方单表 10-20 亿应用单机就足以满足查问需要。如果应用复制须要应用 zk，更多集群的请自行查阅官网材料。

在 2016 年 clickhouse 刚开始开源的时候对 Ubuntu 反对十分敌对，一个 apt 命令就能够装置了。对于 centos 等零碎反对就比拟差，须要本人编译，而且不肯定可能胜利。随着应用人群的扩充，目前对于 centos 反对也是十分的敌对了，有 rpm 包能够间接装置。甚至目前 Altinity 公司曾经制作了 yum 源，增加源之后间接 yum 装置实现。这个在官网文档外面也有提到。

参考：

https://clickhouse.yandex/doc… https://github.com/Altinity/clickhouse-rpm-install

目前线上应用的是 centos 7.0 的零碎。之所以应用 7.0 的零碎是因为同步数据的程序是用 python 写的，而且用到的一个外围包：python-mysql-replication 须要应用 python 2.7 的环境。同时因为 clickhouse 不兼容 mysql 协定，为了不便开发接入零碎不必过多更改代码，引入了 proxysql 兼容 mysql 协定，clickhouse 最新版本曾经反对 mysql 协定，反对 clickhouse 的 proxysql 也须要 python 2.7 的环境，所以罗唆间接用 centos 7.0 零碎

服务器数量：1 台
操作系统：centos 7.1
装置服务：clickhouse，mysql
装置 mysql 是测试 clickhouse 从 mysql 同步数据。

增加 yum 源
curl -s https://packagecloud.io/insta… | sudo bash
yum 装置
yum install -y clickhouse-server clickhouse-client
服务启动
/etc/init.d/clickhouse-server start
默认数据寄存地位是： /var/lib/clickhouse/
登录，查看数据库（默认用户是 default，明码为空）

[root@ck-server-01 sync]# clickhouse-client -h 127.0.0.1
ClickHouse client version 19.9.2.4.
Connecting to 127.0.0.1:9000 as user default.
Connected to ClickHouse server version 19.9.2 revision 54421.

ck-server-01 :) show databases;

SHOW DATABASES

┌─name────┐
│ default │
│ system  │
└─────────┘

2 rows in set. Elapsed: 0.003 sec.

default 数据库外面没有任何货色，和 mysql 外面的 test 库是一样的。system 库看名字就晓得是什么。到这里 clickhouse 就部署实现，是不是很简略？

补充一点，在官网的文档外面有几点倡议：

敞开大页

调整内存应用

敞开 cpu 节能模式

echo 'performance' | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
echo 0 > /proc/sys/vm/overcommit_memory
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled

这里不做介绍。如果想从 mysql 同步数据那么 binlog 格局必须是 row。而且必须 binlog_row_image=full

同步程序能够放在 clickhouse 服务器下面，也能够独自放在其余服务器。同步程序应用 pypy 启动，所以安装包的时候须要装置 pypy 的包。

yum -y install pypy-libs pypy pypy-devel
wget https://bootstrap.pypa.io/get-pip.py
pypy get-pip.py
/usr/lib64/pypy-5.0.1/bin/pip install MySQL-python
/usr/lib64/pypy-5.0.1/bin/pip install mysql-replication
/usr/lib64/pypy-5.0.1/bin/pip install clickhouse-driver
/usr/lib64/pypy-5.0.1/bin/pip install redis

这里也装置了 redis 模块是因为同步的 binlog pos 能够寄存在 redis 外面，当然程序也是反对寄存在文件外面。proxysql 装置（次要是为了 clickhouse 兼容 mysql 协定）：proxysql 在这里下载：https://github.com/sysown/pro… 抉择带 clickhouse 的包下载，否则不会反对 clickhouse。ps：较新版本的 clickhouse 曾经原生兼容 mysql 协定。

rpm -ivh proxysql-2.0.3-1-clickhouse-centos7.x86_64.rpm
启动（必须这样启动，否则是不反对 clickhouse 的）：
proxysql –clickhouse-server
登录 proxysql，设置账户：

mysql -uadmin -padmin -h127.0.0.1 -P6032
INSERT INTO clickhouse_users VALUES ('clicku','clickp',1,100);
LOAD CLICKHOUSE USERS TO RUNTIME;
SAVE CLICKHOUSE USERS TO DISK;

应用 proxysql 连贯到 clickhouse：

[root@ck-server-01 sync]# mysql -u clicku -pclickp -h 127.0.0.1 -P6090
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28356
Server version: 5.5.30 (ProxySQL ClickHouse Module)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+---------+
| name    |
+---------+
| default |
| system  |
+---------+

mysql 外面有个库 yayun，库外面有张表 tb1，同步这张表到 clickhouse

mysql> use yayun;
Database changed
mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pay_money` decimal(20,2) NOT NULL DEFAULT '0.00',
  `pay_day` date NOT NULL,
  `pay_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

1. clickhouse 外面建库，建表。

ck-server-01 :) create database yayun;

CREATE DATABASE yayun

Ok.

0 rows in set. Elapsed: 0.021 sec.

2. 建表（clickhouse 建表的格局以及字段类型和 mysql 齐全不一样，如果字段少还能够本人建，如果字段多比拟苦楚，能够应用 clickhouse 自带的从 mysql 导数据的命令来建表），在建表之前须要进行受权，因为程序同步也是模仿一个从库拉取数据.

GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'ch_repl'@'127.0.0.1' identified by '123';

3. 登陆 clickhouse 进行建表

ck-server-01 :) use yayun;

USE yayun

Ok.

0 rows in set. Elapsed: 0.001 sec. 

ck-server-01 :) CREATE TABLE tb1
:-]  ENGINE = MergeTree
:-]  PARTITION BY toYYYYMM(pay_time)
:-]  ORDER BY (pay_time) AS
:-]  SELECT *
:-]  FROM mysql('127.0.0.1:3306', 'yayun', 'tb1', 'ch_repl', '123') ;

CREATE TABLE tb1
ENGINE = MergeTree
PARTITION BY toYYYYMM(pay_time)
ORDER BY pay_time AS
SELECT *
FROM mysql('127.0.0.1:3306', 'yayun', 'tb1', 'ch_repl', '123') 

Ok.

0 rows in set. Elapsed: 0.031 sec.

这里应用 MergeTree 引擎，MergeTree 是 clickhouse 外面最牛逼的引擎，反对海量数据，反对索引，反对分区，反对更新删除。toYYYYMM(pay_time) 的意思是依据 pay_time 分区，粒度是按月。ORDER BY (pay_time) 的意思是依据 pay_time 排序存储，同时也是索引。下面的 create table 命令如果 mysql 表外面当前数据那么数据也会一并进入 clickhouse 外面。通常会 limit 1，而后更改一下表构造。下面没有报错的话咱们看看 clickhouse 外面的表构造：

ck-server-01 :) show create table tb1;

SHOW CREATE TABLE tb1

┌─statement────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE yayun.tb1 (`id` UInt32, `pay_money` String, `pay_day` Date, `pay_time` DateTime) ENGINE = MergeTree PARTITION BY toYYYYMM(pay_time) ORDER BY pay_time SETTINGS index_granularity = 8192 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.002 sec.

其中这里的 index_granularity = 8192 是指索引的粒度。如果数据量没有达到百亿，那么通常无需更改。表构造也创立实现当前当初配置同步程序配置文件：metainfo.conf


[root@ck-server-01 sync]# cat metainfo.conf 
# 从这里同步数据
[master_server]
host='127.0.0.1'
port=3306
user='ch_repl'
passwd='123'
server_id=101

# redis 配置信息，用于寄存 pos 点
[redis_server]
host='127.0.0.1'
port=6379
passwd='12345'
log_pos_prefix='log_pos_'

#把 log_position 记录到文件
[log_position]
file='./repl_pos.log'

# ch server 信息，数据同步当前写入这里
[clickhouse_server]
host=127.0.0.1
port=9000
passwd=''user='default'
#字段大小写. 1 是大写，0 是小写
column_lower_upper=0

# 须要同步的数据库
[only_schemas]
schemas='yayun'

# 须要同步的表
[only_tables]
tables='tb1'

# 指定库表跳过 DML 语句(update,delete 可选)
[skip_dmls_sing]
skip_delete_tb_name = ''skip_update_tb_name =''

#跳过所有表的 DML 语句（update，delete 可选）[skip_dmls_all]
#skip_type = 'delete'
#skip_type = 'delete,update'
skip_type = ''

[bulk_insert_nums]
#多少记录提交一次
insert_nums=10
#抉择每隔多少秒同步一次, 正数示意不启用, 单位秒
interval=60

# 同步失败告警收件人
[failure_alarm]
mail_host= 'xxx'
mail_port= 25
mail_user= 'xxx'
mail_pass= 'xxx'
mail_send_from = 'xxx'
alarm_mail = 'xxx'

#日志寄存门路
[repl_log]
log_dir="/tmp/relication_mysql_clickhouse.log"

和 mysql 搭建从库一样，配置从哪里开始同步，看 mysql 的 pos 点：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000069 |  4024404 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

把 pos 点写入文件或者 redis，我抉择记录到文件就是。


[root@ck-server-01 sync]# cat repl_pos.log 
[log_position]
filename = mysql-bin.000069
position = 4024404

[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --help
usage: Data Replication to clikhouse [-h] [-c CONF] [-d] [-l]

mysql data is copied to clikhouse

optional arguments:
  -h, --help            show this help message and exit
  -c CONF, --conf CONF  Data synchronization information file
  -d, --debug           Display SQL information
  -l, --logtoredis      log position to redis ,default file

By dengyayun @2019
[root@ck-server-01 sync]#

默认 pos 点就是记录文件，无需再指定记录 binlog pos 形式

[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --conf metainfo.conf --debug
11:59:54 INFO     开始同步数据工夫 2019-07-17 11:59:54
11:59:54 INFO     从服务器 127.0.0.1:3306 同步数据
11:59:54 INFO     读取 binlog: mysql-bin.000069:4024404
11:59:54 INFO     同步到 clickhouse server 127.0.0.1:9000
11:59:54 INFO     同步到 clickhouse 的数据库: ['yayun']
11:59:54 INFO     同步到 clickhouse 的表: ['tb1']

mysql> insert into  tb1 (pay_money,pay_day,pay_time)values('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00'),('66.22','2019-06-29','2019-06-29 14:00:00') ;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from tb1;
+----+-----------+------------+---------------------+
| id | pay_money | pay_day    | pay_time            |
+----+-----------+------------+---------------------+
|  1 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
|  3 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
|  5 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
|  7 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
|  9 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 11 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 13 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 15 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 17 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |
| 19 |     66.22 | 2019-06-29 | 2019-06-29 14:00:00 |

同步程序日志输入：


[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --conf metainfo.conf --debug
12:12:09 INFO     开始同步数据工夫 2019-07-17 12:12:09
12:12:09 INFO     从服务器 127.0.0.1:3306 同步数据
12:12:09 INFO     读取 binlog: mysql-bin.000069:4024404
12:12:09 INFO     同步到 clickhouse server 127.0.0.1:9000
12:12:09 INFO     同步到 clickhouse 的数据库: ['yayun']
12:12:09 INFO     同步到 clickhouse 的表: ['tb1']
12:12:09 INFO     INSERT 数据插入 SQL: INSERT INTO yayun.tb1 VALUES, [{u'id': 1, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 3, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 5, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 7, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 9, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 11, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 13, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 15, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 17, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}, {u'id': 19, u'pay_money': '66.22', u'pay_day': datetime.date(2019, 6, 29), u'pay_time': datetime.datetime(2019, 6, 29, 14, 0)}]

ck-server-01 :) select * from tb1;

SELECT *
FROM tb1 

┌─id─┬─pay_money─┬────pay_day─┬────────────pay_time─┐
│  1 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  3 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  5 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  7 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  9 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 11 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 13 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 15 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 17 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 19 │ 66.22     │ 2019-06-29 │ 2019-06-29 14:00:00 │
└────┴───────────┴────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.005 sec.


mysql> update tb1 set pay_money='88.88';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> select * from tb1;
+----+-----------+------------+---------------------+
| id | pay_money | pay_day    | pay_time            |
+----+-----------+------------+---------------------+
|  1 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
|  3 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
|  5 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
|  7 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
|  9 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
| 11 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
| 13 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
| 15 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
| 17 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
| 19 |     88.88 | 2019-06-29 | 2019-06-29 14:00:00 |
+----+-----------+------------+---------------------+
10 rows in set (0.00 sec)


ck-server-01 :) select * from tb1;

SELECT *
FROM tb1 

┌─id─┬─pay_money─┬────pay_day─┬────────────pay_time─┐
│  1 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  3 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  5 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  7 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│  9 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 11 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 13 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 15 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 17 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
│ 19 │ 88.88     │ 2019-06-29 │ 2019-06-29 14:00:00 │
└────┴───────────┴────────────┴─────────────────────┘

10 rows in set. Elapsed: 0.009 sec.

能够看见数据都同步实现。

https://github.com/yymysql/my…

目前线上报表业务都曾经在应用 clickhouse，数据同步采纳自行开发的同步程序进行同步。目前数据一致性没有什么问题。当然同步的表须要有自增主键，否则有些状况比拟难解决。延时也比拟小。数据的延时以及数据的一致性都有监控。总体来说应用 clickhouse 解决 olap 还是十分不错的抉择，小伙伴们能够尝试。

1. https://clickhouse-driver.rea…. https://python-mysql-replicat…. https://clickhouse.yandex/doc…. https://github.com/sysown/pro…

对于 ClickHouse 的技术内容，你们还有什么想晓得的吗？连忙 留言通知小编吧！

正文完

mysql

发表至： mysql

2020-08-20

0

关于mysql:4直方图介绍和使用MySQL索引学习

关于mysql:docker容器matomo安装

关于mysql:故障分析-密码使用特殊字符

关于mysql:MySQL三大日志binlogredologundolog详解

关于php:PHP每天都在用的数组你足够了解吗

关于mysql:第02期ClickHouse-单机部署以及从-MySQL-增量同步数据

背景

单机部署（以前的文章也有写过单机部署）

测试环境：

clickhouse 装置：

mysql 部署请自行部署。

装置同步程序依赖的包

proxysql 装置及配置

mysql 同步数据到 clickhouse

设置 pos 点：

启动同步程序：

mysql 插入 10 条数据：

clickhouse 数据查问：

mysql 数据更新：

clickhouse 数据查问：

代码地址：

总结

参考资料

站内搜索