大数据-ClickHouse技术六（clickhouse-backup备份）

一、介绍

相比于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/文件夹内

远程路径在对应路径之下