共计 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 同步数据。
clickhouse 装置:
增加 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 部署请自行部署。
这里不做介绍。如果想从 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 协定。
proxysql 装置及配置
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 同步数据到 clickhouse
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"
设置 pos 点:
和 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 插入 10 条数据:
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)}]
clickhouse 数据查问:
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 数据更新:
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)
clickhouse 数据查问:
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 的技术内容,你们还有什么想晓得的吗?连忙 留言通知小编吧!