clickhouse-backup备份和恢复
版本:1.2.2
1.下载
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v0.6.0/clickhouse-backup.tar.gz
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.2.1/clickhouse-backup.tar.gz
下载地址
https://github.com/AlexAkulov/clickhouse-backup/releases
2.解压即可使用
[root@localhost soft]# tar -xf clickhouse-backup.tar.gz
[root@localhost soft]# cd clickhouse-backup/
[root@localhost soft]# cp clickhouse-backup /usr/local/bin/
查看版本
[root@localhost clickhouse-backup]# clickhouse-backup -v
Version: 1.2.2
Git Commit: 09d6131b6a44e8881db7cc99aa4e5c7ce263afc3
Build Date: 2021-11-26
3.编辑配置文件
mkdir -p /etc/clickhouse-backup/
vi /etc/clickhouse-backup/config.yml
general:
remote_storage: none
backups_to_keep_local: 7
backups_to_keep_remote: 31
clickhouse:
username: default
password: ""
host: localhost
port: 9000
data_path: "/var/lib/clickhouse"
这里注意,需要填写所在ck服务器的账号密码
4.查看全部默认的配置项
clickhouse-backup default-config
# 查看可备份的表
[root@localhost clickhouse-backup]# clickhouse-backup tables
db_test.app_message_80 233.65MiB default
db_test.app_message_80_cp 0B default
5.创建备份
[root@localhost clickhouse-backup]#clickhouse-backup create
备份存储在中 $data_path/backup 下，备份名称默认为时间戳，可手动指定备份名称。例如：
[root@localhost clickhouse-backup]#clickhouse-backup create bak20220114
备份包含两个目录：
'metadata'目录: 包含重新创建所需的DDL SQL
'shadow'目录: 包含作为ALTER TABLE ... FREEZE操作结果的数据.
6.查看备份文件
[root@localhost clickhouse-backup]# clickhouse-backup list
2022-01-14T02-58-42 233.65MiB 14/01/2022 02:58:42 local
bak20220114 233.65MiB 14/01/2022 02:59:35 local
7.删除备份
clickhouse-backup delete local 2022-01-14T02-58-42
clickhouse-backup delete local bak20220114
8.整理成备份脚本
[root@localhost scripts]# more backup_clickhouse.sh
#!/bin/sh
backup_date=`date "+%Y%m%d"`
delete_date=`date +%Y%m%d -d "3 days ago"`
/usr/local/bin/clickhouse-backup create bak${backup_date}
/usr/local/bin/clickhouse-backup delete local bak${delete_date}
-----------------数据恢复-----------------------------------
A.本地恢复
1.删除表
localhost :) drop table app_message_80;
2.恢复
[root@localhost clickhouse-backup]# clickhouse-backup restore bak20220114 -table db_test.app_message_80
2022/01/14 13:59:37 error can't create table `db_test`.`app_message_80`: code: 57, message: Directory for table data store/9a2/9a2875bb-3dfb-4ba8-9a28-75bb3dfbdba8/ already exists after 1 times, please check your schema dependencies
[root@localhost clickhouse-backup]# clickhouse-backup restore bak20220114 -table db_test.app_message_80
2022/01/14 14:01:36 error can't create table `db_test`.`app_message_80`: code: 57, message: Directory for table data store/9a2/9a2875bb-3dfb-4ba8-9a28-75bb3dfbdba8/ already exists after 1 times, please check your schema dependencies
[root@localhost clickhouse-backup]# clickhouse-backup restore bak20220114 -table db_test.app_message_80
2022/01/14 14:04:17 error can't create table `db_test`.`app_message_80`: code: 57, message: Directory for table data store/9a2/9a2875bb-3dfb-4ba8-9a28-75bb3dfbdba8/ already exists after 1 times, please check your schema dependencies
解决办法，去掉备份文件json中的uuid
去掉 /opt/clickhouse/data/backup/bak20240424/metadata/db_test/tb_test_local.json 中的UUID
[root@dsc1 db_test]# more tb_test_local.json
{
"table": "tb_test_local",
"database": "db_test",
"parts": {
"default": [
{
"name": "all_1_1_7"
}
]
},
"query": "CREATE TABLE db_test.tb_test_local UUID 'c0eb108e-3715-4dce-80eb-108e37151dce' (`id` Int32, `name1` String, `name2` String,
`name3` String, `name4` String, `name5` String, `name6` String) ENGINE = MergeTree ORDER BY id SETTINGS index_granularity = 8192",
"size": {
"default": 1483
},
"total_bytes": 1147,
"metadata_only": false
}
[root@localhost clickhouse-backup]# clickhouse-backup restore bak20220114 -table db_test.app_message_80
2022/01/14 14:07:38 info done backup=bak20220114 operation=restore table=db_test.app_message_80
2022/01/14 14:07:38 info done backup=bak20220114 duration=81ms operation=restore
2022/01/14 14:07:38 info done backup=bak20220114 operation=restore
b.异机恢复
1.将源端备份目录打包并传到恢复机器
tar -cvf bak20220114.tar ./bak20220114
scp bak20220114.tar root@192.168.1.136:/tmp/
2.恢复机器上创建备份目录
[root@localhost clickhouse-backup]# cd /var/lib/clickhouse
[root@localhost clickhouse]# mkdir backup
将源端备份的文件解压并将目录拷贝到恢复机器的备份目录
cd /tmp/
tar -xvf bak20220114.tar
mv /tmp/bak20220114 /var/lib/clickhouse/backup/
3.恢复
[root@localhost tmp]# clickhouse-backup list
2022/01/14 14:36:28 error can't connect to clickhouse: code: 516, message: default: Authentication failed: password is incorrect or there is no user with such name
注意这里需要配置/etc/clickhouse-backup/config.yml 指定正确的账号密码
[root@localhost tmp]# clickhouse-backup list
bak20220114 239.01MiB 14/01/2022 05:54:25 local
再次执行恢复
[root@localhost tmp]# clickhouse-backup restore bak20220114 -table db_test.app_message_80
2022/01/14 14:38:48 info done backup=bak20220114 operation=restore table=db_test.app_message_80
2022/01/14 14:38:48 info done backup=bak20220114 duration=240ms operation=restore
2022/01/14 14:38:48 info done backup=bak20220114 operation=restore
4.查看恢复情况
clickhouse-client --host 192.168.1.136 --port=9000 --password 123456
-------------------------备份集群多个节点(待验证)---------------------
[root@localhost all_1_4_1]# clickhouse-backup -help
NAME:
clickhouse-backup - Tool for easy backup of ClickHouse with cloud support
USAGE:
clickhouse-backup <command> [-t, --tables=<db>.<table>] <backup_name>
VERSION:
1.2.2
DESCRIPTION:
Run as 'root' or 'clickhouse' user
COMMANDS:
tables Print list of tables
create Create new backup
create_remote Create and upload
upload Upload backup to remote storage
list Print list of backups
download Download backup from remote storage
restore Create schema and restore data from backup
restore_remote Download and restore
delete Delete specific backup
default-config Print default config
server Run API server
help, h Shows a list of commands or help for one command
GLOBAL OPTIONS:
--config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG]
--help, -h show help
--version, -v print the version
#######################备份到ftp服务器#######################
需要提前搭建好sftp服务器,可以参考如下连接
https://www.cnblogs.com/hxlasky/p/18157549
1.进行远程配置
/etc/clickhouse-backup/config.yml
[root@dsc1 clickhouse-backup]# more config.yml
general:
remote_storage: sftp
max_file_size: 0
backups_to_keep_local: 0
backups_to_keep_remote: 0
sftp:
address: "192.168.56.103"
port: 22
username: "ckbackup"
password: "123456"
key: ""
path: "/opt/ftpbak/ckbackup/upload"
object_disk_path: ""
compression_format: tar
compression_level: 1
concurrency: 6
debug: false
这里的目录单独创建一个upload存放备份文件,需要在ftp服务器上创建
mkdir -p mkdir -p /opt/ftpbak/ckbackup/upload
chown -R ckbackup:sftp /opt/ftpbak/ckbackup/upload
2.创建远程备份
clickhouse-backup create_remote remote_bak0425
会自动进行本地备份的:
[root@dsc1 clickhouse-backup]# clickhouse-backup list
remote_bak0425 2.27KiB 25/04/2024 07:43:42 local regular
remote_bak0425 9.38KiB 25/04/2024 15:43:43 remote tar, regular
3.远程恢复
clickhouse-backup restore_remote remote_bak0425 -table db_test.tb_test_local;
恢复的过程是先从远程将备份集下载到本地主机的backup目录后,在进行恢复的.
报如下错误需要删除表对应的json文件中创建表的uuid
2024/04/25 15:50:58.723980 error can't create table `db_test`.`tb_test_local`: code: 57,
message: Directory for table data store/5fb/5fbe3148-28d8-46b4-9fbe-314828d8c6b4/ already exists after 1 times, please check your schema dependencies
