clickhouse-backup使用

# cllichouse-backup命令

我这里部署方式是使用docker-compose 运行的挂载目录

创建数据库软链接,元数据

ln -s /data/clickhouse/clickhouse_server/clickhouse_database/ /var/lib/clickhouse

clickhouse-backup 版本：V1.2.2

#下载
wget -c https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.2.2/clickhouse-backup.tar.gz

#解压
tar xf clickhouse-backup.tar.gz -C /data

#拷贝到全局命令：
cp /data/clickhouse-backup/clickhouse-backup /usr/local/bin

1
2
3
4
5
6
7
8
9

curl -s -o ck-bakcup.tar http://download.yfklife.cn/blog/dba/clickhouse/clickhouse-backup.tar && tar xf ck-bakcup.tar -C /usr/local/bin/

修改连接配置

生成默认的配置：clickhouse-backup default-config

cd /data/clickhouse-backup/

[root@yfk clickhouse-backup]# cat config.yml

general:
  remote_storage: none
  max_file_size: 107374182400
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 0
  log_level: info
  allow_empty_backups: false
  download_concurrency: 1
  upload_concurrency: 1
clickhouse:
  username: default 
  password: ""	#默认空密码
  host: 192.168.5.112 #clickhouse IP地址
  port: 9002 #clickhouse 连接端口
  data_path: "/data/clickhouse/clickhouse_server/clickhouse_database/" #数据存放目录
  path: "./"
  disk_mapping: {}
  skip_tables:
  - system.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: false
  config_dir: /data/clickhouse/clickhouse_server/clickhouse-server/ #config.xml 配置目录
  restart_command: docker restart dm_clickhouse_server #好像没有什么作用
  debug: false

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

[root@yfk clickhouse_server]# pwd
/data/clickhouse/clickhouse_server/clickhouse_server
[root@yfk clickhouse_server]# 
[root@yfk clickhouse_server]# tree -FL 2
.
├── clickhouse_database/
│   ├── access/
│   ├── backup/
│   ├── data/
│   ├── dictionaries_lib/
│   ├── flags/
│   ├── format_schemas/
│   ├── metadata/
│   ├── metadata_dropped/
│   ├── preprocessed_configs/
│   ├── shadow/
│   ├── status
│   ├── store/
│   ├── tmp/
│   └── user_files/
├── clickhouse_log/
│   ├── clickhouse-server.err.log
│   └── clickhouse-server.log
└── clickhouse_server/
    ├── config.d/
    ├── config.xml
    ├── users.d/
    └── users.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# clickhouse-backup备份与恢复

说明：在我这里只有一个dispatcher库，和三个表

备份存放路径：/var/lib/clickhouse/backup

这个备份好像不支持远端备份到本地，只支持本地备份到远端，也可能是我没有找到方法

注意!!! 提前创建软链接

ln -sf /data/clickhouse/clickhouse_server/clickhouse_database/ /var/lib/clickhouse

如果备份出来的数据目录没有shadow目录，检查 /var/lib/clickhouse 软链接

# 备份

备份脚本

#!/bin/bash

Date=$(date +%F-%H-%M)

/usr/bin/clickhouse-backup -c /etc/clickhouse-backup/config.yml  create clickhouse-backup-$Date

cd /data/clickhouse/clickhouse_server/clickhouse_database/backup

tar zcf clickhouse-backup-$Date.tar  clickhouse-backup-$Date && rm -rf  clickhouse-backup-$Date

echo 'Clickhouse backup complete'

find /data/clickhouse/clickhouse_server/clickhouse_database/backup   -name "clickhouse-backup*" -mtime +7 -delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14

clickhouse-backup -c config.yml create

clickhouse-backup -c config.yml create test-1 #自定义备份目录名

2.查看备份情况

命令查看

[root@yfk clickhouse-backup]# clickhouse-backup -c config.yml list

2022-01-07T01-51-16   466.50KiB   07/01/2022 01:51:16   local      
2022-01-07T02-00-25   466.50KiB   07/01/2022 01:53:16   local   
test-1                466.50KiB   07/01/2022 01:55:16   local

1
2
3
4
5

查看备份目录

[root@yfk clickhouse-backup]# ll /var/lib/clickhouse/backup/
total 0
drwxr-x--- 4 101 101 57 Jan  7 09:51 2022-01-07T01-51-16
drwxr-x--- 4 101 101 57 Jan  7 09:53 2022-01-07T02-00-25 
drwxr-x--- 4 101 101 57 Jan  7 09:55 test-1

1
2
3
4
5

查看备份出来的目录结构（metadata，shadow）

[root@yfk clickhouse-backup]# tree -FL 3 /var/lib/clickhouse/backup/2022-01-07T02-00-25/
/var/lib/clickhouse/backup/2022-01-07T02-00-25/
├── metadata/
│   └── dispatcher/
│       ├── gnss.json
│       ├── healthcheck.json
│       └── manage_state.json
├── metadata.json
└── shadow/
    └── dispatcher/
        ├── gnss/
        └── manage_state/

6 directories, 4 files

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 恢复

1.恢复整个备份目录

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16

只恢复表结构，（恢复表结构只能执行一次，第二次会报已经存在，就算是把表删除了，这次就要换一个备份的列表再次执行恢复表）

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16 --schema

只恢复数据

当表已经存在，只恢复数据，（重复执行，会再次插入一倍的数据）

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16 --data

2.恢复dispatcher.gnss 单个表

恢复指定库参数：--table=dbname.*

恢复指定表

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16 --table=dispatcher.gnss

恢复单个表结构

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16 --table=dispatcher.gnss --schema

恢复单个表数据，（重复执行，会再次插入一倍的数据）

clickhouse-backup -c config.yml restore 2022-01-07T01-51-16 --table=dispatcher.gnss --data

# 还原之前修改库名

备份库名为："person_position",还原库名为："person_position2"

cd /var/lib/clickhouse/backup/2023-12-28T06-25-00

mv metadata/person_position metadata/person_position2
mv shadow/person_position shadow/person_position2
grep -rl person_position ./* |xargs sed -i 's#person_position#person_position2#g'


查看备份数据
clickhouse-backup -c config.yml list
还原备份数据
clickhouse-backup -c config.yml restore 2023-12-28T06-25-00

#注意：如果之前已经导入过一次需要修改创表语句的UUID "metadata/库名/表名.json"

1
2
3
4
5
6
7
8
9
10
11
12
13

# 恢复报错

warn can't create table 'person_position2.tbl_station': code: 57, message: Mapping for table with UUID=5cd18806-8949-44eb-9cd1-88068949c4eb already exists. It happened due to UUID collision, most likely because some not random UUIDs were manually specified in CREATE queries., will try again backup=2023-12-28T06-25-00 operation=restore

修改 2023-12-28T06-25-00/metadata/person_position2 下所有的创表语句修改 UUID的值，随便修改一位字符

如果此文章对您有帮助，点击 -->> 请博主喝咖啡

上次更新: 2025/01/23, 10:34:52

← clickhouse安装配置 clickhouse数据维护→