clickhouse-backup安装部署new(rpm安装)

环境：

OS:Centos 7

clickhouse-backup:2.6.5

1.下载clickhouse-backup
下载地址:https://github.com/Altinity/clickhouse-backup/releases

[root@localhost soft]#mkdir -p /soft/clickhouse-backup
[root@localhost soft]#cd /soft/clickhouse-backup
[root@localhost clickhouse-backup]#wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.6.5/clickhouse-backup-2.6.5-1.x86_64.rpm
[root@localhost clickhouse-backup]# rpm -ivh clickhouse-backup-2.6.5-1.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:clickhouse-backup-2.6.5-1        ################################# [100%]
[root@localhost clickhouse-backup]# which clickhouse-backup
/usr/bin/clickhouse-backup

[root@localhost ~]# clickhouse-backup -v
Version:         2.6.5
Git Commit:      68dcef9c2cfe1d88050082d6f56654156f819538
Build Date:      2024-12-31

[root@localhost clickhouse-backup]# pwd
/etc/clickhouse-backup

[root@localhost clickhouse-backup]# ls
config.yml.example

默认会生成一个参数样本

2.准备参数配置文件
[root@localhost clickhouse-backup]# cd /etc/clickhouse-backup
[root@localhost clickhouse-backup]# cp config.yml.example config.yml

修改配置文件

clickhouse:
    username: default
    password: "clickhouse123" ##修改的地方
    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: 2
    debug: false

3.查看可备份的表

clickhouse-backup tables --config=/etc/clickhouse-backup/config.yml
[root@localhost clickhouse-backup]# clickhouse-backup tables --config=/etc/clickhouse-backup/config.yml
2025-01-17 17:51:27.464 INF pkg/clickhouse/clickhouse.go:130 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-01-17 17:51:27.488 INF pkg/clickhouse/clickhouse.go:133 > clickhouse connection success: tcp://localhost:9000
2025-01-17 17:51:27.488 INF pkg/clickhouse/clickhouse.go:1080 > SELECT name, count(*) as is_present FROM system.settings WHERE name IN (?, ?) GROUP BY name with args [show_table_uuid_in_table_create_query_if_not_nil display_secrets_in_show_and_select]
2025-01-17 17:51:27.516 INF pkg/clickhouse/clickhouse.go:1082 > SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL','MaterializedPostgreSQL')
2025-01-17 17:51:27.520 INF pkg/clickhouse/clickhouse.go:1082 >    SELECT     countIf(name='data_path') is_data_path_present,     countIf(name='data_paths') is_data_paths_present,     countIf(name='uuid') is_uuid_present,     countIf(name='create_table_query') is_create_table_query_present,     countIf(name='total_bytes') is_total_bytes_present    FROM system.columns WHERE database='system' AND table='tables'  
2025-01-17 17:51:27.526 INF pkg/clickhouse/clickhouse.go:1082 > SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes   FROM system.tables WHERE is_temporary = 0 ORDER BY total_bytes DESC SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2025-01-17 17:51:27.626 INF pkg/clickhouse/clickhouse.go:1082 > SELECT metadata_path FROM system.tables WHERE database = 'system' AND metadata_path!='' LIMIT 1;
2025-01-17 17:51:27.630 INF pkg/clickhouse/clickhouse.go:1082 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-01-17 17:51:27.633 INF pkg/clickhouse/clickhouse.go:1082 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-01-17 17:51:27.638 INF pkg/clickhouse/clickhouse.go:1082 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
db_test.hxl_metric  3.18KiB  default  full
2025-01-17 17:51:27.648 INF pkg/clickhouse/clickhouse.go:327 > clickhouse connection closed

4.创建备份

[root@localhost clickhouse-backup]# clickhouse-backup create mybak20250117 --config=/etc/clickhouse-backup/config.yml
2025-01-17 17:56:15.879 INF pkg/clickhouse/clickhouse.go:130 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-01-17 17:56:15.881 INF pkg/clickhouse/clickhouse.go:133 > clickhouse connection success: tcp://localhost:9000
2025-01-17 17:56:15.881 INF pkg/clickhouse/clickhouse.go:1082 > SELECT metadata_path FROM system.tables WHERE database = 'system' AND metadata_path!='' LIMIT 1;
2025-01-17 17:56:15.886 INF pkg/clickhouse/clickhouse.go:1082 > SELECT name, engine FROM system.databases WHERE NOT match(name,'^(system|INFORMATION_SCHEMA|information_schema|_temporary_and_external_tables)$')
2025-01-17 17:56:15.890 INF pkg/clickhouse/clickhouse.go:1082 > SHOW CREATE DATABASE `db_test`
2025-01-17 17:56:15.893 INF pkg/clickhouse/clickhouse.go:1082 > SHOW CREATE DATABASE `default`
2025-01-17 17:56:15.911 INF pkg/clickhouse/clickhouse.go:1080 > SELECT name, count(*) as is_present FROM system.settings WHERE name IN (?, ?) GROUP BY name with args [show_table_uuid_in_table_create_query_if_not_nil display_secrets_in_show_and_select]
2025-01-17 17:56:15.918 INF pkg/clickhouse/clickhouse.go:1082 > SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL','MaterializedPostgreSQL')
2025-01-17 17:56:15.921 INF pkg/clickhouse/clickhouse.go:1082 >    SELECT     countIf(name='data_path') is_data_path_present,     countIf(name='data_paths') is_data_paths_present,     countIf(name='uuid') is_uuid_present,     countIf(name='create_table_query') is_create_table_query_present,     countIf(name='total_bytes') is_total_bytes_present    FROM system.columns WHERE database='system' AND table='tables'  
2025-01-17 17:56:15.927 INF pkg/clickhouse/clickhouse.go:1082 > SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes   FROM system.tables WHERE is_temporary = 0 ORDER BY total_bytes DESC SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2025-01-17 17:56:15.969 INF pkg/clickhouse/clickhouse.go:1082 > SELECT metadata_path FROM system.tables WHERE database = 'system' AND metadata_path!='' LIMIT 1;
2025-01-17 17:56:15.974 INF pkg/clickhouse/clickhouse.go:1082 > SELECT count() as cnt FROM system.columns WHERE database='system' AND table='functions' AND name='create_query' SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-01-17 17:56:15.978 INF pkg/clickhouse/clickhouse.go:1082 > SELECT name, create_query FROM system.functions WHERE create_query!=''
2025-01-17 17:56:15.984 INF pkg/clickhouse/clickhouse.go:1082 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-01-17 17:56:15.986 INF pkg/clickhouse/clickhouse.go:1082 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-01-17 17:56:15.991 INF pkg/clickhouse/clickhouse.go:1082 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-01-17 17:56:15.997 INF pkg/clickhouse/clickhouse.go:1082 > SELECT name FROM system.user_directories WHERE type='replicated'
2025-01-17 17:56:16.002 INF pkg/clickhouse/clickhouse.go:1082 > SELECT JSONExtractString(params,'path') AS access_path FROM system.user_directories WHERE type='local directory'
2025-01-17 17:56:16.005 INF pkg/clickhouse/clickhouse.go:1082 > SELECT metadata_path FROM system.tables WHERE database = 'system' AND metadata_path!='' LIMIT 1;
2025-01-17 17:56:16.042 INF pkg/backup/create.go:177 > done createBackupRBAC size=1.62KiB
2025-01-17 17:56:16.042 INF pkg/clickhouse/clickhouse.go:1080 > SELECT column, groupUniqArray(type) AS uniq_types FROM system.parts_columns WHERE active AND database=? AND table=? AND type NOT LIKE 'Enum%!((MISSING)%!'(MISSING) AND type NOT LIKE 'Tuple(%!'(MISSING) AND type NOT LIKE 'Nullable(Enum%!((MISSING)%!'(MISSING) AND type NOT LIKE 'Nullable(Tuple(%!'(MISSING) AND type NOT LIKE 'Array(Tuple(%!'(MISSING) AND type NOT LIKE 'Nullable(Array(Tuple(%!'(MISSING) GROUP BY column HAVING length(uniq_types) > 1 with args [db_test hxl_metric]
2025-01-17 17:56:16.060 INF pkg/clickhouse/clickhouse.go:1082 > ALTER TABLE `db_test`.`hxl_metric` FREEZE WITH NAME '623309416ae04a42959defd37cb394f5';
2025-01-17 17:56:16.097 INF pkg/clickhouse/clickhouse.go:1082 > ALTER TABLE `db_test`.`hxl_metric` UNFREEZE WITH NAME '623309416ae04a42959defd37cb394f5'
2025-01-17 17:56:16.112 INF pkg/clickhouse/clickhouse.go:1080 > SELECT mutation_id, command FROM system.mutations WHERE is_done=0 AND database=? AND table=? with args [db_test hxl_metric]
2025-01-17 17:56:16.117 INF pkg/backup/create.go:344 > done progress=8/133 table=db_test.hxl_metric
2025-01-17 17:56:16.117 INF pkg/clickhouse/clickhouse.go:1082 > SELECT value FROM `system`.`build_options` WHERE name='VERSION_DESCRIBE'
2025-01-17 17:56:16.120 INF pkg/backup/create.go:356 > done duration=241ms operation=createBackupLocal version=2.6.5
2025-01-17 17:56:16.121 INF pkg/clickhouse/clickhouse.go:327 > clickhouse connection closed

5.查看备份

[root@localhost data]# clickhouse-backup list --config=/etc/clickhouse-backup/config.yml
2025-01-21 08:47:18.351 INF pkg/clickhouse/clickhouse.go:130 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-01-21 08:47:18.353 INF pkg/clickhouse/clickhouse.go:133 > clickhouse connection success: tcp://localhost:9000
2025-01-21 08:47:18.354 INF pkg/clickhouse/clickhouse.go:1082 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-01-21 08:47:18.357 INF pkg/clickhouse/clickhouse.go:1082 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-01-21 08:47:18.363 INF pkg/clickhouse/clickhouse.go:1082 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
mybak20250117   6.86KiB    17/01/2025 09:56:16   local      regular
mybak20250121   15.37GiB   21/01/2025 00:46:08   local      regular
2025-01-21 08:47:18.388 INF pkg/clickhouse/clickhouse.go:327 > clickhouse connection closed

备份数据文件存放在clickhouse数据目录
[root@localhost backup]# pwd
/home/middle/clickhouse/data/backup
[root@localhost backup]# ls
mybak20250117

备份的文件如下:
[root@localhost mybak20250117]# ls -al
total 4
drwxr-xr-x 5 clickhouse clickhouse 71 Jan 17 17:56 .
drwxr-xr-x 3 clickhouse clickhouse 27 Jan 17 17:56 ..
drwxr-x--- 2 clickhouse clickhouse 102 Jan 17 17:56 access ##创建的用户和权限赋予
drwxr-x--- 3 clickhouse clickhouse 21 Jan 17 17:56 metadata ##表元数据信息
-rw-r----- 1 clickhouse clickhouse 689 Jan 17 17:56 metadata.json
drwxr-x--- 3 clickhouse clickhouse 21 Jan 17 17:56 shadow ##包含作为ALTER TABLE ... FREEZE操作结果的数据,硬链接

查看备份文件的大小

[root@localhost mybak20250121]# du -h ./ --max-depth=1
8.0K    ./access
16G     ./shadow
8.0K    ./metadata
16G     ./

备份文件大小与数据目录大小一致

[root@localhost data]# du -h ./ --max-depth=1
92K     ./preprocessed_configs
0       ./flags
0       ./dictionaries_lib
0       ./user_scripts
0       ./data
20K     ./metadata
0       ./metadata_dropped
16G     ./store
0       ./cores
92K     ./backup
4.0K    ./shadow
16G     ./

发现这个./backup目录存储空间没有体现出来

6.恢复
模拟删除表

[root@localhost clickhouse-backup]# clickhouse-client -h 192.168.1.134 -m -u hxl01 --password clickhouse123
localhost :) use db_test;
localhost :) show tables;

SHOW TABLES

Query id: cc029bd6-2760-4687-bb06-cfcb1177e3ff

   ┌─name───────┐
1. │ hxl_metric │
   └────────────┘
localhost :) drop table hxl_metric;

恢复表

[root@localhost mybak20250117]# clickhouse-backup restore mybak20250117 -table db_test.hxl_metric --config=/etc/clickhouse-backup/config.yml
2025-01-17 18:06:15.248 INF pkg/clickhouse/clickhouse.go:130 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-01-17 18:06:15.251 INF pkg/clickhouse/clickhouse.go:133 > clickhouse connection success: tcp://localhost:9000
2025-01-17 18:06:15.251 INF pkg/clickhouse/clickhouse.go:1082 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-01-17 18:06:15.254 INF pkg/clickhouse/clickhouse.go:1082 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2025-01-17 18:06:15.316 INF pkg/clickhouse/clickhouse.go:1082 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-01-17 18:06:15.369 INF pkg/clickhouse/clickhouse.go:1082 > SELECT engine FROM system.databases WHERE name = 'db_test'
2025-01-17 18:06:15.372 INF pkg/clickhouse/clickhouse.go:1082 > DROP TABLE IF EXISTS `db_test`.`hxl_metric` NO DELAY
2025-01-17 18:06:15.374 INF pkg/clickhouse/clickhouse.go:1082 > CREATE DATABASE IF NOT EXISTS `db_test`
2025-01-17 18:06:15.375 INF pkg/clickhouse/clickhouse.go:1082 > CREATE TABLE db_test.hxl_metric UUID 'b452f8c7-da2f-4546-a627-6fa89e6a6c6b' (`app` String, `block_qps` Int64, `count` Int64, `exception_qps` Int64, `gmt_create` DateTime DEFAULT now(), `gmt_modified` DateTime DEFAULT now(), `id` Int64, `machine_ip` Nullable(String), `pass_qps` Int64, `resource` String, `resource_code` Int64, `rt` Float64, `success_qps` Int64, `timestamp` DateTime DEFAULT now(), `month` Date DEFAULT now()) ENGINE = MergeTree PARTITION BY toYYYYMM(month) ORDER BY (timestamp, app, resource) SETTINGS index_granularity = 8192
2025-01-17 18:06:15.507 INF pkg/clickhouse/clickhouse.go:327 > clickhouse connection closed
2025-01-17 18:06:15.507 FTL cmd/clickhouse-backup/main.go:668 > error="can't create table `db_test`.`hxl_metric`: code: 57, message: Directory for table data store/b45/b452f8c7-da2f-4546-a627-6fa89e6a6c6b/ already exists after 1 times, please check your schema dependencies"

解决办法:
去掉 hxl_metric.json 中的UUID,该文件存在如下的备份目录:

[root@localhost db_test]# pwd
/home/middle/clickhouse/data/backup/mybak20250117/metadata/db_test
[root@localhost db_test]# ls
hxl_metric.json

把UUID部分删除掉(红色标注)

[root@localhost db_test]# more hxl_metric.json 
{
 "table": "hxl_metric",
 "database": "db_test",
 "parts": {
  "default": [
   {
    "name": "202501_1_2_1"
   },
   {
    "name": "202502_3_3_0"
   }
  ]
 },
 "query": "CREATE TABLE db_test.hxl_metric UUID 'b452f8c7-da2f-4546-a627-6fa89e6a6c6b' (`app` String, `block_qps` In
t64, `count` Int64, `exception_qps` Int64, `gmt_create` DateTime DEFAULT now(), `gmt_modified` DateTime DEFAULT now(
), `id` Int64, `machine_ip` Nullable(String), `pass_qps` Int64, `resource` String, `resource_code` Int64, `rt` Float
64, `success_qps` Int64, `timestamp` DateTime DEFAULT now(), `month` Date DEFAULT now()) ENGINE = MergeTree PARTITIO
N BY toYYYYMM(month) ORDER BY (timestamp, app, resource) SETTINGS index_granularity = 8192",
 "size": {
  "default": 4561
 },
 "total_bytes": 3255,
 "metadata_only": false
}

删除后再次执行恢复:
clickhouse-backup restore mybak20250117 -table db_test.hxl_metric --config=/etc/clickhouse-backup/config.yml

查看数据恢复情况

[root@localhost clickhouse-backup]# clickhouse-client -h 192.168.1.134 -m -u hxl01 --password clickhouse123
localhost :) use db_test;
localhost :) show tables;

SHOW TABLES

Query id: f9db6318-2cba-4faa-ab71-06daa91ef3c8

   ┌─name───────┐
1. │ hxl_metric │
   └────────────┘
   
localhost :) select count(1) from hxl_metric;

SELECT count(1)
FROM hxl_metric

Query id: 52f5fa67-8763-4f41-be85-8fd522e4a8d7

   ┌─count(1)─┐
1. │        3 │
   └──────────┘

恢复数据库
模拟删除数据库

[root@localhost clickhouse-backup]# clickhouse-client -h 192.168.1.134 -m -u hxl01 --password clickhouse123
localhost :) drop database db_test;

DROP DATABASE db_test

使用备份恢复数据库
clickhouse-backup restore mybak20250117 --config=/etc/clickhouse-backup/config.yml --drop

参数说明:
--drop:恢复前删除已经存在的对象

说明:

1.--data只恢复数据不重建表，多分片的集群上备份的文件恢复到单机需要该选项

clickhouse-backup restore mybak_p1_102 -table db_test.tb_test --config=/etc/clickhouse-backup/config.yml
clickhouse-backup restore mybak_p2_105 -table db_test.tb_test --data --config=/etc/clickhouse-backup/config.yml
clickhouse-backup restore mybak_p3_106 -table db_test.tb_test --data --config=/etc/clickhouse-backup/config.yml

2.更换表名(--restore-database-mapping两边一致):
clickhouse-backup restore mybak_p1_102 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test
clickhouse-backup restore mybak_p2_105 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test --data
clickhouse-backup restore mybak_p3_106 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test --data

3.更换库名和表名

clickhouse-backup restore mybak_p1_102 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test01
clickhouse-backup restore mybak_p2_105 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test01 --data
clickhouse-backup restore mybak_p3_106 --config=/etc/clickhouse-backup/config.yml --restore-table-mapping=tb_test:tb_test01 --restore-database-mapping=db_test:db_test01 --data