首页 > 其他分享 >clickhouse-backup 备份恢复测试

clickhouse-backup 备份恢复测试

时间：2023-09-19 18:04:05浏览次数：29  
标签：-- backup 备份 db local events clickhouse


一、安装配置

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_localQuery 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_localQuery 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://www.modb.pro/db/41261

标签：--,backup,备份,db,local,events,clickhouse
From： https://blog.51cto.com/u_13631369/7527855

相关文章

  • 导出复制行备份
    XSSFRowrow15=sheetAt.getRow(15);sheetAt.shiftRows(15,sheetAt.getLastRowNum(),map2.size()-1,true,false);for(inta=0;a<(map2.size()-1);a++){XSSFRowtargetrow=sheetAt.createRow......
  • SQL Server备份/还原 SQL注入
    SQL还原目标数据库 注：不能在目标数据库会话中执行alterdatabasetestsetsingle_userwithrollbackimmediate--(这里也可以延迟几秒回滚你的操作)restoredatabasetestfromdisk='d:\test.bak'alterdatabasetestsetmulti_user无意中看到的，有意思的SQL注入：--完整备份......
  • KingbaseES V8R6备份恢复案例之---备份crond计划任务的调整
    案例说明：在KingbaseESV8R6数据库通常备份建立的crond任务，默认由root用户建立，写入到/etc/cron.d/KINGBASECRON文件中，在有的生产环境限制了普通用户使用root的权限，创建计划任务可能失败，新的KingbaseESV8R6版本，将计划任务由数据库用户创建，比如通用机环境，将由kingbase用户创建计划......
  • KingbaseES V8R6集群备份恢复案例之---备份初始化“can not find primary node”故障
    案例说明：KingbaseESV8R6集群，备库作为repo-path节点，建立类型为‘cluster’模式的备份，在执行sys_backup.shinit时，出现“cannotfindprimarynode”故障。故障如下图所示：适用版本：KingbaseESV8R6一、集群及备份配置1、集群节点状态[kingbase@node101bin]$./repmgrclus......
  • KingbaseES V8R6集群运维案例--cluster模式备份sys_backup.sh init故障
    KingbaseESV8R6集群运维案例--cluster模式备份sys_backup.shinit故障案例说明：通过脚本方式部署KingbaseESV8R6集群后，在‘cluster’模式备份，执行sys_backup.shinit时，出现“cannotcontain//for”log-path"option"错误，但是在‘single’模式下备份，无此错误。故障信息......
  • docker容器里gitlab备份
    一、数据的备份docker容器里的gitlab备份chatgpt/2023/9/185:35:56一、数据的备份1.进入容器dockerexec-itgitlab/bin/bash2、执行备份命令gitlab-rakegitlab:backup:create默认将备份文件保存至/var/opt/gitlab/backups/3.将json和rb文件也复制到备份目录下......
  • 一文详解数据仓库的物理细粒度备份恢复
    本文分享自华为云社区《DTSETechTalk|第43期：数仓数据可靠保证——物理细粒度备份恢复》，作者：华为云社区精选。大数据时代，数据对企业的重要性不言而喻，如果发生数据丢失或因为误操作而造成数据丢失，将对企业的经营决策带来不可估量的损失。本期《备份恢复全掌握，数仓数据更安全》......
  • Linux svn进行备份还原
    在Linux上进行Subversion(SVN)的全量备份，可以使用`svnadmindump`命令来生成仓库的完整备份文件。以下是进行全量备份的步骤：1.停止SVN服务：在备份之前，停止SVN服务以确保备份文件的一致性和完整性。具体停止SVN服务的方式取决于所使用的版本控制软件和Linux发行版......
  • 9-MySQL数据库 数据的备份与恢复
    1.date文件的备份2.mysqldump备份说明：mysqldump是MySQL数据库中的一个实用程序，它主要用于转储（备份）数据库。mysqldump通过生成一个SQL脚本文件，包含从头开始重新创建数据库所必需的（如CREATETABLE和INSERT等），来实现数据库的备份和转储。这样，你可以在任何时候通过运行这个脚本文......
  • Mongodb/Elasticsearch数据备份/还原（随记）
    一，MongoDBv4.0.3数据备份/还原登录mongo库：mongo--port27017-u"root"-p"xxxxxxxx"--authenticationDatabase"admin"################备份##################1，mongo全库备份：mongodump-uroot-pxxxxxxxx--authenticationDatabaseadmin--op......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99