clickhouse-backup 备份恢复测试

毋玺

2023-12-01

一、安装配置

1. 下载

https://github.com/AlexAkulov/clickhouse-backup/releases

2. 安装

[root@backup-db ~]# rpm -ivh clickhouse-backup-1.3.1-1.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:clickhouse-backup-1.3.1-1 ################################# [100%]

[root@backup-db ~]# clickhouse-backup -v
Version: 1.3.1
Git Commit: 0b1a97a6a2610fa59b58d86c139b67b0ce62e420
Build Date: 2022-02-17

3. 编辑配置文件

[root@backup-db ~]# cd /etc/clickhouse-backup/
[root@backup-db clickhouse-backup]# ls
config.yml.example
[root@backup-db clickhouse-backup]# cp config.yml.example config.yml

vi /etc/clickhouse-backup/config.yml

# 改一些主要配置
backups_to_keep_local: 3
clickhouse:
username: default
password: "xxxx"

二、备份测试

1. 查看帮助

[root@backup-db clickhouse-backup]# clickhouse-backup create -help
NAME:
clickhouse-backup create - Create new backup

USAGE:
clickhouse-backup create [-t, --tables=<db>.<table>] [--partitions=<partition_names>] [-s, --schema] [--rbac] [--configs] <backup_name>

DESCRIPTION:
Create new backup

OPTIONS:
--config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG]
--table value, --tables value, -t value table name patterns, separated by comma, allow ? and * as wildcard
--partitions value partition names, separated by comma
--schema, -s Backup schemas only
--rbac, --backup-rbac, --do-backup-rbac Backup RBAC related objects only
--configs, --backup-configs, --do-backup-configs Backup ClickHouse server configuration files only

2. 备份所有库

clickhouse-backup create

备份存储在 $data_path/backup 下，备份名称默认为时间戳，可手动指定备份名称。例如：

clickhouse-backup create ch_bk_20220311

[root@backup-db hydb]# clickhouse-backup create ch_bk_20220311
2022/03/11 15:59:51.905675 info done backup=ch_bk_20220311 operation=create table=hydb.events_local
2022/03/11 15:59:51.928635 info done backup=ch_bk_20220311 operation=create table=hydb.events_local01
...
2022/03/11 15:59:53.212917 info done backup=ch_bk_20220311 operation=create table=hydb.events_local07
2022/03/11 15:59:53.213019 info done backup=ch_bk_20220311 operation=create table=hydb.events_local08
2022/03/11 15:59:53.216185 info done backup=ch_bk_20220311 duration=1.565s operation=create

备份后目录结构

[root@backup-db clickhouse-backup]# cd /data/prd/clickhouse/21.8.14/ch9000/data/
backup/ dictionaries_lib/ metadata/ preprocessed_configs/ store/
data/ flags/ metadata_dropped/ status

备份包含两个目录：

metadata：元数据信息（DDL SQL）
shadow：数据目录

[root@backup-db backup]# cd ch_bk_20220311/
[root@backup-db ch_bk_20220311]# ls
metadata metadata.json shadow

元数据

[root@backup-db ch_bk_20220311]# cd metadata
[root@backup-db metadata]# ls
data_db hydb
[root@backup-db metadata]# cd hydb/
[root@backup-db hydb]# ls
events_local.json tanktest.json

[root@backup-db hydb]# less events_local.json
{
"table": "events_local",
"database": "hydb",
"parts": {
"default": [
{
"name": "20220311_0_2_1"
}
]
},
"query": "CREATE TABLE hydb.events_local UUID '38bcc1d0-0bda-4a49-b8bc-c1d00bdada49' (`ts_date` Date, `user_id` Int64) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_local', '{replica}') PARTITION BY ts_date ORDER BY ts_date SETTINGS index_granularity = 8192",
"size": {
"default": 485
},
"total_bytes": 167,
"metadata_only": false
}

数据（与clickhouse数据目录结构一致）

[root@backup-db 202203_1_3_1]# ll -h
total 36K
-r--r----- 2 clickhouse clickhouse 258 Mar 11 15:13 checksums.txt
-r--r----- 2 clickhouse clickhouse 71 Mar 11 15:13 columns.txt
-r--r----- 2 clickhouse clickhouse 1 Mar 11 15:13 count.txt
-r--r----- 2 clickhouse clickhouse 67 Mar 11 15:13 data.bin
-r--r----- 2 clickhouse clickhouse 80 Mar 11 15:13 data.mrk3
-r--r----- 2 clickhouse clickhouse 10 Mar 11 15:13 default_compression_codec.txt
-r--r----- 2 clickhouse clickhouse 8 Mar 11 15:13 minmax_create_time.idx
-r--r----- 2 clickhouse clickhouse 4 Mar 11 15:13 partition.dat
-r--r----- 2 clickhouse clickhouse 2 Mar 11 15:13 primary.idx

[root@backup-db 202203_1_3_1]# pwd
/data/prd/clickhouse/21.8.14/ch9000/data/backup/ch_bk_20220311/shadow/hydb/tanktest/default/202203_1_3_1

3. 单表备份

语法

clickhouse-backup create [-t, --tables=<db>.<table>] <backup_name>

备份表 hydb.events_local

clickhouse-backup create -t hydb.events_local

仅备份表结构

clickhouse-backup create -t hydb.events_local -s

备份多个表

clickhouse-backup create -t hydb.events_local01,-t hydb.events_local02,-t hydb.events_local03

4. 其他常用命令

查看可备份的表（注意备份用户要有system.tables的对应权限，否则会返回空）

clickhouse-backup tables

查看备份文件

clickhouse-backup list

删除指定备份文件

clickhouse-backup delete local test20201019

清除所有shadow目录下的备份文件

clickhouse-backup clean

三、数据恢复

1. 查看帮助

[root@backup-db hydb]# clickhouse-backup restore -help
NAME:
clickhouse-backup restore - Create schema and restore data from backup

USAGE:
clickhouse-backup restore [-t, --tables=<db>.<table>] [--partitions=<partitions_names>] [-s, --schema] [-d, --data] [--rm, --drop] [--rbac] [--configs] <backup_name>

OPTIONS:
--config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG]
--table value, --tables value, -t value table name patterns, separated by comma, allow ? and * as wildcard
--partitions value partition names, separated by comma
--schema, -s Restore schema only
--data, -d Restore data only
--rm, --drop Drop table before restore
--rbac, --restore-rbac, --do-restore-rbac Restore RBAC related objects only
--configs, --restore-configs, --do-restore-configs Restore CONFIG related files only

2. 本机数据恢复测试

删除测试表

backup-db.dji.com :) select * from events_local;

SELECT *
FROM events_local

Query id: 389fc6ea-0ce6-498a-aa57-9c4f656d0341

┌────ts_date─┬─user_id─┐
│ 2022-03-11 │ 10000 │
│ 2022-03-11 │ 10001 │
│ 2022-03-11 │ 10002 │
└────────────┴─────────┘

3 rows in set. Elapsed: 0.003 sec.

backup-db.dji.com :) drop table events_local ON CLUSTER ads_cluster;

DROP TABLE events_local ON CLUSTER ads_cluster

Query id: d52a161b-37bf-48e8-b2ad-2f180965d503

┌─host────────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ CH01 │ 9000 │ 0 │ │ 2 │ 0 │
│ CH02 │ 9000 │ 0 │ │ 1 │ 0 │
│ backup-db │ 9000 │ 0 │ │ 0 │ 0 │
└─────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

3 rows in set. Elapsed: 0.127 sec.

backup-db.dji.com :) select * from events_local;

SELECT *
FROM events_local

Query id: e514d39f-fda5-4534-a542-e450168406ff

0 rows in set. Elapsed: 0.001 sec.

Received exception from server (version 21.8.14):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table hydb.events_local doesn't exist.

恢复数据

为缩短恢复时间，只恢复被删除的表

clickhouse-backup restore ch_bk_20220311 --table hydb.events_local

[root@backup-db hydb]# clickhouse-backup restore ch_bk_20220311 --table hydb.events_local
2022/03/11 16:54:13.049287 info done backup=ch_bk_20220311 operation=restore table=hydb.events_local
2022/03/11 16:54:13.049346 info done backup=ch_bk_20220311 duration=88ms operation=restore
2022/03/11 16:54:13.049357 info done backup=ch_bk_20220311 operation=restore

再次查询

backup-db.dji.com :) show create table events_local;

SHOW CREATE TABLE events_local

Query id: 38ae9a51-9a52-4177-b21d-213adff0fbef

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE hydb.events_local
(
`ts_date` Date,
`user_id` Int64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_local', '{replica}')
PARTITION BY ts_date
ORDER BY ts_date
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.001 sec.

backup-db.dji.com :) select * from events_local;

SELECT *
FROM events_local

Query id: 7e8a3da1-c2ba-43f7-b862-e0f04c20848e

3 rows in set. Elapsed: 0.002 sec.

3. 异机恢复测试

在另一个clickhouse集群服务器安装及配置clickhouse-backup

原服务器 - 整理及打包

events_local.json文件放入目标库 metadata/hydb
metadata.json 文件放入目标库 backup/ch_bk_20220311
打包events_local目录

cd /data/prd/clickhouse/21.8.14/ch9000/data/backup/ch_bk_20220311/shadow/hydb

tar -zcvf events_local.tar.gz events_local

[root@backup-db hydb]# ls
events_local events_local.tar.gz tanktest

目标服务器 - 创建对应目录并移入备份文件

将原服务器的backup目录放到本机$data_path目录下（如果只恢复部分表，则只挪部分表部分数据过来）

mkdir -p /var/lib/clickhouse/backup/ch_bk_20220311/metadata/hydb
mkdir -p /var/lib/clickhouse/backup/ch_bk_20220311/shadow/hydb

移入数据文件

events_local.tar.gz文件传到目标服务器 /var/lib/clickhouse/backup/ch_bk_20220311/shadow/hydb
解压
tar -zxvf events_local.tar.gz

移入元数据文件

cd /var/lib/clickhouse/backup/ch_bk_20220311/metadata/hydb

[root@restore-db hydb]# ls
events_local.json

[root@restore-db ch_bk_20220311]# ls
metadata metadata.json shadow
[root@restore-db ch_bk_20220311]# pwd
/var/lib/clickhouse/backup/ch_bk_20220311

[root@restore-db clickhouse]# chown clickhouse.clickhouse /var/lib/clickhouse/backup -R

列出备份信息

[root@restore-db clickhouse]# clickhouse-backup list
ch_bk_20220311 47.57GiB 11/03/2022 07:59:53 local

恢复数据

[root@restore-db clickhouse]# clickhouse-backup restore ch_bk_20220311 --table hydb.events_local
2022/03/11 17:17:05.587049 info done backup=ch_bk_20220311 operation=restore table=hydb.events_local
2022/03/11 17:17:05.587109 info done backup=ch_bk_20220311 duration=68ms operation=restore
2022/03/11 17:17:05.587119 info done backup=ch_bk_20220311 operation=restore
You have mail in /var/spool/mail/root
[root@restore-db clickhouse]# clickhouse-client --password
ClickHouse client version 21.9.6.24 (official build).
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.9.6 revision 54449.

restore-db :) use hydb;

USE hydb

Query id: 68b1be97-d79a-4e28-afcb-03241faa4600

Ok.

0 rows in set. Elapsed: 0.001 sec.

restore-db :) select * from events_local;

SELECT *
FROM events_local

Query id: d30d1025-8690-49c9-871f-7eca08a9fcd6

3 rows in set. Elapsed: 0.002 sec.

restore-db :) show create table events_local;

SHOW CREATE TABLE events_local

Query id: fb784f8e-83f4-419e-a118-d4a0c6d41d26

1 rows in set. Elapsed: 0.003 sec.

参考
https://clickhouse.com/docs/en/operations/backup/
https://github.com/AlexAkulov/clickhouse-backup
https://segmentfault.com/a/1190000038604737
https://www.modb.pro/db/41261

clickhouse-backup 备份恢复测试

一、安装配置

1. 下载

2. 安装

3. 编辑配置文件

二、备份测试

1. 查看帮助

2. 备份所有库

3. 单表备份

4. 其他常用命令

三、数据恢复

1. 查看帮助

2. 本机数据恢复测试

3. 异机恢复测试

相关阅读

相关文章

相关问答

相关文档

clickhouse-backup 备份恢复测试

一、安装配置

1. 下载

2. 安装

3. 编辑配置文件

二、 备份测试

1. 查看帮助

2. 备份所有库

3. 单表备份

4. 其他常用命令

三、 数据恢复

1. 查看帮助

2. 本机数据恢复测试

3. 异机恢复测试

相关阅读

相关文章

相关问答

相关文档

二、备份测试

三、数据恢复