ClickHouse資料庫單機安裝及備份恢復

chenoracle 發表於 2022-11-24
資料庫

二進位制安裝

下載安裝介質:

上傳安裝介質

[[email protected] 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值

[[email protected] 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

檢視程式

[[email protected] ~]# 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

登入

[[email protected] 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

檢視資料

[[email protected] 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;

最後看看建立的表在磁碟上是如何儲存的:

[[email protected] 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/
[[email protected] 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
[[email protected] 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

檢視錶列資訊

[[email protected] 1669269521_12_12_0]$ cat columns.txt 
columns format version: 1
2 columns:
`id` Int8
`time` DateTime



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2925028/,如需轉載,請註明出處,否則將追究法律責任。

相關文章