clickhouse-backup备份数据应用
版本:1.2.1
一、安装
1.下载
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.2.1/clickhouse-backup.tar.gz
下载地址
https://github.com/AlexAkulov/clickhouse-backup/releases
注意 建议用root用户操作，普通用户注意设置文件目录权限。
2.解压即可使用
tar -xf clickhouse-backup.tar.gz cd clickhouse-backup/ cp clickhouse-backup /usr/local/bin/ #查看版本 clickhouse-backup -v Version: 1.2.1 Git Commit: 38cac6b647f46c3e076650d574eb1f2fb8c3ecf0 Build Date: 2021-10-30
3.编辑配置文件
mkdir -p /etc/clickhouse-backup/ vi /etc/clickhouse-backup/config.yml
#加一个必要设置即可【可不用指定数据目录，注意配置文件格式，避免加载失败】
general: remote_storage: none
【如需要更改ck服务器的账号密码，建议参照配置项调整。不设置使用default用户以及初始密码】
4.查看全部默认的配置项，可根据需求更改。
clickhouse-backup default-config
general:
remote_storage: none
max_file_size: 107374182400
disable_progress_bar: true
backups_to_keep_local: 0
backups_to_keep_remote: 0
log_level: info
allow_empty_backups: false
download_concurrency: 4
upload_concurrency: 4
clickhouse:
username: default
password: ""
host: localhost
port: 9000
disk_mapping: {}
skip_tables:
- system.*
timeout: 5m
freeze_by_part: false
secure: false
skip_verify: false
sync_replicated_tables: false
log_sql_queries: false
config_dir: /etc/clickhouse-server/
restart_command: systemctl restart clickhouse-server
debug: false
s3:
access_key: ""
secret_key: ""
bucket: ""
endpoint: ""
region: us-east-1
acl: private
assume_role_arn: ""
force_path_style: false
path: ""
disable_ssl: false
compression_level: 1
compression_format: tar
sse: ""
disable_cert_verification: false
storage_class: STANDARD
concurrency: 1
part_size: 0
debug: false
gcs:
credentials_file: ""
credentials_json: ""
bucket: ""
path: ""
compression_level: 1
compression_format: tar
debug: false
endpoint: ""
cos:
url: ""
timeout: 2m
secret_id: ""
secret_key: ""
path: ""
compression_format: tar
compression_level: 1
api:
listen: localhost:7171
enable_metrics: true
enable_pprof: false
username: ""
password: ""
secure: false
certificate_file: ""
private_key_file: ""
create_integration_tables: false
ftp:
address: ""
timeout: 2m
username: ""
password: ""
tls: false
path: ""
compression_format: tar
compression_level: 1
concurrency: 4
debug: false
sftp:
address: ""
port: 22
username: ""
password: ""
key: ""
path: ""
compression_format: tar
compression_level: 1
concurrency: 1
azblob:
endpoint_suffix: core.windows.net
account_name: ""
account_key: ""
sas: ""
use_managed_identity: false
container: ""
path: ""
compression_level: 1
compression_format: tar
sse_key: ""
# 查看可备份的表【可调整配置文件过滤不想备份的表】
clickhouse-backup tables
二、备份
1.创建备份
clickhouse-backup create #备份存储在中 $data_path/backup 下，备份名称默认为时间戳，可手动指定备份名称。例如： clickhouse-backup create bak20220309
备份包含两个目录一个文件：
‘metadata.json’文件：备份的库表等信息
'metadata'目录: 包含重新创建所需的DDL SQL
'shadow'目录: 包含作为ALTER TABLE ... FREEZE操作结果的数据.【即原data文件中的表存储的结构文件】
2.查看备份文件
clickhouse-backup list 2022-03-08T02-58-42 233.389.25KiB 08/03/2022 08:26:42 local bak20220308 389.25KiB 08/03/2022 08:27:11 local
3.删除备份
clickhouse-backup delete local 2022-03-08T02-58-42 clickhouse-backup delete local bak20220308
三、恢复
1.本机恢复
1）删除表
DROP TABLE test.tb_test_replacing2 Query id: b982e131-b64e-4ff0-b42b-2718ad669766 Ok. 0 rows in set. Elapsed: 0.002 sec.
2）恢复
clickhouse-backup restore bak20220308 -table test.tb_test_replacing2 2022/03/09 09:33:36 info done backup=bak20220308 operation=restore table=test.tb_test_replacing2 2022/03/09 09:33:36 info done backup=bak20220308 duration=87ms operation=restore 2022/03/09 09:33:36 info done backup=bak20220308 operation=restore
2.异机恢复
1）将源端备份目录打包并传到恢复机器
tar -cvf bak20220308.tar ./bak20220308 #scp rz 都可
2）恢复机器上创建备份目录
cd /data/clickhouse #建议和备份的目录一致 mkdir backup #将源端备份的文件解压并将目录拷贝到恢复机器的备份目录 tar -xvf bak20220308.tar mv /tmp/bak20220308 /data/clickhouse/backup/
3）恢复
【注意这里需要配置/etc/clickhouse-backup/config.yml 指定正确的账号密码】
clickhouse-backup list bak20220308 389.25KiB 09/03/2022 08:47:11 local
再次执行恢复
clickhouse-backup restore bak20220308 -table test.tb_test_replacing2 2022/03/09 09:40:21 info done backup=bak20220308 operation=restore table=test.tb_test_replacing2 2022/03/09 09:40:21 info done backup=bak20220308 duration=172ms operation=restore 2022/03/09 09:40:21 info done backup=bak20220308 operation=restore
3.查看恢复情况
clickhouse-client --m
