ClickHouse数据库单机安装及备份恢复
二进制安装
下载安装介质:
上传安装介质
|
|
[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 修改日志路径
原文
|
|
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
|
修改后
|
|
<log>/clickhouse/9000/logclickhouse-server.log</log>
<errorlog>/clickhouse/9000/logclickhouse-server.err.log</errorlog>
|
---2 修改数据路径
原参数
|
|
<path>/var/lib/clickhouse/</path>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
<privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<path>/var/lib/clickhouse/access/</path>
<path>/clickhouse/task_queue/ddl</path>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>
|
修改后
|
<path>/clickhouse/9000/data/</path>
<tmp_path>/clickhouse/9000/tmp/</tmp_path>
<user_files_path>/clickhouse/9000/user_files/</user_files_path>
<path>/clickhouse/9000/access/</path>
<certificateFile>/clickhouse/9000/crt/server.crt</certificateFile>
<privateKeyFile>/clickhouse/9000/crt/server.key</privateKeyFile>
<path>/clickhouse/9000/task_queue/ddl</path>
<format_schema_path>/clickhouse/9000/format_schemas/</format_schema_path>
|
---3 修改host限制
原参数
|
<!-- <listen_host>::</listen_host> -->
|
修改后参数
|
<listen_host>::</listen_host>
|
---4 修改时区
原参数
|
<!-- <timezone>UTC</timezone> -->
|
修改后参数
|
<timezone>Asia/Shanghai</timezone>
|
修改user参数
|
cp users.xml users.xml_20221124bak
vi users.xml
|
---1 默认可创建其他用户和授权
原参数
|
<!-- <access_management>1</access_management> -->
|
修改后参数
|
<!-- <access_management>1</access_management> -->
|
执行安装
|
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
|
时间:2022-12-02 21:34 来源:未知 转发量:次
声明：本站部分作品是由网友自主投稿和发布、编辑整理上传，对此类作品本站仅提供交流平台，转载的目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，不为其版权负责。如果您发现网站上有侵犯您的知识产权的作品，请与我们取得联系，我们会及时修改或删除。
相关文章：
相关推荐：
网友评论：
最新文章
热门文章
