一、介绍
相比于FREEZE,DETACH手动备份数据的繁琐,clickhouse-backup自动化了FREEZE的整个流程,且不会影响元原表的使用和数据,支持server化,可以和CI/CD服务整合,支持增量备份,支持元数据备份,比较人性化。
参考文档:https://github.com/AlexAkulov/clickhouse-backup
二、下载clickhouse-backup
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.0.0/clickhouse-backup.tar.gz
tar -zxvf clickhouse-backup.tar.gz # 解压
cd clickhouse-backup
cp clickhouse-backup /usr/local/bin/ # 将可执行文件复制到/usr/local/bin
mkdir /etc/clickhouse-backup # /etc下创建一个目录,用来放置配置文件config.yml
cp config.yml /etc/clickhouse-backup/
vim /etc/clickhouse-backup/config.yml # 修改配置文文件
配置文件config.yml如下
general:
remote_storage: sftp # 通过sftp,上传到远程服务器的话,需要这个参数,否则为none
max_file_size: 1099511627776
disable_progress_bar: false
backups_to_keep_local: 2 # 本地备份的个数,大于2则自动删除旧的备份,默认为0,不删除备份
backups_to_keep_remote: 2 # 远程备份的个数
log_level: info
allow_empty_backups: false
clickhouse:
username: default # 本地clickhouse的连接参数
password: ""
host: localhost
port: 9010
disk_mapping: {}
skip_tables:
- system.*
- db.*
- information_schema.*
- INFORMATION_SCHEMA.*
timeout: 5m
freeze_by_part: false
secure: false
skip_verify: false
sync_replicated_tables: true
skip_sync_replica_timeouts: true
log_sql_queries: false
s3:
access_key: ""
secret_key: ""
bucket: ""
endpoint: ""
region: us-east-1
acl: private
force_path_style: false
path: ""
disable_ssl: false
part_size: 536870912
compression_level: 1
compression_format: tar
sse: ""
disable_cert_verification: false
storage_class: STANDARD
gcs:
credentials_file: ""
credentials_json: ""
bucket: ""
path: ""
compression_level: 1
compression_format: tar
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
sftp:
address: "host"
port: 端口号
username: "用户名"
password: "密码"
key: ""
path: "/home/data_dev/clickhouse_backup" # 上传文件到远程服务器的路径
compression_format: tar
compression_level: 1
azblob:
endpoint_suffix: core.windows.net
account_name: ""
account_key: ""
sas: ""
container: ""
path: ""
compression_level: 1
compression_format: tar
sse_key: ""
三、本地和远程创建备份
clickhouse-backup tables # 查看可以备份的表
clickhouse-backup create # 全库备份,一般这个备份的位置是原clickhouse数据存储路径下新建了一个backup文件夹,这个文件夹下会存备份
clickhouse-backup create -t 数据库.表名1,数据库.表名2 # 多表备份,单表类似
clickhouse-backup list # 查看已经生成的备份,如果设置了remote_storage: sftp,此条命令还会检测是否成功连接上了远程服务器
clickhouse-backup restore --help # 查看数据恢复相关参数
# 使用scp,将备份上传到服务器上,如果config.yml中设置了sftp,则不需要这一步
scp -rp /var/lib/clickhouse/backup/备份名 name@host:/data/clickhouse-backup/
# 通过配置文件里的sftp上传备份到服务器
clickhouse-backup upload 备份名
四、下载远程文件,并恢复
删除表,后还原数据
--下载备份文件
./clickhouse-backup download demo_ckbackup_tbl_20210728
--注意一定要授权
chown clickhouse:clickhouse -R /data1/clickhouse/store/ed5/ed52238e-xxxx-xxxx-bc95-9207927d186c/detached/*
--恢复数据到ck --schema 只恢复数据结构 --data 恢复数据
./clickhouse-backup restore demo_ckbackup_tbl_20210728
./clickhouse-backup restore demo_ckbackup_tbl_20210728 -t db.tab1,db.tab2
五、crontab定时备份
crontab -e 或者vim /etc/crontab
输入以下内容
40 15 * * * sh /data/clickhouse_backup.sh > /data/a.log 2>&1 # 表示每天15:40分会运行clickhouse_backup.sh
vim /data/clickhouse_backup.sh
输入以下内容
#!/bin/bash
BACKUP_NAME=ch_backup_$(date +%Y-%m-%dT%H-%M-%S)
/usr/local/bin/clickhouse-backup create $BACKUP_NAME #本地备份
/usr/local/bin/clickhouse-backup upload $BACKUP_NAME # 本地备份之后,上传到远程服务器
五、查看备份文件
本地备份路径 /var/lib/clickhouse/backup/文件夹内
远程路径在对应路径之下