SRE文档

深浅模式

1. ckPart备份 ​

1.1 介绍 ​

官当

来自官方介绍，尽管 副本 可以提供针对硬件的错误防护, 但是它不能预防人为操作失误: 数据的意外删除, 错误表的删除或者错误集群上表的删除, 以及导致错误数据处理或者数据损坏的软件bug. 在很多案例中，这类意外可能会影响所有的副本. ClickHouse 有内置的保护措施可以预防一些错误 — 例如, 默认情况下 不能人工删除使用带有MergeTree引擎且包含超过50Gb数据的表. 但是，这些保护措施不能覆盖所有可能情况，并且这些措施可以被绕过。

1.2 原理 ​

手工备份，是使用CK的ALTER TABLE ... FREEZE PARTITION ...命令来实现，是利用硬链接到一个目录(/var/lib/clickhouse/shadow)，恢复时从 /var/lib/clickhouse/bakcup 寻找指定的名称进行恢复

需要注意的是 手工备份和恢复 都不会涉及到表结构，只单纯的备份和恢复数据，因此相关表结构需要 另外存档和自行创建。

1.3 备份-表级别 ​

  • 创建目录
bash
#根据自己的路径修改
mkdir /var/lib/clickhouse/{shadow,bakcup}

#修改权限
chown -R clickhouse. bakcup shadow

#查看
# ls -ld bakcup shadow
drwxr-xr-x 2 clickhouse clickhouse 6 11?  6 15:45 bakcup
drwxr-xr-x 2 clickhouse clickhouse 6 11?  6 15:45 shadow
#根据自己的路径修改
mkdir /var/lib/clickhouse/{shadow,bakcup}

#修改权限
chown -R clickhouse. bakcup shadow

#查看
# ls -ld bakcup shadow
drwxr-xr-x 2 clickhouse clickhouse 6 11?  6 15:45 bakcup
drwxr-xr-x 2 clickhouse clickhouse 6 11?  6 15:45 shadow
  • 备份
bash
#登录数据库
[root@kubeadm-master01 20241105_11_13_1]# clickhouse-client -udefault --password 123456
ClickHouse client version 23.12.6.19 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.12.6.

#查看数据库
ch_accesslog :) show databases;

SHOW DATABASES

Query id: 57347c65-6138-4c53-b7c6-1761ddcdd2b7

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ nginxlogs          │
│ system             │
└────────────────────┘

#对nginxlogs进行备份，进入到nginxlogs数据库中
ch_accesslog :) use nginxlogs

USE nginxlogs

#查看有哪些表
ch_accesslog :) show tables;

SHOW TABLES

Query id: 37989ea7-7477-49f5-9c8f-9da7bca2dbc7

┌─name─────────┐
│ nginx_access │
└──────────────┘

#执行备份命令，ALTER TABLE ... FREEZE PARTITION ...
ch_accesslog :) alter table nginx_access freeze;

ALTER TABLE nginx_access
    FREEZE

Query id: 500155af-7662-4e14-8316-caf0b0f71479

Ok.
#登录数据库
[root@kubeadm-master01 20241105_11_13_1]# clickhouse-client -udefault --password 123456
ClickHouse client version 23.12.6.19 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.12.6.

#查看数据库
ch_accesslog :) show databases;

SHOW DATABASES

Query id: 57347c65-6138-4c53-b7c6-1761ddcdd2b7

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ nginxlogs          │
│ system             │
└────────────────────┘

#对nginxlogs进行备份，进入到nginxlogs数据库中
ch_accesslog :) use nginxlogs

USE nginxlogs

#查看有哪些表
ch_accesslog :) show tables;

SHOW TABLES

Query id: 37989ea7-7477-49f5-9c8f-9da7bca2dbc7

┌─name─────────┐
│ nginx_access │
└──────────────┘

#执行备份命令，ALTER TABLE ... FREEZE PARTITION ...
ch_accesslog :) alter table nginx_access freeze;

ALTER TABLE nginx_access
    FREEZE

Query id: 500155af-7662-4e14-8316-caf0b0f71479

Ok.

不交互执行sql命令

echo 'alter table tableName freeze' | clickhouse-client -u xxxx --password xxxx -d dataName

2. ckBACKUP备份 ​

官当

2.1 备份到本地 ​

3.clickhouse-backup ​

官方推荐工具

3.1介绍 ​

clickhouse-backup是一个由Altinity开发的开源项目，专为ClickHouse数据库提供高效、可靠的全量和增量备份解决方案。它利用了ClickHouse原生的API接口，支持多种云和非云存储类型，如AWS、GCS、Azure、腾讯COS、FTP、SFTP等，对于不同数据量的ClickHouse数据库，clickhouse-backup都能提供有效的备份和恢复解决方案。无论是小到几GB的数据库，还是大到几十TB甚至上百TB的数据库，clickhouse-backup都能通过其高效的备份和恢复机制，确保数据的安全性和可用性。

1.为什么用它？ ​

  1. 高效性
    • 全量备份：clickhouse-backup可以生成ClickHouse数据的完整副本，以文件系统或云存储的形式保存。
    • 增量备份：基于上一次的全量或增量备份，只备份自上次备份以来发生改变的数据，极大地减少了存储空间需求。
  2. 灵活性
    • 支持从本地或远程存储恢复到ClickHouse，可选择特定时间点的数据进行恢复。
    • 通过CRON表达式设置自动备份计划，无需手动操作。
    • 支持多节点同步备份，确保集群数据的一致性。
  3. 安全性
    • 支持SSL加密传输，保障备份过程中数据的安全。
  4. 易用性
    • 使用Go语言编写，易于部署和集成，并且有着丰富的命令行选项供用户灵活配置。
    • 支持多种操作系统，如Linux、macOS、Windows。
  5. 可扩展性
    • 可以根据自身需求编写插件，实现自定义的存储后端或者备份策略。

3.2 部署 ​

1.下载 ​

bash
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.6.3/clickhouse-backup-linux-amd64.tar.gz

#直接解压即可
tar zxvf clickhouse-backup-linux-amd64.tar.gz  --strip-components=3 -C /usr/local/bin/

#查看版本
[root@kubeadm-master01 ~]# clickhouse-backup --version
Version:	 2.6.3
Git Commit:	 e039c604807c52cbbce56582a0d22073338d1e3f
Build Date:	 2024-11-04
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.6.3/clickhouse-backup-linux-amd64.tar.gz

#直接解压即可
tar zxvf clickhouse-backup-linux-amd64.tar.gz  --strip-components=3 -C /usr/local/bin/

#查看版本
[root@kubeadm-master01 ~]# clickhouse-backup --version
Version:	 2.6.3
Git Commit:	 e039c604807c52cbbce56582a0d22073338d1e3f
Build Date:	 2024-11-04

2.查看默认配置 ​

yaml
clickhouse-backup default-config

general:
    remote_storage: none
    max_file_size: 0
    backups_to_keep_local: 0
    backups_to_keep_remote: 0
    log_level: info
    allow_empty_backups: false
    download_concurrency: 1
    upload_concurrency: 1
    upload_max_bytes_per_second: 0
    download_max_bytes_per_second: 0
    object_disk_server_side_copy_concurrency: 32
    allow_object_disk_streaming: false
    use_resumable_state: true
    restore_schema_on_cluster: ""
    upload_by_part: true
    download_by_part: true
    restore_database_mapping: {}
    restore_table_mapping: {}
    retries_on_failure: 3
    retries_pause: 5s
    watch_interval: 1h
    full_interval: 24h
    watch_backup_name_template: shard{shard}-{type}-{time:20060102150405}
    sharded_operation_mode: ""
    cpu_nice_priority: 15
    io_nice_priority: idle
    rbac_backup_always: true
    rbac_conflict_resolution: recreate
    retriesduration: 5s
    watchduration: 1h0m0s
    fullduration: 24h0m0s
clickhouse:
    username: default
    password: ""
    host: localhost
    port: 9000
    disk_mapping: {}
    skip_tables:
        - system.*
        - INFORMATION_SCHEMA.*
        - information_schema.*
        - _temporary_and_external_tables.*
    skip_table_engines: []
    timeout: 30m
    freeze_by_part: false
    freeze_by_part_where: ""
    use_embedded_backup_restore: false
    embedded_backup_disk: ""
    backup_mutations: true
    restore_as_attach: false
    check_parts_columns: true
    secure: false
    skip_verify: false
    sync_replicated_tables: false
    log_sql_queries: true
    config_dir: /etc/clickhouse-server/
    restart_command: exec:systemctl restart clickhouse-server
    ignore_not_exists_error_during_freeze: true
    check_replicas_before_attach: true
    default_replica_path: /clickhouse/tables/{cluster}/{shard}/{database}/{table}
    default_replica_name: '{replica}'
    tls_key: ""
    tls_cert: ""
    tls_ca: ""
    max_connections: 1
    debug: false
s3:
    access_key: ""
    secret_key: ""
    bucket: ""
    endpoint: ""
    region: us-east-1
    acl: private
    assume_role_arn: ""
    force_path_style: false
    path: ""
    object_disk_path: ""
    disable_ssl: false
    compression_level: 1
    compression_format: tar
    sse: ""
    sse_kms_key_id: ""
    sse_customer_algorithm: ""
    sse_customer_key: ""
    sse_customer_key_md5: ""
    sse_kms_encryption_context: ""
    disable_cert_verification: false
    use_custom_storage_class: false
    storage_class: STANDARD
    custom_storage_class_map: {}
    concurrency: 2
    part_size: 0
    max_parts_count: 4000
    allow_multipart_download: false
    object_labels: {}
    request_payer: ""
    check_sum_algorithm: ""
    debug: false
gcs:
    credentials_file: ""
    credentials_json: ""
    credentials_json_encoded: ""
    embedded_access_key: ""
    embedded_secret_key: ""
    skip_credentials: false
    bucket: ""
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: tar
    debug: false
    force_http: false
    endpoint: ""
    storage_class: STANDARD
    object_labels: {}
    custom_storage_class_map: {}
    client_pool_size: 32
    chunk_size: 0
cos:
    url: ""
    timeout: 2m
    secret_id: ""
    secret_key: ""
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    debug: false
api:
    listen: localhost:7171
    enable_metrics: true
    enable_pprof: false
    username: ""
    password: ""
    secure: false
    certificate_file: ""
    private_key_file: ""
    ca_cert_file: ""
    ca_key_file: ""
    create_integration_tables: false
    integration_tables_host: ""
    allow_parallel: false
    complete_resumable_after_restart: true
    watch_is_main_process: false
ftp:
    address: ""
    timeout: 2m
    username: ""
    password: ""
    tls: false
    skip_tls_verify: false
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 3
    debug: false
sftp:
    address: ""
    port: 22
    username: ""
    password: ""
    key: ""
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 3
    debug: false
azblob:
    endpoint_schema: https
    endpoint_suffix: core.windows.net
    account_name: ""
    account_key: ""
    sas: ""
    use_managed_identity: false
    container: ""
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: tar
    sse_key: ""
    buffer_size: 0
    buffer_count: 3
    max_parts_count: 256
    timeout: 4h
    debug: false
custom:
    upload_command: ""
    download_command: ""
    list_command: ""
    delete_command: ""
    command_timeout: 4h
    commandtimeoutduration: 4h0m0s
clickhouse-backup default-config

general:
    remote_storage: none
    max_file_size: 0
    backups_to_keep_local: 0
    backups_to_keep_remote: 0
    log_level: info
    allow_empty_backups: false
    download_concurrency: 1
    upload_concurrency: 1
    upload_max_bytes_per_second: 0
    download_max_bytes_per_second: 0
    object_disk_server_side_copy_concurrency: 32
    allow_object_disk_streaming: false
    use_resumable_state: true
    restore_schema_on_cluster: ""
    upload_by_part: true
    download_by_part: true
    restore_database_mapping: {}
    restore_table_mapping: {}
    retries_on_failure: 3
    retries_pause: 5s
    watch_interval: 1h
    full_interval: 24h
    watch_backup_name_template: shard{shard}-{type}-{time:20060102150405}
    sharded_operation_mode: ""
    cpu_nice_priority: 15
    io_nice_priority: idle
    rbac_backup_always: true
    rbac_conflict_resolution: recreate
    retriesduration: 5s
    watchduration: 1h0m0s
    fullduration: 24h0m0s
clickhouse:
    username: default
    password: ""
    host: localhost
    port: 9000
    disk_mapping: {}
    skip_tables:
        - system.*
        - INFORMATION_SCHEMA.*
        - information_schema.*
        - _temporary_and_external_tables.*
    skip_table_engines: []
    timeout: 30m
    freeze_by_part: false
    freeze_by_part_where: ""
    use_embedded_backup_restore: false
    embedded_backup_disk: ""
    backup_mutations: true
    restore_as_attach: false
    check_parts_columns: true
    secure: false
    skip_verify: false
    sync_replicated_tables: false
    log_sql_queries: true
    config_dir: /etc/clickhouse-server/
    restart_command: exec:systemctl restart clickhouse-server
    ignore_not_exists_error_during_freeze: true
    check_replicas_before_attach: true
    default_replica_path: /clickhouse/tables/{cluster}/{shard}/{database}/{table}
    default_replica_name: '{replica}'
    tls_key: ""
    tls_cert: ""
    tls_ca: ""
    max_connections: 1
    debug: false
s3:
    access_key: ""
    secret_key: ""
    bucket: ""
    endpoint: ""
    region: us-east-1
    acl: private
    assume_role_arn: ""
    force_path_style: false
    path: ""
    object_disk_path: ""
    disable_ssl: false
    compression_level: 1
    compression_format: tar
    sse: ""
    sse_kms_key_id: ""
    sse_customer_algorithm: ""
    sse_customer_key: ""
    sse_customer_key_md5: ""
    sse_kms_encryption_context: ""
    disable_cert_verification: false
    use_custom_storage_class: false
    storage_class: STANDARD
    custom_storage_class_map: {}
    concurrency: 2
    part_size: 0
    max_parts_count: 4000
    allow_multipart_download: false
    object_labels: {}
    request_payer: ""
    check_sum_algorithm: ""
    debug: false
gcs:
    credentials_file: ""
    credentials_json: ""
    credentials_json_encoded: ""
    embedded_access_key: ""
    embedded_secret_key: ""
    skip_credentials: false
    bucket: ""
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: tar
    debug: false
    force_http: false
    endpoint: ""
    storage_class: STANDARD
    object_labels: {}
    custom_storage_class_map: {}
    client_pool_size: 32
    chunk_size: 0
cos:
    url: ""
    timeout: 2m
    secret_id: ""
    secret_key: ""
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    debug: false
api:
    listen: localhost:7171
    enable_metrics: true
    enable_pprof: false
    username: ""
    password: ""
    secure: false
    certificate_file: ""
    private_key_file: ""
    ca_cert_file: ""
    ca_key_file: ""
    create_integration_tables: false
    integration_tables_host: ""
    allow_parallel: false
    complete_resumable_after_restart: true
    watch_is_main_process: false
ftp:
    address: ""
    timeout: 2m
    username: ""
    password: ""
    tls: false
    skip_tls_verify: false
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 3
    debug: false
sftp:
    address: ""
    port: 22
    username: ""
    password: ""
    key: ""
    path: ""
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 3
    debug: false
azblob:
    endpoint_schema: https
    endpoint_suffix: core.windows.net
    account_name: ""
    account_key: ""
    sas: ""
    use_managed_identity: false
    container: ""
    path: ""
    object_disk_path: ""
    compression_level: 1
    compression_format: tar
    sse_key: ""
    buffer_size: 0
    buffer_count: 3
    max_parts_count: 256
    timeout: 4h
    debug: false
custom:
    upload_command: ""
    download_command: ""
    list_command: ""
    delete_command: ""
    command_timeout: 4h
    commandtimeoutduration: 4h0m0s

3.创建配置文件 ​

yaml
mkdir -p /etc/clickhouse-backup/

cd /etc/clickhouse-backup/

cat > config.yml <<EOF
general:
  remote_storage: none
  backups_to_keep_local: 7 # 本地备份保留个数，默认0表示不自动做备份清理
  backups_to_keep_remote: 31 # 远程备份保留个数
clickhouse:
  username: default
  password: "123456"
  host: 10.103.236.200 #根据自己环境修改
  port: 9000 # 这个与jdbc链接里的端口没关系，就是9000
  data_path: "/var/lib/clickhouse" #clickhouse的数据的实际存储位置
  skip_tables: # 不需要备份的库
  - system.*
  - default.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
EOF
mkdir -p /etc/clickhouse-backup/

cd /etc/clickhouse-backup/

cat > config.yml <<EOF
general:
  remote_storage: none
  backups_to_keep_local: 7 # 本地备份保留个数，默认0表示不自动做备份清理
  backups_to_keep_remote: 31 # 远程备份保留个数
clickhouse:
  username: default
  password: "123456"
  host: 10.103.236.200 #根据自己环境修改
  port: 9000 # 这个与jdbc链接里的端口没关系，就是9000
  data_path: "/var/lib/clickhouse" #clickhouse的数据的实际存储位置
  skip_tables: # 不需要备份的库
  - system.*
  - default.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
EOF

💡 说明

默认去/etc/clickhouse-backup/config.xml 找，如果不是在这个目录下，需要通过--config指定

clickhouse-backup create my_backup --config /path/config.xml

默认备份文件备份到数据目录下的backup目录下

4.帮助 ​

bash
[root@kubeadm-master01 clickhouse-backup]# 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:
   2.6.3

DESCRIPTION:
   Run as 'root' or 'clickhouse' user

COMMANDS:
   tables               List of tables, exclude skip_tables
   create               Create new backup
   create_remote        Create and upload new backup
   upload               Upload backup to remote storage
   list                 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
   print-config         Print current config merged with environment variables
   clean                Remove data in 'shadow' folder from all 'path' folders available from 'system.disks'
   clean_remote_broken  Remove all broken remote backups
   watch                Run infinite loop which create full + incremental backup sequence to allow efficient backup sequences
   server               Run API server
   help, h              Shows a list of commands or help for one command
[root@kubeadm-master01 clickhouse-backup]# 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:
   2.6.3

DESCRIPTION:
   Run as 'root' or 'clickhouse' user

COMMANDS:
   tables               List of tables, exclude skip_tables
   create               Create new backup
   create_remote        Create and upload new backup
   upload               Upload backup to remote storage
   list                 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
   print-config         Print current config merged with environment variables
   clean                Remove data in 'shadow' folder from all 'path' folders available from 'system.disks'
   clean_remote_broken  Remove all broken remote backups
   watch                Run infinite loop which create full + incremental backup sequence to allow efficient backup sequences
   server               Run API server
   help, h              Shows a list of commands or help for one command

3.3 备份 ​

  • 准备数据
sql
#创建库
create database if not exists test;

#创建表
create table if not exists test.t_order
(
  id Int64 COMMENT '订单id', 
  datetime DateTime COMMENT '订单日期',
  name String COMMENT '手办名称',
  price Decimal32(2) COMMENT '手办价格',
  user_id Int64 COMMENT '用户id'
) engine = MergeTree 
partition by toYYYYMM(datetime)
order by id ;

#插入数据
insert into t_order values (1, '2022-03-08 12:40:00', '张', 999.99, 202203080001)
insert into t_order values (2, '2023-03-08 12:44:00', '李', 1111, 202303080002)
insert into t_order values (3, '2022-03-08 12:45:00', '黄', 8989, 202203080001)
#用全库备份先预留一份，再进行下面操作：

#删除数据
alter table t_order delete where id = 2;

#清空表
truncate table t_order;

#删除表
drop table t_order;

#删除库
drop database test;
#创建库
create database if not exists test;

#创建表
create table if not exists test.t_order
(
  id Int64 COMMENT '订单id', 
  datetime DateTime COMMENT '订单日期',
  name String COMMENT '手办名称',
  price Decimal32(2) COMMENT '手办价格',
  user_id Int64 COMMENT '用户id'
) engine = MergeTree 
partition by toYYYYMM(datetime)
order by id ;

#插入数据
insert into t_order values (1, '2022-03-08 12:40:00', '张', 999.99, 202203080001)
insert into t_order values (2, '2023-03-08 12:44:00', '李', 1111, 202303080002)
insert into t_order values (3, '2022-03-08 12:45:00', '黄', 8989, 202203080001)
#用全库备份先预留一份，再进行下面操作：

#删除数据
alter table t_order delete where id = 2;

#清空表
truncate table t_order;

#删除表
drop table t_order;

#删除库
drop database test;

1.单表备份 ​

  • 语法
bash
clickhouse-backup create [-t, --tables=<db>.<table>] <backup_name>
clickhouse-backup create [-t, --tables=<db>.<table>] <backup_name>
  • 查看有多少表
bash
[root@kubeadm-master01 clickhouse-backup]# clickhouse-backup tables
2024-11-07 11:26:02.669 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。。
nginxlogs.nginx_access  4.06KiB  default  full

2024-11-07 11:26:02.714 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed
[root@kubeadm-master01 clickhouse-backup]# clickhouse-backup tables
2024-11-07 11:26:02.669 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。。
nginxlogs.nginx_access  4.06KiB  default  full

2024-11-07 11:26:02.714 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed
  • 备份nginx_access表

备份名称默认为时间戳，可手动指定备份名称

bash
clickhouse-backup create  -t nginxlogs.nginx_access nginxlogs_nginx_access_data_bak_202411

#查看
[root@kubeadm-master01 clickhouse-backup]# ls /var/lib/clickhouse/backup/
nginxlogs_nginx_access_data_bak_202411

#目录包含
[root@kubeadm-master01 clickhouse-backup]# ls /var/lib/clickhouse/backup/nginxlogs_nginx_access_data_bak_202411/
metadata  metadata.json  shadow
clickhouse-backup create  -t nginxlogs.nginx_access nginxlogs_nginx_access_data_bak_202411

#查看
[root@kubeadm-master01 clickhouse-backup]# ls /var/lib/clickhouse/backup/
nginxlogs_nginx_access_data_bak_202411

#目录包含
[root@kubeadm-master01 clickhouse-backup]# ls /var/lib/clickhouse/backup/nginxlogs_nginx_access_data_bak_202411/
metadata  metadata.json  shadow

备份存储在中 $data_path/backup 下(默认在/var/lib/clickhouse/backup)

💡 说明

  • metadata目录: 包含重新创建所需的DDL SQL
  • shadow目录: 包含作为ALTER TABLE … FREEZE操作结果的数据。
  • metadata.json文件：备份的库表等信息

2.多表备份 ​

两个表用","隔开

bash
clickhouse-backup create  -t nginxlogs.nginx_access, nginxlogs.nginx_access nginxlogs_nginx_access_data_bak_202411
clickhouse-backup create  -t nginxlogs.nginx_access, nginxlogs.nginx_access nginxlogs_nginx_access_data_bak_202411

3.全库备份 ​

  • 备份
bash
$ clickhouse-backup create bak202411
$ clickhouse-backup create bak202411
  • 查看
bash
$ clickhouse-backup list
2024-11-07 11:49:12.884 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。
nginxlogs_nginx_access_data_bak_202411   7.15KiB   07/11/2024 03:42:07   local      regular
bak202411                                7.15KiB   07/11/2024 03:48:14   local      regular
2024-11-07 11:49:12.952 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed
$ clickhouse-backup list
2024-11-07 11:49:12.884 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。
nginxlogs_nginx_access_data_bak_202411   7.15KiB   07/11/2024 03:42:07   local      regular
bak202411                                7.15KiB   07/11/2024 03:48:14   local      regular
2024-11-07 11:49:12.952 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed

3.4 恢复备份 ​

  • 语法
bash
$ clickhouse-backup restore 备份名

--table 只恢复特定表，可以用正则（如还原指定数据库：--table=dbname.*）
--schema 只还原表结构
--data 只还原数据
$ clickhouse-backup restore 备份名

--table 只恢复特定表，可以用正则（如还原指定数据库：--table=dbname.*）
--schema 只还原表结构
--data 只还原数据

1.单表恢复 ​

bash
#删除先前备份的表

#查看
$ clickhouse-backup list

#恢复
$ clickhouse-backup restore nginxlogs_nginx_access_data_bak_202411
会报错
 error="can't create table `nginxlogs`.`nginx_access`: code: 57, message: Directory for table data store/72a/72ad70a1-093a-4254-9098-14d5735fc0d3/ already exists after 1 times, please check your schema dependencies"
 
#解决方式
UUID 'e8c3a343-758d-4e9a-a8c3-a343758dce9a’,这一整个删除掉,重新执行clickhouse-backup restore 备份名
#删除先前备份的表

#查看
$ clickhouse-backup list

#恢复
$ clickhouse-backup restore nginxlogs_nginx_access_data_bak_202411
会报错
 error="can't create table `nginxlogs`.`nginx_access`: code: 57, message: Directory for table data store/72a/72ad70a1-093a-4254-9098-14d5735fc0d3/ already exists after 1 times, please check your schema dependencies"
 
#解决方式
UUID 'e8c3a343-758d-4e9a-a8c3-a343758dce9a’,这一整个删除掉,重新执行clickhouse-backup restore 备份名

2.全库恢复 ​

bash
##需要保证原来的库完全被删除，否则可能会报错,默认会去/var/lib/clickhouse/backup路径下去找备份文件夹
clickhouse-backup restore /var/lib/clickhouse/backup/备份名
##需要保证原来的库完全被删除，否则可能会报错,默认会去/var/lib/clickhouse/backup路径下去找备份文件夹
clickhouse-backup restore /var/lib/clickhouse/backup/备份名

💡 说明

如果遇到报错，Directory for table data store/e8c/e8c3a343-758d-4e9a-a8c3-a343758dce9a/ already exists after 1 times，参考单表恢复的解决方案即可。

3.只恢复表结构 ​

bash
#使用 --schema 恢复表的表结构
clickhouse-backup restore 备份名 --table 库名.表名  --schema
#使用 --schema 恢复表的表结构
clickhouse-backup restore 备份名 --table 库名.表名  --schema

4.只恢复数据 ​

bash
#用 --data 恢复表中数据（注意:由于是ATTACH PARTITION操作，如果执行2次的话，数据会翻倍）
clickhouse-backup restore 备份名 --table 库名.表名  --data
#用 --data 恢复表中数据（注意:由于是ATTACH PARTITION操作，如果执行2次的话，数据会翻倍）
clickhouse-backup restore 备份名 --table 库名.表名  --data

3.5 删除备份 ​

  • 查看备份
bash
$ clickhouse-backup list
2024-11-07 11:49:12.884 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。
nginxlogs_nginx_access_data_bak_202411   7.15KiB   07/11/2024 03:42:07   local      regular
bak202411                                7.15KiB   07/11/2024 03:48:14   local      regular
2024-11-07 11:49:12.952 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed
$ clickhouse-backup list
2024-11-07 11:49:12.884 INF pkg/clickhouse/clickhouse.go:128 > clickhouse connection prepared: tcp://10.103.236.200:9000 run ping
。。。。
nginxlogs_nginx_access_data_bak_202411   7.15KiB   07/11/2024 03:42:07   local      regular
bak202411                                7.15KiB   07/11/2024 03:48:14   local      regular
2024-11-07 11:49:12.952 INF pkg/clickhouse/clickhouse.go:325 > clickhouse connection closed
  • 删除
bash
#删除nginxlogs_nginx_access_data_bak_202411
$ clickhouse-backup delete local nginxlogs_nginx_access_data_bak_202411
#删除nginxlogs_nginx_access_data_bak_202411
$ clickhouse-backup delete local nginxlogs_nginx_access_data_bak_202411

  • 删除shadow下的临时备份文件 ​

bash
$ clickhouse-backup  clean
$ clickhouse-backup  clean

3.6 远程备份 ​

bash
#创建远程备份
$ clickhouse-backup  update

#下载远程备份到本地
$ clickhouse-backup  download

#恢复远程备份
$ clickhouse-backup  restore_remote
#创建远程备份
$ clickhouse-backup  update

#下载远程备份到本地
$ clickhouse-backup  download

#恢复远程备份
$ clickhouse-backup  restore_remote