clickhouse-backup 备份恢复测试
一、安装配置1. 下载https://github.com/AlexAkulov/clickhouse-backup/releases2. 安装[root@backup-db ~]# rpm -ivh clickhouse-backup-1.3.1-1.x86_64.rpmPreparing... ################################# [100%]Updating /
文章共2,050字 · 阅读需要大约7分钟
一、安装配置
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
┌────ts_date─┬─user_id─┐
│ 2022-03-11 │ 10000 │
│ 2022-03-11 │ 10001 │
│ 2022-03-11 │ 10002 │
└────────────┴─────────┘
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
┌────ts_date─┬─user_id─┐
│ 2022-03-11 │ 10000 │
│ 2022-03-11 │ 10001 │
│ 2022-03-11 │ 10002 │
└────────────┴─────────┘
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
┌─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.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
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
1
0- 0
已为社区贡献13条内容
所有评论(0)