Clickhouse备份
版本: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
这里需要等待1分钟左右,不知道那个参数控制的
[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
通过指定--config 参数备份多个节点
