clickhouse数据库备份

clickhouse备份方法有如下几种：

（一）文本文件导入导出

导出：clickhouse-client -h 127.0.0.1 –query=”select * from db.test” > test.csv

导入： clickhouse-client –query=”insert into db.test FORMAT CSV”<test.csv

（二）使用clickhouse-backup软件备份数据库（推荐）

官方下载地址：
Release 1.3.2 · AlexAkulov/clickhouse-backup · GitHub 下载rpm包直接安装即可

安装好clickhouse-backup后，创建一下配置文件
/etc/clickhouse-backup/config.yml ，参考默认文件config.yml.example

/etc/clickhouse-backup/config.yml内容如下：

general:
  remote_storage: none
  max_file_size: 0
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 0
  log_level: info
  allow_empty_backups: false
  download_concurrency: 1
  upload_concurrency: 1
  restore_schema_on_cluster: ""
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: default
  password: ""
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: true
  config_dir: /etc/clickhouse-server/
  restart_command: systemctl restart clickhouse-server
  ignore_not_exists_error_during_freeze: true
  tls_key: ""
  tls_cert: ""
  tls_ca: ""
  debug: false

备份命令如下：

（1）全库备份 clickhouse-backup create

（2）单表备份 clickhouse-backup create [-t, –tables=<db>.<table>] <backup_name>

其他命令：

查看可备份的表 clickhouse-backup tables

查看备份文件 clickhouse-backup list

恢复命令如下：

clickhouse-backup restore 备份名