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 [email protected]:/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 参数备份多个节点