docker下的clickhouse备份恢复(本地恢复和异地恢复)

环境
OS:Centos 7
clickhouse:24.8 容器部署
clickhouse backup:2.6.69 容器部署

说明:
1.docker部署的clickhouse,若进行备份的话需要使用的clickhouse-backup，也需要使用容器的部署方式，否则备份出来的文件无法进行恢复;

2.clickhouse容器需要将数据目录data外挂到宿主机;

3.宿主机需要安装clickhouse-backup-2.6.39-1.x86_64.rpm,准备好配置文件/etc/clickhouse-backup/config.yml 

 

#####################################本地备份恢复##############################

1.登录clickhouse docker容器创建表并写入数据

clickhouse-client -h 127.0.0.1 -m -u clickhouse --password clickhouse --port=9000
use default;
CREATE TABLE tb_test_docker
(
   id Int32,
   name String,
   timestamp DateTime DEFAULT now(),
   day date DEFAULT now()
)
engine = MergeTree
partition by day
order by id
settings index_granularity = 8192;


insert into tb_test_docker(id,name) values(1,'name1');
insert into tb_test_docker(id,name) values(2,'name2');
insert into tb_test_docker(id,name) values(3,'name3');
insert into tb_test_docker(id,name) values(4,'name4');
insert into tb_test_docker(id,name) values(5,'name5');
insert into tb_test_docker(id,name) values(6,'name6');
insert into tb_test_docker(id,name) values(7,'name7');
insert into tb_test_docker(id,name) values(8,'name8');
insert into tb_test_docker(id,name) values(9,'name9');
insert into tb_test_docker(id,name) values(10,'name10');

 

2.备份
这里登录到clickhouse-backup容器
[root@host135 clickhouse-backup]#docker run --rm -it --network host -v "/home/middle/langfuse/clickhouse/data:/var/lib/clickhouse" -v "/etc/clickhouse-backup:/etc/clickhouse-backup" registry.cn-shenzhen.aliyuncs.com/hxlk8s/clickhouse-backup:2.6.39 bash

参数说明

-v "/home/middle/langfuse/clickhouse/data:/var/lib/clickhouse"  ##数据目录映射到容器内部

-v "/etc/clickhouse-backup:/etc/clickhouse-backup" ##配置文件目录映射到容器内部

 

进行备份(这里已经进入到容器内部执行)
host135:/# clickhouse-backup create hxl_docker_bak20251016 --config=/etc/clickhouse-backup/config.yml

 

3.查看备份

#docker run --rm -it --network host -v "/home/middle/langfuse/clickhouse/data:/var/lib/clickhouse" -v "/etc/clickhouse-backup:/etc/clickhouse-backup" registry.cn-shenzhen.aliyuncs.com/hxlk8s/clickhouse-backup:2.6.39 bash

host135:/# clickhouse-backup -v
Version:         2.6.39
Git Commit:      a94304a1549a38b05c32a3ba7665a285f3aba0de
Build Date:      2025-09-26

host135:/# clickhouse-backup list --config=/etc/clickhouse-backup/config.yml
hxl_docker_bak20251016   2025-10-16 01:24:12   local      all:30.99KiB,data:3.08KiB,arch:0B,obj:0B,meta:27.91KiB,rbac:0B,conf:0B,nc:0B   regular

 

4.模拟删除刚才创建的表
[root@host135 ~]#clickhouse-client -h 127.0.0.1 -m -u clickhouse --password clickhouse --port=9000
use default;
drop table tb_test_docker;

 

5.恢复

#docker run --rm -it --network host -v "/home/middle/langfuse/clickhouse/data:/var/lib/clickhouse" -v "/etc/clickhouse-backup:/etc/clickhouse-backup" registry.cn-shenzhen.aliyuncs.com/hxlk8s/clickhouse-backup:2.6.39 bash


host135:/#clickhouse-backup restore hxl_docker_bak20251016 -table default.tb_test_docker --config=/etc/clickhouse-backup/config.yml

2025-10-16 01:51:03.287 INF pkg/clickhouse/clickhouse.go:334 > clickhouse connection closed
2025-10-16 01:51:03.300 FTL cmd/clickhouse-backup/main.go:823 > error="can't create table `default`.`tb_test_docker`: code: 57, message: Directory for table data store/47a/47a211d9-244b-403d-9927-e70a979b5e5d/ already exists after 1 times, please check your schema dependencies"

 

解决办法:
去掉元数据里面的uuid栏目

vi /home/middle/langfuse/clickhouse/data/backup/hxl_docker_bak20251016/metadata/default/tb_test_docker.json
{
 "table": "tb_test_docker",
 "database": "default",
 "uuid": "47a211d9-244b-403d-9927-e70a979b5e5d",
 "parts": {
  "default": [
   {
    "name": "20251016_1_10_2"
   }
  ]
 },
 "query": "CREATE TABLE default.tb_test_docker UUID '47a211d9-244b-403d-9927-e70a979b5e5d' (`id` Int32, `name` String, `timestamp` Da
teTime DEFAULT now(), `day` Date DEFAULT now()) ENGINE = MergeTree PARTITION BY day ORDER BY id SETTINGS index_granularity = 8192",
 "size": {
  "default": 1042
 },
 "total_bytes": 612,
 "metadata_only": false,
 "checksums": {
  "20251016_1_10_2": 11210086699896294024
 }
}

重新执行恢复

 

#######################异地恢复################################

我这里的目的clickhouse不是使用docker部署的,恢复的话就不需要clickhouse backup容器了，直接rpm安装即可

1.目的主机安装clickhouse backup并准备好配置文件 /etc/clickhouse-backup/config.yml
确保clickhouse backup能够链接到服务器

 

2.源宿主机备份目录打包传到目的机器

[root@host135 ]# cd /home/middle/langfuse/clickhouse/data/backup
[root@host135 ]#tar -cvf hxl_docker_bak20251016.tar ./hxl_docker_bak20251016
[root@host135 backup]# scp hxl_docker_bak20251016.tar root@192.168.1.134:/tmp/ckbak/

 

3.目的机器恢复
我这里的目的clickhouse是安装在宿主机上的
将上面步骤拷贝过来的包解压
[root@localhost ckbak]# cd /tmp/ckbak
[root@localhost ckbak]# tar -xvf hxl_docker_bak20251016.tar

将备份目录拷贝到宿主机的备份目录
mv hxl_docker_bak20251016 /var/lib/clickhouse/backup/

 

4.修改权限
[root@localhost clickhouse]# cd /var/lib/clickhouse
[root@localhost clickhouse]# chown -R clickhouse:clickhouse ./backup

 

5.查看备份

[root@localhost clickhouse]# clickhouse-backup list
hxl_docker_bak20251016   2025-10-16 09:24:12   local      all:30.99KiB,data:3.08KiB,arch:0B,obj:0B,meta:27.91KiB,rbac:0B,conf:0B,nc:0B   regular

 

6.恢复

[root@localhost clickhouse]#clickhouse-backup restore hxl_docker_bak20251016 -table default.tb_test_docker

 

 

 

#################################备份脚本##############################

[root@localhost scripts]# more run_backup_clickhouse_for_docker.sh 
#!/bin/bash

backup_date=`date "+%Y%m%d"`

ckbak_log=/scripts/logs/ckbak_${backup_date}.log
/scripts/backup_clickhouse_for_docker.sh>${ckbak_log} 2>&1

 

[root@localhost scripts]# more backup_clickhouse_for_docker.sh 
#!/bin/bash
backup_date=`date "+%Y%m%d"`
delete_date=`date +%Y%m%d -d "3 days ago"`

docker run --rm -i --network host -v "/home/middle/langfuse/clickhouse/data:/var/lib/clickhouse" -v "/etc/clickhouse-backup:/etc/clic
khouse-backup" clickhouse-backup:2.6.39 bash <<EOF
clickhouse-backup create ckbak_${backup_date} --config=/etc/clickhouse-backup/config.yml
clickhouse-backup delete local ckbak_${delete_date} --config=/etc/clickhouse-backup/config.yml
exit
EOF