clickhouse对data下的具体表目录恢复

环境:

OS:Centos 7

DB:21.6.5

单机版本的db

 

1.停掉数据库
systemctl stop clickhouse-server.service

 

2.重新初始化一个新的db
进入到data目录
[root@localhost clickhouse]#cd /home/middle/clickhouse
[root@localhost clickhouse]#mv data bakdata
[root@localhost clickhouse]#mkdir data

 

3.启动数据库
[root@localhost clickhouse]#systemctl start clickhouse-server.service

4.创建库和表
[root@localhost clickhouse]#clickhouse-client -h 192.168.1.77 --port=19000 -m -u default

create database db_sentinel;

CREATE TABLE db_sentinel.metric
(
`app` String,
`block_qps` Int64,
`count` Int64,
`exception_qps` Int64,
`gmt_create` DateTime DEFAULT now(),
`gmt_modified` DateTime DEFAULT now(),
`id` Int64,
`machine_ip` Nullable(String),
`pass_qps` Int64,
`resource` String,
`resource_code` Int64,
`rt` Float64,
`success_qps` Int64,
`timestamp` DateTime DEFAULT now(),
`month` Date DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(month)
ORDER BY (timestamp, app, resource)
SETTINGS index_granularity = 8192;

 

5.找到就库和新库该表指向的存储文件
旧库
[root@]# cd /home/middle/clickhouse/bakdata/data/db_sentinel
[root@db_sentinel]# ls -al
lrwxrwxrwx 1 clickhouse clickhouse 75 Dec 7 2021 metric -> /home/middle/clickhouse/data/store/d9f/d9f4eb20-5e1e-42c5-99f4-eb205e1e12c5

注意这里上面的步骤我们已经进行了目录改名,就库该表指向的真正路径是:
/home/middle/clickhouse/data/store/d9f/d9f4eb20-5e1e-42c5-99f4-eb205e1e12c5

新库
[root@]# cd /home/middle/clickhouse/data/data/db_sentinel
[root@db_sentinel]#ls -al
lrwxrwxrwx 1 clickhouse clickhouse 75 Nov 28 10:15 metric -> /home/middle/clickhouse/data/store/a23/a2365717-0f7d-445d-a236-57170f7d345d

 

6.文件拷贝
##停掉数据库
[root@localhost]#systemctl stop clickhouse-server.service
##进入到新库的表的存储目录
[root@localhost]#cd /home/middle/clickhouse/data/store/a23
##用于备份,万一失败可以mv回来
[root@localhost]#mv a2365717-0f7d-445d-a236-57170f7d345d bak_a2365717-0f7d-445d-a236-57170f7d345d
##新建一个空的目录
[root@localhost]#mkdir a2365717-0f7d-445d-a236-57170f7d345d
##拷贝备库目录的表对于的文件到新库的表目录,注意这里需要带/*考虑
[root@localhost]#cp -rp /home/middle/clickhouse/bakdata/store/d9f/d9f4eb20-5e1e-42c5-99f4-eb205e1e12c5/* /home/middle/clickhouse/data/store/a23/a2365717-0f7d-445d-a236-57170f7d345d/

##修改权限
[root@localhost]#cd /home/middle/clickhouse/data/store/a23
[root@localhost]#chown -R clickhouse:clickhouse ./a2365717-0f7d-445d-a236-57170f7d345d

##启动数据库
[root@localhost]#systemctl start clickhouse-server.service

 

7.验证
查询验证
[root@localhost]#clickhouse-client -h 192.168.1.77 --port=19000 -m -u default
select formatDateTime(month,'%Y%m'),count(1) from metric group by formatDateTime(month,'%Y%m');