本期作者：邓亚运

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/benchmark.html

clickhouse 面对海量数据，比如单表过百亿可以使用集群（复制 + 分片），如果数据量比较小，比如单表 10-20 亿使用单机就足以满足查询需求。如果使用复制需要使用 zk，更多集群的请自行查阅官方资料。

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

在 2016 年 clickhouse 刚开始开源的时候对 Ubuntu 支持非常友好，一个 apt 命令就可以安装了。对于 centos 等系统支持就比较差，需要自己编译，而且不一定能够成功。随着使用人群的扩大，目前对于 centos 支持也是非常的友好 了，有 rpm 包可以直接安装。甚至目前 Altinity 公司已经制作了 yum 源，添加源之后直接 yum 安装完成。这个在官方文档里面也有提到。

参考：

https://clickhouse.yandex/docs/en/getting_started/ 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/install/repositories/altinity/clickhouse/script.rpm.sh | 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.1ClickHouse 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_governorecho 0 > /proc/sys/vm/overcommit_memoryecho 'never' > /sys/kernel/mm/transparent_hugepage/enabledmysql 部署请自行部署。

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

安装同步程序依赖的包

同步程序可以放在 clickhouse 服务器上面，也可以单独放在其他服务器。同步程序使用 pypy 启动，所以安装包的时候需要安装 pypy 的包。

yum -y install pypy-libs pypy pypy-develwget https://bootstrap.pypa.io/get-pip.pypypy 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/proxysql/releases 选择带 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 -P6032INSERT 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 -P6090mysql: [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 28356Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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 changedmysql> show create table tb1\G*************************** 1. row ***************************       Table: tb1Create 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=utf8mb41 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 tb1ENGINE = MergeTreePARTITION BY toYYYYMM(pay_time)ORDER BY pay_time ASSELECT *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=3306user='ch_repl'passwd='123'server_id=101
# redis配置信息，用于存放pos点[redis_server]host='127.0.0.1'port=6379passwd='12345'log_pos_prefix='log_pos_'
#把log_position记录到文件[log_position]file='./repl_pos.log'
# ch server信息，数据同步以后写入这里[clickhouse_server]host=127.0.0.1port=9000passwd=''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= 25mail_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.000069position = 4024404启动同步程序：

[root@ck-server-01 sync]# pypy mysql-clickhouse-replication.py --helpusage: 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 --debug11:59:54 INFO     开始同步数据时间 2019-07-17 11:59:5411:59:54 INFO     从服务器 127.0.0.1:3306 同步数据11:59:54 INFO     读取binlog: mysql-bin.000069:402440411:59:54 INFO     同步到clickhouse server 127.0.0.1:900011: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 --debug12:12:09 INFO     开始同步数据时间 2019-07-17 12:12:0912:12:09 INFO     从服务器 127.0.0.1:3306 同步数据12:12:09 INFO     读取binlog: mysql-bin.000069:402440412:12:09 INFO     同步到clickhouse server 127.0.0.1:900012: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/mysql-clickhouse-replication

总结

目前线上报表业务都已经在使用 clickhouse，数据同步采用自行开发的同步程序进行同步。目前数据一致性没有什么问题。当然同步的表需要有自增主键，否则有些情况比较难处理。延时也比较小。数据的延时以及数据的一致性都有监控。

总体来说使用 clickhouse 处理 olap 还是非常不错的选择，小伙伴们可以尝试。

参考资料

1. https://clickhouse-driver.readthedocs.io/en/latest/

2. https://python-mysql-replication.readthedocs.io/en/latest/examples.html

3. https://clickhouse.yandex/docs/en/

4. https://github.com/sysown/proxysql/wiki/ClickHouse-Support

关于 ClickHouse 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！