二进制安装
下载安装介质:
https://packages.clickhouse.com/tgz/lts
上传安装介质
[root@cjcos02 ck]# ls -lrth clickhouse-* -rw-r--r-- 1 root root 39K Nov 24 10:36 clickhouse-client-22.3.2.2.tgz -rw-r--r-- 1 root root 172M Nov 24 10:36 clickhouse-common-static-22.3.2.2.tgz -rw-r--r-- 1 root root 61K Nov 24 10:36 clickhouse-server-22.3.2.2.tgz -rw-r--r-- 1 root root 739M Nov 24 10:37 clickhouse-common-static-dbg-22.3.2.2.tgz
检查md5值
[root@cjcos02 ck]# md5sum clickhouse-* 5a80d3ceaabc64b00ed0d6edcecd5d7d clickhouse-client-22.3.2.2.tgz 0eb800b1d2e6c60ae9b4cc3400b92794 clickhouse-common-static-22.3.2.2.tgz 36be6c1ec92be9297b9b010aeb2c11de clickhouse-common-static-dbg-22.3.2.2.tgz 718154866367e708e0b88d7b1ba109a6 clickhouse-server-22.3.2.2.tgz
创建用户、组
groupadd -g 2000 clickhouse useradd -u 2000 -g clickhouse clickhouse passwd clickhouse
创建目录
mkdir /clickhouse/9000/{app,data,log,soft,back,scripts,conf} -p
cp clickhouse-*.tgz /clickhouse/9000/app/
解压
cd /clickhouse/9000/app tar -zxvf clickhouse-common-static-22.3.2.2.tgz tar -zxvf clickhouse-common-static-dbg-22.3.2.2.tgz tar -zxvf clickhouse-server-22.3.2.2.tgz tar -zxvf clickhouse-client-22.3.2.2.tgz mv *.tgz ../soft/
重命名
mv clickhouse-common-static-22.3.2.2 clickhouse-common-static mv clickhouse-common-static-dbg-22.3.2.2 clickhouse-common-static-dbg mv clickhouse-server-22.3.2.2 clickhouse-server mv clickhouse-client-22.3.2.2 clickhouse-client
授权
chown clickhouse.clickhouse /clickhouse -R
修改默认配置文件
安装路径
su - clickhouse cd /clickhouse/9000/app/clickhouse-server/install cp doinst.sh doinst.sh_20221124bak vi doinst.sh
原文：
CLICKHOUSE_CONFDIR=${CLICKHOUSE_CONFDIR:=/etc/clickhouse-server}
CLICKHOUSE_DATADIR=${CLICKHOUSE_DATADIR:=/var/lib/clickhouse}
CLICKHOUSE_LOGDIR=${CLICKHOUSE_LOGDIR:=/var/log/clickhouse-server}
CLICKHOUSE_BINDIR=${CLICKHOUSE_BINDIR:=/usr/bin}
CLICKHOUSE_GENERIC_PROGRAM=${CLICKHOUSE_GENERIC_PROGRAM:=clickhouse}
EXTRACT_FROM_CONFIG=${CLICKHOUSE_GENERIC_PROGRAM}-extract-from-config
CLICKHOUSE_CONFIG=$CLICKHOUSE_CONFDIR/config.xml
CLICKHOUSE_PIDDIR=/var/run/$PROGRAM
修改后：
CLICKHOUSE_CONFDIR=${CLICKHOUSE_CONFDIR:=/clickhouse/9000/app/clickhouse-server/etc/clickhouse-server}
CLICKHOUSE_DATADIR=${CLICKHOUSE_DATADIR:=/clickhouse/9000/data}
CLICKHOUSE_LOGDIR=${CLICKHOUSE_LOGDIR:=/clickhouse/9000/log}
CLICKHOUSE_BINDIR=${CLICKHOUSE_BINDIR:=/clickhouse/9000/app/clickhouse-common-static/usr/bin}
CLICKHOUSE_GENERIC_PROGRAM=${CLICKHOUSE_GENERIC_PROGRAM:=clickhouse}
EXTRACT_FROM_CONFIG=${CLICKHOUSE_GENERIC_PROGRAM}-extract-from-config
CLICKHOUSE_CONFIG=$CLICKHOUSE_CONFDIR/config.xml
CLICKHOUSE_PIDDIR=/clickhouse/9000/pid/$PROGRAM
日志路径,数据路径，listen_host限制
cd /clickhouse/9000/app/clickhouse-server/etc/clickhouse-server cp config.xml config.xml_20221124bak vi config.xml
---1 修改日志路径
原文
/var/log/clickhouse-server/clickhouse-server.log /var/log/clickhouse-server/clickhouse-server.err.log
修改后
/clickhouse/9000/logclickhouse-server.log /clickhouse/9000/logclickhouse-server.err.log
---2 修改数据路径
原参数
/var/lib/clickhouse/ /var/lib/clickhouse/tmp/ /var/lib/clickhouse/user_files/ /etc/clickhouse-server/server.crt /etc/clickhouse-server/server.key /var/lib/clickhouse/user_files/ /var/lib/clickhouse/access/ /clickhouse/task_queue/ddl /var/lib/clickhouse/format_schemas/
修改后
/clickhouse/9000/data/ /clickhouse/9000/tmp/ /clickhouse/9000/user_files/ /clickhouse/9000/access/ /clickhouse/9000/crt/server.crt /clickhouse/9000/crt/server.key /clickhouse/9000/task_queue/ddl /clickhouse/9000/format_schemas/
---3 修改host限制
原参数
修改后参数
::
---4 修改时区
原参数
修改后参数
Asia/Shanghai
修改user参数
cp users.xml users.xml_20221124bak vi users.xml
---1 默认可创建其他用户和授权
原参数
修改后参数
执行安装
su - root sh /clickhouse/9000/app/clickhouse-common-static/install/doinst.sh sh /clickhouse/9000/app/clickhouse-common-static-dbg/install/doinst.sh sh /clickhouse/9000/app/clickhouse-server/install/doinst.sh sh /clickhouse/9000/app/clickhouse-client/install/doinst.sh
启动
systemctl status clickhouse-server.service systemctl start clickhouse-server.service
查看进程
[root@cjcos02 ~]# ps -ef|grep clickhouse|grep -v grep root 12733 2103 0 12:47 pts/0 00:00:00 su - clickhouse clickho+ 13771 1 0 12:54 ? 00:00:00 clickhouse-watchdog --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid clickho+ 13775 13771 3 12:54 ? 00:03:14 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
登录
[clickhouse@cjcos02 9000]$ clickhouse-client ClickHouse client version 22.3.2.1. Connecting to localhost:9000 as user default. Connected to ClickHouse server version 22.3.2 revision 54455. cjcos02 :) show databases; SHOW DATABASES Query id: 762a3a43-4efc-48df-a8c2-f91bc588f359 ┌─name───────────────┐ │ INFORMATION_SCHEMA │ │ default │ │ information_schema │ │ system │ └────────────────────┘ 4 rows in set. Elapsed: 0.002 sec.
创建数据库
CREATE DATABASE cjc Query id: 1db91404-1a18-4a61-9e04-3ed9946ec976 Ok.
建表
cjcos02 :) use cjc
1.创建内存表，重启后数据丢失。
注意engine和字段类型区分大小写
CREATE TABLE t1 (id Int8,name String) engine=Memory;
插入数据
cjcos02 :) insert into t1 values(1,'cjc');
查询
cjcos02 :) select * from t1 SELECT * FROM t1 Query id: e5a8a4a8-12ff-43f7-b3d9-16a4dbc2bf0c ┌─id─┬─name─┐ │ 1 │ cjc │ └────┴──────┘ 1 rows in set. Elapsed: 0.002 sec.
2.创建MergeTree引擎表,其中PARTITION和ORDER是必填项
CREATE TABLE t2 (id Int8,time Datetime) engine=MergeTree() PARTITION BY time ORDER BY id;
插入数据
insert into t2 values(1,now());
insert into t2 values(2,now());
insert into t2 values(3,now());
查询
cjcos02 :) select * from t2; SELECT * FROM t2 Query id: ba420943-9261-45c8-ae5f-c0c8760879c7 ┌─id─┬────────────────time─┐ │ 1 │ 2022-11-24 13:19:37 │ └────┴─────────────────────┘ ┌─id─┬────────────────time─┐ │ 2 │ 2022-11-24 13:19:44 │ └────┴─────────────────────┘ ┌─id─┬────────────────time─┐ │ 3 │ 2022-11-24 13:19:49 │ └────┴─────────────────────┘ 3 rows in set. Elapsed: 0.007 sec.
查看集群信息
cjcos02 :) select * from system.clusters; SELECT * FROM system.clusters Query id: 01c76b60-1dcd-4c5d-9352-af1a482fee13 ┌─cluster─────────────────────────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─slowdowns_count─┬─estimated_recovery_time─┐ │ test_cluster_one_shard_three_replicas_localhost │ 1 │ 1 │ 1 │ 127.0.0.1 │ 127.0.0.1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_one_shard_three_replicas_localhost │ 1 │ 1 │ 2 │ 127.0.0.2 │ 127.0.0.2 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_one_shard_three_replicas_localhost │ 1 │ 1 │ 3 │ 127.0.0.3 │ 127.0.0.3 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards │ 1 │ 1 │ 1 │ 127.0.0.1 │ 127.0.0.1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards │ 2 │ 1 │ 1 │ 127.0.0.2 │ 127.0.0.2 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards_internal_replication │ 1 │ 1 │ 1 │ 127.0.0.1 │ 127.0.0.1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards_internal_replication │ 2 │ 1 │ 1 │ 127.0.0.2 │ 127.0.0.2 │ 9000 │ 0 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards_localhost │ 1 │ 1 │ 1 │ localhost │ ::1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_cluster_two_shards_localhost │ 2 │ 1 │ 1 │ localhost │ ::1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_shard_localhost │ 1 │ 1 │ 1 │ localhost │ ::1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_shard_localhost_secure │ 1 │ 1 │ 1 │ localhost │ ::1 │ 9440 │ 0 │ default │ │ 0 │ 0 │ 0 │ │ test_unavailable_shard │ 1 │ 1 │ 1 │ localhost │ ::1 │ 9000 │ 1 │ default │ │ 0 │ 0 │ 0 │ │ test_unavailable_shard │ 2 │ 1 │ 1 │ localhost │ ::1 │ 1 │ 0 │ default │ │ 0 │ 0 │ 0 │ └─────────────────────────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────┴─────────────────────────┘ 13 rows in set. Elapsed: 0.002 sec.
数据库备份和恢复
一：导出备份和恢复
适用于：数据量小的表
备份
clickhouse-client --query="select * from cjc.t2" > /clickhouse/9000/back/t2_20221124.tsv
查看数据
[clickhouse@cjcos02 back]$ cat t2_20221124.tsv
1 2022-11-24 13:53:49
2 2022-11-24 13:58:39
3 2022-11-24 13:58:41
模拟误删除
cjcos02 :) truncate table t2;
恢复
cat /clickhouse/9000/back/t2_20221124.tsv | clickhouse-client --query "INSERT INTO cjc.t2 FORMAT TSV"
查看恢复后的数据
cjcos02 :) select * from cjc.t2; SELECT * FROM cjc.t2 Query id: 4dd29dbf-ca7d-44f4-85ce-52a691f751e7 ┌─id─┬────────────────time─┐ │ 1 │ 2022-11-24 13:53:49 │ └────┴─────────────────────┘ ┌─id─┬────────────────time─┐ │ 2 │ 2022-11-24 13:58:39 │ └────┴─────────────────────┘ ┌─id─┬────────────────time─┐ │ 3 │ 2022-11-24 13:58:41 │ └────┴─────────────────────┘ 3 rows in set. Elapsed: 0.004 sec.
二：快照表备份和恢复
CREATE TABLE cjc.t2_20221124bak AS cjc.t2;
INSERT INTO TABLE cjc.t2_20221124bak SELECT * FROM cjc.t2;
模拟误删除
truncate table t2;
恢复
INSERT INTO TABLE cjc.t2 SELECT * FROM cjc.t2_20221124bak;
最后看看创建的表在磁盘上是如何存储的：
[clickhouse@cjcos02 cjc]$ ls -lrth total 0 lrwxrwxrwx 1 clickhouse clickhouse 69 Nov 24 13:18 t2 -> /clickhouse/9000/data/store/9de/9de2608a-908e-4b06-8d9a-24404f11ffbb/ lrwxrwxrwx 1 clickhouse clickhouse 69 Nov 24 14:10 t2_20221124bak -> /clickhouse/9000/data/store/5c2/5c20ebfb-4ace-4506-a5d3-712b430f64cb/ [clickhouse@cjcos02 cjc]$ ls -lrth /clickhouse/9000/data/store/9de/9de2608a-908e-4b06-8d9a-24404f11ffbb/ total 16K -rw-r----- 1 clickhouse clickhouse 1 Nov 24 13:18 format_version.txt drwxr-x--- 2 clickhouse clickhouse 6 Nov 24 13:18 detached drwxr-x--- 2 clickhouse clickhouse 4.0K Nov 24 14:11 1669269521_12_12_0 drwxr-x--- 2 clickhouse clickhouse 4.0K Nov 24 14:11 1669269519_11_11_0 drwxr-x--- 2 clickhouse clickhouse 4.0K Nov 24 14:11 1669269229_10_10_0 [clickhouse@cjcos02 cjc]$ ls -lrth /clickhouse/9000/data/store/9de/9de2608a-908e-4b06-8d9a-24404f11ffbb/1669269521_12_12_0/ total 36K -rw-r----- 1 clickhouse clickhouse 2 Nov 24 14:11 primary.idx -rw-r----- 1 clickhouse clickhouse 4 Nov 24 14:11 partition.dat -rw-r----- 1 clickhouse clickhouse 8 Nov 24 14:11 minmax_time.idx -rw-r----- 1 clickhouse clickhouse 10 Nov 24 14:11 default_compression_codec.txt -rw-r----- 1 clickhouse clickhouse 80 Nov 24 14:11 data.mrk3 -rw-r----- 1 clickhouse clickhouse 57 Nov 24 14:11 data.bin -rw-r----- 1 clickhouse clickhouse 1 Nov 24 14:11 count.txt -rw-r----- 1 clickhouse clickhouse 63 Nov 24 14:11 columns.txt -rw-r----- 1 clickhouse clickhouse 251 Nov 24 14:11 checksums.txt
查看表列信息
[clickhouse@cjcos02 1669269521_12_12_0]$ cat columns.txt columns format version: 1 2 columns: `id` Int8 `time` DateTime