Clickhouse备份恢复_clickhouse-backup方式备份恢复的使用介绍

于 2024-06-18 19:30:59 发布
阅读量856 收藏 10
点赞数 20
分类专栏： Clickhouse 文章标签： clickhouse
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lusklusklusk/article/details/139781448
版权 
https://clickhouse.com/docs/zh/operations/backup
https://github.com/Altinity/clickhouse-backup?tab=readme-ov-file#readme

clichouse-backup备份的总结
1、clichouse-backup备份的方式是物理备份
2、clichouse-backup只能在数据库本机运行备份，在远程异机去备份目标机器的clickhouse数据库的话，只能备份元数据
3、clichouse-backup备份文件名在执行clichouse-backup create backupname备份语句时自定义
4、clichouse-backup配置文件设置了backups_to_keep_local=3和backups_to_keep_remote=15的话，备份的时候本机clickhouse数据目录会有一个backup目录，这个目录只保留最新3天的备份文件，之前的历史备份会被自动删除，且备份时会把备份复制一份到远程目录，远程目录的备份文件保留15天，之前的历史备份会被自动删除。所以配置了这两个参数的话不用像mysql的innobackupex一样再在备份脚本里面写删除历史备份的脚本
5、实验测试过，clichouse-backup能备份单节点也能备份集群节点，单节点和集群节点都能正常恢复
6、可以异地恢复，只需要把备份文件拷贝到目标端的默认备份目录，目标端执行clickhouse-backup list就可以看到源端拷贝过来的备份了

clickhouse-copier
将数据从一个群集中的表复制到另一个（或相同）群集中的表
个人感觉clickhouse-copier是clickhouse官方提供的一个数据迁移工具，用于多个集群之间的数据迁移，做备份的话，差点意思

使用root用户在clickhouse的每个节点上执行如下命令来安装clickhouse-backup工具

root@DDLACHDBDEV002:~# wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.4.2/clickhouse-backup_2.4.2_amd64.deb
root@DDLACHDBDEV002:~# dpkg -i clickhouse-backup_2.4.2_amd64.deb
root@DDLACHDBDEV002:~# whereis clickhouse-backup
clickhouse-backup: /usr/bin/clickhouse-backup /etc/clickhouse-backup
DDLACHDBDEV002 :) select name,path,type from system.disks;
Query id: a1ec3402-1a73-42f4-8752-81c59f1b1888
┌─name────┬─path─────────────────────┬─type──┐
│ default │ /chdata/clickhouse/data/ │ local │
└─────────┴──────────────────────────┴───────┘

root@DDLACHDBDEV002:~# vim /etc/clickhouse-backup/config.yml
general:
  remote_storage: sftp   # 通过sftp上传到远程服务器的话设置为sftp,否则为none
  max_file_size: 1099511627776 #设置备份
  disable_progress_bar: false
  backups_to_keep_local: 7   # 本地备份的个数，大于7则自动删除旧的备份，默认为0，不删除备份
  log_level: info
  allow_empty_backups: false
clickhouse:
  username: default    # 本地clickhouse的连接参数
  password: "888888"
  host: localhost
  port: 9000
  disk_mapping: {""}
  skip_tables:
  - system.*
  - default.*
  - 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
sftp:
  address: "127.0.0.1" 
  username: "root"  
  password: "123456" 
  port: 22
  key: ""
  path: "/clickhousebackup" 
  concurrency: 1
  compression_format: none     
  debug: false

备注：关于disk_mapping参数的解释如下，不是说设置disk_mapping: {“数据目录”：“备份的存放目录”}这样来配置备份的存放目录，之前这样配置过，发现备份的存放目录里面只有元数据而没有数据文件

# CLICKHOUSE_DISK_MAPPING, use this mapping when your 'system.disks' are different between the source and destination clusters during backup and restore process
# The format for this env variable is "disk_name1:disk_path1,disk_name2:disk_path2". For YAML please continue using map syntax

DDLACHDBDEV001 :) select name,path from system.disks;
┌─name────┬─path─────────────────────┐
│ default │ /chdata/clickhouse/data/ │
└─────────┴──────────────────────────┘

查看当前配置文件

root@DDLACHDBDEV002:~# clickhouse-backup print-config

查看可备份的表

root@DDLACHDBDEV002:~# clickhouse-backup --config /etc/clickhouse-backup/config.yml tables

创建备份

root@DDLACHDBDEV002:~# clickhouse-backup create nodexx_clickhouse_backup_yyyymmdd

备份以目录形式存在，该备份目录默认存放在数据库目录的backup子目录下，即/chdata/clickhouse/data/backup,备份目录名称自己定义，本例为nodexx_clickhouse_backup_yyyymmdd

拷贝备份到备份目录

root@DDLACHDBDEV002:~# clickhouse-backup upload nodexx_clickhouse_backup_yyyymmdd

备份恢复测试1

以下7张表在1-4节点的信息
lukestest1.table_mergetree20231031–节点1,2,3,4都是4条记录
lukestest1.table_ReplicatedMergeTree20231031–节点1-2是6条记录，节点3-4是2条记录
lukestest1.table_Distributed_ReplicatedMergeTree20231031–节点1,2,3,4都是8条记录
lukestest1.table_mergetree–节点1,2,3是0条记录，节点4是4条记录
lukestest1.table_mergetree2–节点2是4条记录，节点1,3,4是0条记录
lukestest1.table_Distributed_ReplicatedMergeTree1–节点1,2,3,4都是8条记录
lukestest1.table_ReplicatedMergeTree1–节点1,2,3,4都是4条记录

节点1的信息

DDLACHDBDEV001 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1','sentry');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
│ sentry     │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV001 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1','sentry') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
│ sentry     │ errors_local                                  │ ['/chdata/clickhouse/data/store/181/181f6904-a0c2-4c12-976c-7504f435b68c/'] │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/errors_local.sql                                  │

节点2的信息

DDLACHDBDEV002 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1','sentry');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
│ sentry     │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV002 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1','sentry') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
│ sentry     │ errors_local                                  │ ['/chdata/clickhouse/data/store/2e4/2e4b60b2-92b7-4863-a92d-0af4308de3ee/'] │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/errors_local.sql                                  │

节点3的信息

DDLACHDBDEV003 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1','sentry');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
│ sentry     │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV003 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1','sentry') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
│ sentry     │ errors_local                                  │ ['/chdata/clickhouse/data/store/b9c/b9ca82a9-c989-439c-a3c1-3027aea0d3e5/'] │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/errors_local.sql                                  │

节点4的信息

DDLACHDBDEV004 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1','sentry');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
│ sentry     │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV004 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1','sentry') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
│ sentry     │ errors_local                                  │ ['/chdata/clickhouse/data/store/efe/efe99291-0919-4d4e-8487-7433db620412/'] │ /chdata/clickhouse/data/store/24a/24a5aa06-54b5-4606-8c27-5e3adfd11049/errors_local.sql                                  │

节点1备份并上传备份到指定目录
clickhouse-backup create node01_clickhousebackup_20231101
clickhouse-backup upload node01_clickhousebackup_20231101
节点2备份并上传备份到指定目录
clickhouse-backup create node02_clickhousebackup_20231101
clickhouse-backup upload node02_clickhousebackup_20231101
节点3备份并上传备份到指定目录
clickhouse-backup create node03_clickhousebackup_20231101
clickhouse-backup upload node03_clickhousebackup_20231101
节点4备份并上传备份到指定目录
clickhouse-backup create node04_clickhousebackup_20231101
clickhouse-backup upload node04_clickhousebackup_20231101

节点1关机删除 lukestest1 库的元数据库和里面所有表的元数据

lukestest1  /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/']
table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/']
table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/']
table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/']
table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/']
table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/']
table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/']

节点2开机删除

drop database lukestest1
truncate table sentry.errors_local

节点3开机删除

drop database lukestest1
drop table sentry.errors_local

节点4开机删除

drop table lukestest1.table_mergetree20231031
drop table lukestest1.table_Distributed_ReplicatedMergeTree20231031
drop table sentry.errors_local

然后节点1开启数据库服务，发现show databases有lukestest1,但是 use lukestest1后执行show tables看不到table了
然后节点2重启数据库服务，shwo database查不到lukestest1了，查不到且被drop的sentry.errors_local,lukestest1库对应的/chdata/clickhouse/data/store/ae1子目录不存在了,表sentry.errors_local对应的元数据文件还在，数据文件目录也存在不过变成空的了
然后节点2重启数据库服务，shwo database查不到lukestest1了，查不到且被drop的sentry.errors_local,lukestest1库对应的/chdata/clickhouse/data/store/ae1子目录不存在了,表sentry.errors_local对应的元数据文件和数据文件子目录都不存在了
然后节点4不动数据库服务，查不到别drop的3张表了，3表对应的元数据文件不存在了但是对应的数据文件还在且大小没有变

恢复，必须在clickhouse服务启动的情况下才能运行，否则报错如下
2023/11/01 07:00:40.726544 error clickhouse connection ping: tcp://localhost:9000 return error: dial tcp 127.0.0.1:9000: connect: connection refused logger=clickhouse
2023/11/01 07:00:40.726634 error can’t connect to clickhouse: dial tcp 127.0.0.1:9000: connect: connection refused

节点1 clickhouse-backup restore node01_clickhousebackup_20231101
节点2 clickhouse-backup restore node02_clickhousebackup_20231101
节点3 clickhouse-backup restore node03_clickhousebackup_20231101
节点4 clickhouse-backup restore node04_clickhousebackup_20231101

只有节点1有如下报错，其他2,3,4节点都正常

2023/11/01 07:55:03.283638  warn can't create table 'lukestest1.table_ReplicatedMergeTree1': code: 253, message: Replica /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01/replicas/DDLACHDBDEV001 already exists, will try again backup=node01_clickhousebackup_20231101 operation=restore
2023/11/01 07:55:03.307959  warn can't create table 'lukestest1.table_ReplicatedMergeTree20231031': code: 253, message: Replica /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01/replicas/DDLACHDBDEV001 already exists, will try again backup=node01_clickhousebackup_20231101 operation=restore
...
2023/11/01 07:55:05.704752 error can't create table `lukestest1`.`table_ReplicatedMergeTree1`: code: 253, message: Replica /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01/replicas/DDLACHDBDEV001 already exists after 53 times, please check your schema dependencies

节点1尝试了拷贝对应table的metadata sql到指定目录再关闭clickhouse服务再执行如下
drop table lukestest1.table_ReplicatedMergeTree1 sync
drop table lukestest1.table_ReplicatedMergeTree20231031 sync

节点1再执行clickhouse-backup restore node01_clickhousebackup_20231101，还是有报错

lukestest1.table_ReplicatedMergeTree1 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse

恢复后1-4节点
节点1-2的lukestest1.table_ReplicatedMergeTree1和lukestest1.table_ReplicatedMergeTree20231031光有表结构，没有数据库，其他表没有问题

发现两个问题
这种方式的恢复，对drop有效，但是对于ReplicatedMergeTree引擎的表进行truncate和数据文件被物理删除的情况，有一定的问题

备份恢复测试2
只truncate两个节点DDLACHDBDEV001和DDLACHDBDEV002对应的ReplicatedMergeTree引擎的表

恢复过程导入全库报错

root@DDLACHDBDEV001:/clickhousebackup# clickhouse-backup restore  node01_clickhousebackup_20231102
2023/11/02 09:55:48.217933  info replication_in_progress status = [{InProgress:0}] logger=clickhouse
2023/11/02 09:55:48.269645  warn lukestest1.table_ReplicatedMergeTree1 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
2023/11/02 09:55:48.269690  info done                      backup=node01_clickhousebackup_20231102 operation=restore table=lukestest1.table_ReplicatedMergeTree1
2023/11/02 09:55:48.274139  warn lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse

恢复过程只导入指定表且只导入数据不导表结构也报错

root@DDLACHDBDEV001:/clickhousebackup# clickhouse-backup restore  node01_clickhousebackup_20231102 -t lukestest1.table_ReplicatedMergeTree20231031 -d
2023/11/02 09:53:56.691073  warn lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse

DDLACHDBDEV001 :) select database,table,zookeeper_path,replica_name,replica_path from system.replicas;
┌─database───┬─table────────────────────────────┬─zookeeper_path────────────────────────────────────────────┬─replica_name───┬
│ lukestest1 │ table_ReplicatedMergeTree1       │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01│ DDLACHDBDEV001 │

root@DDLACHDBDEV001:/clickhousebackup# /chdata/zookeeper/apache-zookeeper-3.7.1-bin/bin/zkCli.sh -server DDLACHDBDEV001:2181
[zk: DDLACHDBDEV001:2181(CONNECTED) 1]deleteall '/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01';

root@DDLACHDBDEV001:/clickhousebackup# clickhouse-backup restore  node01_clickhousebackup_20231102 -t lukestest1.table_ReplicatedMergeTree1
2023/11/02 10:39:24.520902  info replication_in_progress status = [{InProgress:0}] logger=clickhouse
2023/11/02 10:39:24.530972  info done                      backup=node01_clickhousebackup_20231102 operation=restore table=lukestest1.table_ReplicatedMergeTree1
2023/11/02 10:39:24.531005  info done                      backup=node01_clickhousebackup_20231102 duration=59ms operation=restore
2023/11/02 10:39:24.531023  info done                      backup=node01_clickhousebackup_20231102 operation=restore

DDLACHDBDEV001 :) select database,table,zookeeper_path,replica_name,replica_path from system.replicas where table='table_ReplicatedMergeTree20231031';
┌─database───┬─table─────────────────────────────┬─zookeeper_path─────────────────────────────────────────────┬─replica_name───┬─replica_path───────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_ReplicatedMergeTree20231031 │ /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01 │ DDLACHDBDEV001 │ /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01/replicas/DDLACHDBDEV001 │
└────────────┴───────────────────────────────────┴────────────────────────────────────────────────────────────┴────────────────┴────────────────────────────────────────────────────────────────────────────────────┘

root@DDLACHDBDEV001:/clickhousebackup# /chdata/zookeeper/apache-zookeeper-3.7.1-bin/bin/zkCli.sh -server DDLACHDBDEV001:2181
[zk: DDLACHDBDEV001:2181(CONNECTED) 0] deleteall '/clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01';

root@DDLACHDBDEV001:/clickhousebackup# clickhouse-backup restore  node01_clickhousebackup_20231102 -t lukestest1.table_ReplicatedMergeTree20231031
2023/11/02 10:42:58.874671  info replication_in_progress status = [{InProgress:0}] logger=clickhouse
2023/11/02 10:42:58.884327  info done                      backup=node01_clickhousebackup_20231102 operation=restore table=lukestest1.table_ReplicatedMergeTree20231031
2023/11/02 10:42:58.884625  info done                      backup=node01_clickhousebackup_20231102 duration=55ms operation=restore
2023/11/02 10:42:58.884749  info done                      backup=node01_clickhousebackup_20231102 operation=restore

以上只恢复了节点1，节点2需要同样的操作

DDLACHDBDEV002 :) select database,table,zookeeper_path,replica_name from system.replicas where table in ('table_ReplicatedMergeTree1','table_ReplicatedMergeTree20231031');
┌─database───┬─table─────────────────────────────┬─zookeeper_path─────────────────────────────────────────────┬─replica_name───┐
│ lukestest1 │ table_ReplicatedMergeTree1        │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01 │ DDLACHDBDEV002 │
│ lukestest1 │ table_ReplicatedMergeTree20231031 │ /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01 │ DDLACHDBDEV002 │
└────────────┴───────────────────────────────────┴────────────────────────────────────────────────────────────┴────────────────┘

root@DDLACHDBDEV002:~# /chdata/zookeeper/apache-zookeeper-3.7.1-bin/bin/zkCli.sh -server DDLACHDBDEV002:2181
[zk: DDLACHDBDEV002:2181(CONNECTED) 0] deleteall '/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01';
[zk: DDLACHDBDEV002:2181(CONNECTED) 1] deleteall '/clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01';

root@DDLACHDBDEV002:~# clickhouse-backup restore  node02_clickhousebackup_20231102 -t lukestest1.table_ReplicatedMergeTree20231031
2023/11/02 10:49:16.935904  info replication_in_progress status = [{InProgress:0}] logger=clickhouse
2023/11/02 10:49:16.944193  info done                      backup=node02_clickhousebackup_20231102 operation=restore table=lukestest1.table_ReplicatedMergeTree20231031
2023/11/02 10:49:16.944236  info done                      backup=node02_clickhousebackup_20231102 duration=71ms operation=restore
2023/11/02 10:49:16.944254  info done                      backup=node02_clickhousebackup_20231102 operation=restore
root@DDLACHDBDEV002:~# clickhouse-backup restore  node02_clickhousebackup_20231102 -t lukestest1.table_ReplicatedMergeTree1
2023/11/02 10:49:36.745037  info replication_in_progress status = [{InProgress:0}] logger=clickhouse
2023/11/02 10:49:36.755868  info done                      backup=node02_clickhousebackup_20231102 operation=restore table=lukestest1.table_ReplicatedMergeTree1
2023/11/02 10:49:36.756064  info done                      backup=node02_clickhousebackup_20231102 duration=72ms operation=restore
2023/11/02 10:49:36.756093  info done                      backup=node02_clickhousebackup_20231102 operation=restore

到zookeeper里面删除ReplicatedMergeTree引擎的表的zookeeper_path后，再clickhouse-backup restore可以正常恢复了

备份恢复测试3
总结：发现这种删除表元数据库目录(就是数据库元数据目录)和表数据库的情况，大概没法直接restore恢复整个库，而是需要restore -t参数来对一张张表进行恢复
物理删除两个节点DDLACHDBDEV001和DDLACHDBDEV002对应的库和所有引擎的表

DDLACHDBDEV001 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV001 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
└────────────┴───────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

DDLACHDBDEV002 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1');
┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
│ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/ │
└────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘
DDLACHDBDEV002 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1') order by 1;
┌─database───┬─name──────────────────────────────────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree1        │ ['/chdata/clickhouse/data/store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree1.sql        │
│ lukestest1 │ table_Distributed_ReplicatedMergeTree20231031 │ ['/chdata/clickhouse/data/store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_Distributed_ReplicatedMergeTree20231031.sql │
│ lukestest1 │ table_ReplicatedMergeTree1                    │ ['/chdata/clickhouse/data/store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree1.sql                    │
│ lukestest1 │ table_ReplicatedMergeTree20231031             │ ['/chdata/clickhouse/data/store/600/600470de-9db1-419f-9009-35a552616c56/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_ReplicatedMergeTree20231031.sql             │
│ lukestest1 │ table_mergetree                               │ ['/chdata/clickhouse/data/store/137/137bb490-f192-4482-b747-01c0939e36ad/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree.sql                               │
│ lukestest1 │ table_mergetree2                              │ ['/chdata/clickhouse/data/store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree2.sql                              │
│ lukestest1 │ table_mergetree20231031                       │ ['/chdata/clickhouse/data/store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/'] │ /chdata/clickhouse/data/store/ae1/ae1d8050-fdd6-4274-8eb8-c7e5270dd09c/table_mergetree20231031.sql                       │
└────────────┴───────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

节点1上做备份
clickhouse-backup create node01_clickhousebackup_20231103_01
clickhouse-backup upload node01_clickhousebackup_20231103_01
节点2上做备份
clickhouse-backup create node02_clickhousebackup_20231103_01
clickhouse-backup upload node02_clickhousebackup_20231103_01

关闭节点1和节点2
systemctl stop clickhouse-server

删除节点1和节点2数据库的元数据路径和表的存储路径

启动节点1和节点2
systemctl start clickhouse-server

节点1上做恢复
clickhouse-backup restore node01_clickhousebackup_20231103_01
节点2上做恢复
clickhouse-backup restore node02_clickhousebackup_20231103_01
节点1有如下报错

2023/11/03 08:43:06.505938  warn lukestest1.table_ReplicatedMergeTree1 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
2023/11/03 08:43:06.530428  warn lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse

节点2有如下报错

2023/11/03 08:43:16.874915  warn can't create table 'lukestest1.table_ReplicatedMergeTree1': code: 253, message: Replica /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01/replicas/DDLACHDBDEV002 already exists, will try again backup=node02_clickhousebackup_20231103_01 operation=restore
2023/11/03 08:43:16.907099  warn can't create table 'lukestest1.table_ReplicatedMergeTree20231031': code: 253, message: Replica /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01/replicas/DDLACHDBDEV002 already exists, will try again backup=node02_clickhousebackup_20231103_01 operation=restore

恢复后发现节点1有表lukestest1.table_ReplicatedMergeTree1和lukestest1.table_ReplicatedMergeTree20231031但是没有数据
恢复后发现节点2没有表lukestest1.table_ReplicatedMergeTree1和lukestest1.table_ReplicatedMergeTree20231031

节点1节点2查询ReplicatedMergeTree引擎表

DDLACHDBDEV001 :) select database,table,zookeeper_path,replica_name from system.replicas where table in ('table_ReplicatedMergeTree1','table_ReplicatedMergeTree20231031');
┌─database───┬─table─────────────────────────────┬─zookeeper_path─────────────────────────────────────────────┬─replica_name───┐
│ lukestest1 │ table_ReplicatedMergeTree1        │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01 │ DDLACHDBDEV001 │
│ lukestest1 │ table_ReplicatedMergeTree20231031 │ /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01 │ DDLACHDBDEV001 │
└────────────┴───────────────────────────────────┴────────────────────────────────────────────────────────────┴────────────────┘
DDLACHDBDEV002 :) select database,table,zookeeper_path,replica_name from system.replicas where table in ('table_ReplicatedMergeTree1','table_ReplicatedMergeTree20231031');
0 rows in set. Elapsed: 0.003 sec.

节点1执行

[zk: DDLACHDBDEV001:2181(CONNECTED) 0]deleteall '/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01';
[zk: DDLACHDBDEV001:2181(CONNECTED) 1]deleteall '/clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01';
DDLACHDBDEV001 :) drop table lukestest1.table_ReplicatedMergeTree1;
DDLACHDBDEV001 :) drop table lukestest1.table_ReplicatedMergeTree20231031;

节点1执行

clickhouse-backup restore  node01_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree1 lukestest1.table_ReplicatedMergeTree20231031
报错2023/11/03 08:58:11.438212 error can't create table `lukestest1`.`table_ReplicatedMergeTree1`: code: 57, message: Directory for table data store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/ already exists after 1 times, please check your schema dependencies

节点1进入/chdata/clickhouse/data/store/f9c/和/chdata/clickhouse/data/store/600，把下面的子目录改名

节点1执行

root@DDLACHDBDEV001:/chdata/clickhouse/data/store/600# clickhouse-backup restore  node01_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree1 lukestest1.table_ReplicatedMergeTree20231031
2023/11/03 09:03:58.402157 error can't create table `lukestest1`.`table_ReplicatedMergeTree1`: code: 57, message: Mapping for table with UUID=f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b already exists. It happened due to UUID collision, most likely because some not random UUIDs were manually specified in CREATE queries. after 1 times

节点1执行

[zk: DDLACHDBDEV001:2181(CONNECTED) 0]deleteall '/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b';
[zk: DDLACHDBDEV001:2181(CONNECTED) 1]deleteall '/clickhouse/tables/600470de-9db1-419f-9009-35a552616c56';

节点1执行

root@DDLACHDBDEV001:/chdata/clickhouse/data/store/600# clickhouse-backup restore  node01_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree1 lukestest1.table_ReplicatedMergeTree20231031

只恢复了表lukestest1.table_ReplicatedMergeTree1，说明-t后面只能有一张表名
节点1执行

root@DDLACHDBDEV001:/chdata/clickhouse/data/store/600# clickhouse-backup restore  node01_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree20231031

恢复了lukestest1.table_ReplicatedMergeTree20231031

不过进入节点2发现lukestest1.table_ReplicatedMergeTree1和lukestest1.table_ReplicatedMergeTree20231031两表还是没有恢复，需要继续在节点2执行恢复

节点2执行

root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree1 
报错2023/11/03 09:16:16.892373  warn lukestest1.table_ReplicatedMergeTree1 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree20231031
报错2023/11/03 09:16:30.533921  warn lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse

节点2执行

DDLACHDBDEV002 :) select database,table,zookeeper_path,replica_name from system.replicas where table in ('table_ReplicatedMergeTree1','table_ReplicatedMergeTree20231031');
┌─database───┬─table─────────────────────────────┬─zookeeper_path─────────────────────────────────────────────┬─replica_name───┐
│ lukestest1 │ table_ReplicatedMergeTree1        │ /clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01 │ DDLACHDBDEV002 │
│ lukestest1 │ table_ReplicatedMergeTree20231031 │ /clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01 │ DDLACHDBDEV002 │
└────────────┴───────────────────────────────────┴────────────────────────────────────────────────────────────┴────────────────┘
[zk: DDLACHDBDEV002:2181(CONNECTED) 0] deleteall '/clickhouse/tables/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/01';
[zk: DDLACHDBDEV002:2181(CONNECTED) 1] deleteall '/clickhouse/tables/600470de-9db1-419f-9009-35a552616c56/01';

节点2执行

root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree1 
root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_ReplicatedMergeTree20231031

两表在节点2都恢复好了

检查后，发现节点1的mergetree引擎的表只有表结构没有数据，节点2执行如下

root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_mergetree20231031
root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231103_01 -t lukestest1.table_mergetree2

至此最终彻底恢复节点1和节点2

备份恢复测试4
两个节点DDLACHDBDEV001和DDLACHDBDEV002只drop对应的ReplicatedMergeTree，MergeTree，distributed引擎的表

节点1上做备份
clickhouse-backup create node01_clickhousebackup_20231106
clickhouse-backup upload node01_clickhousebackup_20231106
节点2上做备份
clickhouse-backup create node02_clickhousebackup_20231106
clickhouse-backup upload node02_clickhousebackup_20231106

节点1和节点2都去drop ReplicatedMergeTree，MergeTree，distributed引擎的表

节点1执行恢复
root@DDLACHDBDEV001:# clickhouse-backup restore node01_clickhousebackup_20231106

节点2执行恢复
root@DDLACHDBDEV001:# clickhouse-backup restore node02_clickhousebackup_20231106

被drop的表都找回来了

备份恢复测试5
两个节点DDLACHDBDEV001和DDLACHDBDEV002只drop database

节点1上做备份

clickhouse-backup create node01_clickhousebackup_20231106
clickhouse-backup upload node01_clickhousebackup_20231106

节点2上做备份

clickhouse-backup create node02_clickhousebackup_20231106
clickhouse-backup upload node02_clickhousebackup_20231106

节点1执行删除db操作

DDLACHDBDEV001 :) drop database lukestest1;

节点2执行删除db操作

DDLACHDBDEV002 :) drop database lukestest1;

节点1执行恢复

root@DDLACHDBDEV001:# clickhouse-backup restore  node01_clickhousebackup_20231106
报错如下
2023/11/06 03:19:52.782595  warn can't create table 'lukestest1.table_ReplicatedMergeTree1': code: 57, message: Directory for table data store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:19:52.784587  warn can't create table 'lukestest1.table_ReplicatedMergeTree20231031': code: 57, message: Directory for table data store/600/600470de-9db1-419f-9009-35a552616c56/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:19:52.787136  warn can't create table 'lukestest1.table_mergetree': code: 57, message: Directory for table data store/137/137bb490-f192-4482-b747-01c0939e36ad/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:19:52.788726  warn can't create table 'lukestest1.table_mergetree2': code: 57, message: Directory for table data store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:19:52.790026  warn can't create table 'lukestest1.table_mergetree20231031': code: 57, message: Directory for table data store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:20:10.561241  warn can't create table 'lukestest1.table_Distributed_ReplicatedMergeTree1': code: 57, message: Directory for table data store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore
2023/11/06 03:20:10.603421  warn can't create table 'lukestest1.table_Distributed_ReplicatedMergeTree20231031': code: 57, message: Directory for table data store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/ already exists, will try again backup=node01_clickhousebackup_20231106 operation=restore

节点2执行恢复

root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231106
报错如下
2023/11/06 03:21:10.186776  warn can't create table 'lukestest1.table_ReplicatedMergeTree1': code: 57, message: Directory for table data store/f9c/f9ce3729-2815-4a9b-bdf5-7f24cd6ab01b/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:10.189623  warn can't create table 'lukestest1.table_ReplicatedMergeTree20231031': code: 57, message: Directory for table data store/600/600470de-9db1-419f-9009-35a552616c56/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:10.193292  warn can't create table 'lukestest1.table_mergetree': code: 57, message: Directory for table data store/137/137bb490-f192-4482-b747-01c0939e36ad/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:10.195192  warn can't create table 'lukestest1.table_mergetree2': code: 57, message: Directory for table data store/2ea/2eae7c5a-a3d0-4346-8e6c-e6c4bf9c1605/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:10.199158  warn can't create table 'lukestest1.table_mergetree20231031': code: 57, message: Directory for table data store/dbd/dbdeab84-8486-4357-b1f5-5b8c727331c1/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:16.342629  warn can't create table 'lukestest1.table_Distributed_ReplicatedMergeTree1': code: 57, message: Directory for table data store/966/9663a43b-dcf5-4b2e-b9b0-dbecb1def8bc/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore
2023/11/06 03:21:16.393215  warn can't create table 'lukestest1.table_Distributed_ReplicatedMergeTree20231031': code: 57, message: Directory for table data store/e4b/e4bcc136-193a-4ad8-a56e-c123a80a683f/ already exists, will try again backup=node02_clickhousebackup_20231106 operation=restore

登陆节点1和节点2，发现db lukestest1存在了，但是里面没有一张表

节点1继续执行恢复
root@DDLACHDBDEV001:# clickhouse-backup restore node01_clickhousebackup_20231106
没有报错，正常恢复了

节点2继续执行恢复

root@DDLACHDBDEV002:# clickhouse-backup restore  node02_clickhousebackup_20231106
除了ReplicatedMergeTree引擎的表有报错，其他表正常恢复
2023/11/06 05:04:34.050680  warn lukestest1.table_ReplicatedMergeTree1 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
2023/11/06 05:04:34.050705  info done                      backup=node02_clickhousebackup_20231106 operation=restore table=lukestest1.table_ReplicatedMergeTree1
2023/11/06 05:04:34.090644  warn lukestest1.table_ReplicatedMergeTree20231031 skipped cause system.replicas entry already exists and replication in progress from another replica logger=clickhouse
2023/11/06 05:04:34.090847  info done                      backup=node02_clickhousebackup_20231106 operation=restore table=lukestest1.table_ReplicatedMergeTree20231031

登陆节点1和节点2，发现db lukestest1里面的表都有了，且数据也恢复出来了
select count(*) from tables

常用命令
clickhouse-backup list --显示备份信息
clickhouse-backup download backupname --下载远程的某个备份备份到本地目录
clickhouse-backup restore backupname --使用某个备份来恢复
clickhouse-backup restore backupname -t dbname.tablename1,dbname.tablename2 --使用某个备份来恢复指定的表

clickhouse-backup是物理备份的理解

DDLACHDBDEV001 :) select database,name,engine,data_paths,metadata_path from system.tables where database='lukestest1';
┌─database───┬─name──────────────────────────────────┬─engine──────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ lukestest1 │ table_Distributed_ReplicatedMergeTree │ Distributed         │ ['/chdata/clickhouse/data/store/16c/16c4621c-63c6-4ad4-9c6e-d16d93aa797e/'] │ /chdata/clickhouse/data/store/67b/67bcf47f-0041-4e2f-af59-a6cc54c5d732/table_Distributed_ReplicatedMergeTree.sql │
│ lukestest1 │ table_ReplicatedMergeTree             │ ReplicatedMergeTree │ ['/chdata/clickhouse/data/store/a13/a13ff214-816b-4d01-9600-2a71f70853e6/'] │ /chdata/clickhouse/data/store/67b/67bcf47f-0041-4e2f-af59-a6cc54c5d732/table_ReplicatedMergeTree.sql             │
└────────────┴───────────────────────────────────────┴─────────────────────┴─────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

数据库中数据文件路径

root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/store/a13/a13ff214-816b-4d01-9600-2a71f70853e6/
total 4
drwxr-x--- 5 clickhouse clickhouse  88 Apr  9 07:54 ./
drwxr-x--- 3 clickhouse clickhouse  50 Apr  9 07:52 ../
drwxr-x--- 2 clickhouse clickhouse 231 Apr  9 07:53 202011_0_0_0/
drwxr-x--- 2 clickhouse clickhouse 231 Apr  9 07:54 202011_1_1_0/
drwxr-x--- 2 clickhouse clickhouse   6 Apr  9 07:52 detached/
-rw-r----- 1 clickhouse clickhouse   1 Apr  9 07:52 format_version.txt
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/store/a13/a13ff214-816b-4d01-9600-2a71f70853e6/202011_1_1_0/
total 40
drwxr-x--- 2 clickhouse clickhouse 231 Apr  9 07:54 ./
drwxr-x--- 5 clickhouse clickhouse  88 Apr  9 07:54 ../
-r--r----- 4 clickhouse clickhouse 258 May 14 09:43 checksums.txt
-r--r----- 4 clickhouse clickhouse  72 May 14 09:43 columns.txt
-r--r----- 4 clickhouse clickhouse   1 May 14 09:43 count.txt
-r--r----- 4 clickhouse clickhouse  73 May 14 09:43 data.bin
-r--r----- 4 clickhouse clickhouse  80 May 14 09:43 data.mrk3
-r--r----- 4 clickhouse clickhouse  10 May 14 09:43 default_compression_codec.txt
-r--r----- 4 clickhouse clickhouse   1 May 14 09:43 metadata_version.txt
-r--r----- 4 clickhouse clickhouse   8 May 14 09:43 minmax_create_time.idx
-r--r----- 4 clickhouse clickhouse   4 May 14 09:43 partition.dat
-r--r----- 4 clickhouse clickhouse   6 May 14 09:43 primary.idx

备份目录中数据文件信息
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/backup/20240514/shadow/lukestest1/table_ReplicatedMergeTree/
total 0
drwxr-x--- 3 clickhouse clickhouse 21 May 14 06:00 ./
drwxr-x--- 3 clickhouse clickhouse 39 May 14 06:00 ../
drwxr-x--- 4 clickhouse clickhouse 46 May 14 06:00 default/
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/backup/20240514/shadow/lukestest1/table_ReplicatedMergeTree/default/
total 0
drwxr-x--- 4 clickhouse clickhouse  46 May 14 06:00 ./
drwxr-x--- 3 clickhouse clickhouse  21 May 14 06:00 ../
drwxr-x--- 2 root       root       231 May 14 06:00 202011_0_0_0/
drwxr-x--- 2 root       root       231 May 14 06:00 202011_1_1_0/
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/backup/20240514/shadow/lukestest1/table_ReplicatedMergeTree/default/202011_1_1_0/
total 40
drwxr-x--- 2 root       root       231 May 14 06:00 ./
drwxr-x--- 4 clickhouse clickhouse  46 May 14 06:00 ../
-r--r----- 4 clickhouse clickhouse 258 May 14 09:43 checksums.txt
-r--r----- 4 clickhouse clickhouse  72 May 14 09:43 columns.txt
-r--r----- 4 clickhouse clickhouse   1 May 14 09:43 count.txt
-r--r----- 4 clickhouse clickhouse  73 May 14 09:43 data.bin
-r--r----- 4 clickhouse clickhouse  80 May 14 09:43 data.mrk3
-r--r----- 4 clickhouse clickhouse  10 May 14 09:43 default_compression_codec.txt
-r--r----- 4 clickhouse clickhouse   1 May 14 09:43 metadata_version.txt
-r--r----- 4 clickhouse clickhouse   8 May 14 09:43 minmax_create_time.idx
-r--r----- 4 clickhouse clickhouse   4 May 14 09:43 partition.dat
-r--r----- 4 clickhouse clickhouse   6 May 14 09:43 primary.idx

数据库中元数据文件信息
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/store/67b/67bcf47f-0041-4e2f-af59-a6cc54c5d732/
total 8
drwxr-x--- 2 clickhouse clickhouse  92 Apr  9 07:54 ./
drwxr-x--- 3 clickhouse clickhouse  50 Apr  9 07:50 ../
-rw-r----- 1 clickhouse clickhouse 207 Apr  9 07:54 table_Distributed_ReplicatedMergeTree.sql
-rw-r----- 1 clickhouse clickhouse 267 Apr  9 07:52 table_ReplicatedMergeTree.sql

备份目录中元数据文件信息
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/backup/20240514/metadata
total 4
drwxr-x--- 5 clickhouse clickhouse   53 May 14 06:00 ./
drwxr-x--- 4 clickhouse clickhouse   77 May 14 06:00 ../
drwxr-x--- 2 clickhouse clickhouse  223 May 14 06:00 actuals/
drwxr-x--- 2 clickhouse clickhouse   94 May 14 06:00 lukestest1/
drwxr-x--- 2 clickhouse clickhouse 4096 May 14 06:00 sentry/
root@DDLACHDBDEV001:~# ll /chdata/clickhouse/data/backup/20240514/metadata/lukestest1/
total 8
drwxr-x--- 2 clickhouse clickhouse  94 May 14 06:00 ./
drwxr-x--- 5 clickhouse clickhouse  53 May 14 06:00 ../
-rw-r--r-- 1 clickhouse clickhouse 393 May 14 06:00 table_Distributed_ReplicatedMergeTree.json
-rw-r--r-- 1 clickhouse clickhouse 562 May 14 06:00 table_ReplicatedMergeTree.json

异机恢复,可以正常恢复，只要把备份文件拷贝到目标端的默认备份目录，目标端执行clickhouse-backup list就可以看到源端拷贝过来的备份了
源端

[root@FSTachDEV2 ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@FSTachDEV2 ~]# clickhouse-client
FSTachDEV2 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ lukestest1         │
5. │ lukestest2         │
6. │ system             │
   └────────────────────┘
FSTachDEV2 :) use lukestest1;
FSTachDEV2 :) show tables;
   ┌─name──────────────┐
1. │ table88_mergetree │
2. │ table_mergetree   │
   └───────────────────┘
FSTachDEV2 :) select * from table_mergetree;
   ┌─id─┬─────────create_time─┐
1. │ 1  │ 2020-11-17 00:00:00 │
2. │ 10 │ 2020-11-17 00:00:00 │
3. │ 11 │ 2020-11-17 00:00:00 │
4. │ 12 │ 2020-11-17 00:00:00 │
   └────┴─────────────────────┘
FSTachDEV2 :) select * from table88_mergetree;
   ┌─id───┬─────────create_time─┐
1. │ 88   │ 1969-12-31 16:00:00 │
2. │ 888  │ 1969-12-31 16:00:00 │
3. │ 8888 │ 1969-12-31 16:00:00 │
   └──────┴─────────────────────┘
FSTachDEV2 :) use lukestest2;
FSTachDEV2 :) show tables;
   ┌─name──────────────┐
1. │ table99_mergetree │
2. │ table_mergetree   │
   └───────────────────┘
FSTachDEV2 :) select * from table_mergetree;
   ┌─id─┬─────────create_time─┐
1. │ 5  │ 2020-11-17 00:00:00 │
2. │ 6  │ 2020-11-17 00:00:00 │
3. │ 7  │ 2020-11-17 00:00:00 │
4. │ 8  │ 2020-11-17 00:00:00 │
5. │ 9  │ 2020-11-17 00:00:00 │
   └────┴─────────────────────┘
FSTachDEV2 :) select * from table99_mergetree;
   ┌─id──┬─────────create_time─┐
1. │ 99  │ 1999-09-09 00:00:00 │
2. │ 999 │ 1999-09-09 00:00:00 │
   └─────┴─────────────────────┘
FSTachDEV2 :) select name,path from system.disks;
   ┌─name────┬─path─────────────────────┐
1. │ backups │ /backups/                │
2. │ default │ /chdata/clickhouse/data/ │
   └─────────┴──────────────────────────┘
[root@FSTachDEV2 ~]# clickhouse-backup create FSTachDEV2_20240618
[root@FSTachDEV2 ~]# ll /chdata/clickhouse/data/backup/
total 0
drwxr-x---. 4 clickhouse clickhouse 54 Jun 17 22:56 FSTachDEV2_20240618
[root@FSTachDEV2 ~]# clickhouse-backup list
FSTachDEV2_20240618   4.20KiB   18/06/2024 05:56:40   local

目标端

root@FSTachDEV3:~# cat /etc/issue
Ubuntu 22.04.1 LTS \n \l
root@FSTachDEV3:~# clickhouse-client
FSTachDEV3 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ system             │
   └────────────────────┘
FSTachDEV3 :) select name,path from system.disks;
   ┌─name────┬─path─────────────────┐
1. │ backups │ /backups/clickhouse/ │
2. │ default │ /var/lib/clickhouse/ │
   └─────────┴──────────────────────┘
root@FSTachDEV3:~# clickhouse-backup list
root@FSTachDEV3:~# scp -r root@FSTachDEV2:/chdata/clickhouse/data/backup /var/lib/clickhouse/
root@FSTachDEV3:~# clickhouse-backup list
FSTachDEV2_20240618   4.20KiB   18/06/2024 05:56:40   local
root@FSTachDEV3:~# clickhouse-backup restore FSTachDEV2_20240618
2024/06/18 06:11:43.832414  info done                      backup=FSTachDEV2_20240618 operation=restore table=lukestest1.table88_mergetree
2024/06/18 06:11:43.833837  info done                      backup=FSTachDEV2_20240618 operation=restore table=lukestest1.table_mergetree
2024/06/18 06:11:43.835309  info done                      backup=FSTachDEV2_20240618 operation=restore table=lukestest2.table99_mergetree
2024/06/18 06:11:43.836581  info done                      backup=FSTachDEV2_20240618 operation=restore table=lukestest2.table_mergetree
2024/06/18 06:11:43.836776  info done                      backup=FSTachDEV2_20240618 duration=110ms operation=restore
2024/06/18 06:11:43.836906  info done                      backup=FSTachDEV2_20240618 operation=restore
root@FSTachDEV3:~# clickhouse-client
FSTachDEV3 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ lukestest1         │
5. │ lukestest2         │
6. │ system             │
   └────────────────────┘
FSTachDEV3 :) use lukestest1;
FSTachDEV3 :) show tables;
   ┌─name──────────────┐
1. │ table88_mergetree │
2. │ table_mergetree   │
   └───────────────────┘
FSTachDEV3 :) select * from table_mergetree;
   ┌─id─┬─────────create_time─┐
1. │ 1  │ 2020-11-17 08:00:00 │
2. │ 10 │ 2020-11-17 08:00:00 │
3. │ 11 │ 2020-11-17 08:00:00 │
4. │ 12 │ 2020-11-17 08:00:00 │
   └────┴─────────────────────┘
FSTachDEV3 :) select * from table88_mergetree;
   ┌─id───┬─────────create_time─┐
1. │ 88   │ 1970-01-01 00:00:00 │
2. │ 888  │ 1970-01-01 00:00:00 │
3. │ 8888 │ 1970-01-01 00:00:00 │
   └──────┴─────────────────────┘
FSTachDEV3 :) use lukestest2;
FSTachDEV3 :) show tables;
   ┌─name──────────────┐
1. │ table99_mergetree │
2. │ table_mergetree   │
   └───────────────────┘
FSTachDEV3 :) select * from table_mergetree;
   ┌─id─┬─────────create_time─┐
1. │ 5  │ 2020-11-17 08:00:00 │
2. │ 6  │ 2020-11-17 08:00:00 │
3. │ 7  │ 2020-11-17 08:00:00 │
4. │ 8  │ 2020-11-17 08:00:00 │
5. │ 9  │ 2020-11-17 08:00:00 │
   └────┴─────────────────────┘
FSTachDEV3 :) select * from table99_mergetree;
   ┌─id──┬─────────create_time─┐
1. │ 99  │ 1999-09-09 07:00:00 │
2. │ 999 │ 1999-09-09 07:00:00 │
   └─────┴─────────────────────┘

个人备份脚本(配置了backups_to_keep_local和backups_to_keep_remote的话，代码里面的那段删除30天之前的备份代码就可以不用写了)
root@DDLACHDBDEV001:/usr/lib/systemd/system# cat /root/script/clickhouse_backup.sh

#!/bin/bash


backupdate=`date +%Y%m%d`
deletedate=`date -d '30 days ago' +%Y%m%d`
echo "  Begin to delete `date -d '30 days ago' +%Y%m%d` backup dir...">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
/bin/rm -rf /mnt/datadomaindir/clickhouse_backup/Dev/DDLACHDBDEV001/$deletedate
if [ $? -eq 0 ]; then
echo "  Delete `date -d '30 days ago' +%Y%m%d` backup dir successfully !">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
fi

echo " ">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
echo "++++++++++++++++++++++++++++++++++++++++++++++++">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
echo "  Begin to backup...">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log

clickhouse-backup create $backupdate >>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log 2>&1
clickhouse-backup upload $backupdate >>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log 2>&1
lusklusklusk
关注
  • 20
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
clickhouse-backup-1.4.5离线rpm安装包适应于centos
09-22
8. 恢复数据：如果需要从备份恢复，使用`clickhouse-backup restore [备份名称]`命令。这会将数据还原到Clickhouse数据库中。 9. 定期备份：为了确保数据安全，建议设置定时任务来定期执行备份操作。这可以通过`...
ansible-role-clickhouse-backup:部署Clickhouse备份工具的角色
05-20
角色：ClickHouse备份Ansible角色，用于管理安装和配置。角色变量列出和描述的可用变量以及默认值。依存关系没有任何。剧本范例 - hosts: all roles: - { role: ... clickhouse_backup_gcs_bucket: clickhouse-backup-b
Clickhouse学习笔记（15）—— Clickhouse备份
WHY的博客
11-14 1454
本文介绍了clickhouse的数据备份方法，包括手动备份和使用clickhouse-backup进行备份
clickHouse备份工具1.3.1：clickhouse-backup-linux-amd64.tar.gz
03-07
clickHouse备份工具1.3.1：clickhouse-backup-linux-amd64.tar.gz
2024年clickhouse的BACKUP RESTORE命令介绍_clickhouse-backup，醍醐灌顶
最新发布
2401_84975342的博客
05-13 695
无论是备份还是恢复，执行效率都非常高同压缩比备份，支持不同的备份目的地支持增量备份需要开放配置，操作性上不太友好集群备份到本地时，必须要选共享目录，如果每个分片单独备份，则无法进行数据汇总，存留于节点自身，与没有备份没什么区别，还不如多加副本数据恢复功能尚不太完备网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。需要这份系统化的资料的朋友，可以添加戳这里获取一个人可以走的很快，但一群人才能走的更远！
clickhouse使用clickhouse-backup来本地和远程备份文件
dair6的博客
02-08 5820
1.下载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
clickhouse-backup
lwei_998的专栏
08-21 871
clickhouse-backup -v Version: 1.0.0 Git Commit: 37f3bd78adec2aadc1de10f9323fe426a5e12dc4 Build Date: 2021-06-16 SELECT version() Query id: 20eab3e5-347d-42fe-82a2-03459476a023 ┌─version()─┐ │ 21.8.3.44 │ └───────────┘ # clickhouse-ba.
ClickHouse 备份恢复工具：CLICKHOUSE-BACKUP
tianjinsong的专栏
12-03 3742
https://www.cnblogs.com/xibuhaohao/p/13802693.html 回到顶部 官方网址： https://altinity.com/blog/introduction-to-clickhouse-backups-and-clickhouse-backup GitHub地址： https://github.com/AlexAkulov/clickhouse-backup 回到顶部 Limitations ClickHouse above 1.1.54390 i
clickhouse-backup数据备份
weixin_42820161的博客
07-11 2932
docker-compose启动clickhouse-backup并完成备份
Route-backup.rar_2800 router backup_route-back_路由备份
09-22
在"Route-backup.pdf"这份文档中，可能会详细介绍CISCO 2800路由器的备份步骤、最佳实践以及可能遇到的问题及其解决方案。阅读并理解这份文档对于任何负责CISCO路由器管理的IT人员来说都是极其有价值的。 总的来说...
clickhouse万能自动备份脚本
06-14
sh backup-ch.sh ip port user password db chdir(clickhouse安装目录) bak_dir(备份目录) 参数灵活配置。拥有7个参数。可满足多种场景。 支持crontab定时调度。 支持全库备份，备份压缩，节省服务器空间。 适用...
使用clickhouse-backup备份和恢复数据
04-12 1359
支持单表/全库备份支持备份上传到S3/FTP等存储支持单表/全库恢复本文对备份工具的使用做一些介绍。可以使用clickhouse-backup命令行工具进行备份和恢复操作。使用clickhoues-backup有几个前提条件需要能访问clickhoues的数据目录（如/var/lib/clickhoues, 在docker或k8s环境中，需要将数据卷挂载到同样的路径）需要有clickhouse账号，能访问clickhouse服务，执行一些sql。
clickhouse-backup 备份恢复
aldxy的博客
04-10 676
调整BACKUPS_TO_KEEP_REMOTE保留参数为24 ，因为当前设置BACKUPS_TO_KEEP_REMOTE保留3个不满足当前需求，1小时一个备份，24小时进行一次全备，那么至少需要保留24个。原因是 删除更改表，每个单独的部分会创建一堆具有新名称的部分，在这种情况下，用clickhouse备份来进行增量时，会有些问题。原因是：删除后不会立即删除，而是等待一段时间后删除，默认是8min， 当然也可以直接重启，也是可以的。在增量备份期间，如果修改表，删除表之类的。会扰乱增量备份的机制。
clickhouse-backup 备份恢复测试
Hehuyi_In的博客
03-23 4948
一、安装配置 1. 下载 https://github.com/AlexAkulov/clickhouse-backup/releases 2. 安装 [root@backup-db ~]# rpm -ivh clickhouse-backup-1.3.1-1.x86_64.rpm Preparing... ################################# [100%] Updating / installing... 1:clickhouse-backup-1.3.1-1 #
ClickHouse实时分析（十五）- ClickHouse的备份与恢复
程序园@大Null
04-11 6967
目录1. 手动实现备份及恢复1.1 创建备份路径1.2 执行备份命令1.3 将备份数据保存到其他路径1.4 恢复数据2. 使用clickhouse-backup2.1 上传并安装2.2 配置文件2.3 创建备份2.4 从备份恢复数据2.5 其他说明参考文献 1. 手动实现备份及恢复   ClickHouse 允许使用ALTER TABLE ... FREEZE PARTITION ... 查询以创建表分区的本地副本。这是利用硬链接（hardlink）到/var/lib/clickhouse/shadow/文
使用clickhouse-backup工具备份clickhouse数据库
gsls200808的专栏
02-16 1913
工具官网：https://github.com/AlexAkulov/clickhouse-backup/dockerhub工具官网：https://hub.docker.com/r/alexakulov/clickhouse-backup注意：这个工具只支持MergeTree 系列表引擎。
clickhouse的BACKUP/RESTORE命令介绍
禹鼎侯的博客
05-29 3169
无论是备份还是恢复，执行效率都非常高同压缩比备份，支持不同的备份目的地支持增量备份需要开放配置，操作性上不太友好集群备份到本地时，必须要选共享目录，如果每个分片单独备份，则无法进行数据汇总，存留于节点自身，与没有备份没什么区别，还不如多加副本数据恢复功能尚不太完备推荐一个零声学院免费教程，个人觉得老师讲得不错，分享给大家：[Linux，Nginx，ZeroMQ，MySQL，Redis，fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，
clickhouse-backup 使用示例
vkingnew 的技术博客
07-13 1515
功能介绍: 限制： 参考： https://github.com/AlexAkulov/clickhouse-backup
clickhouse-backup 备份
02-07
clickhouse-backup 是一个用于备份 ClickHouse 数据库的工具。它提供了一种简单、可靠的方式来备份和恢复 ClickHouse 数据库，保护您的数据免受损坏或丢失。 使用 clickhouse-backup，您可以备份整个 ClickHouse 数据库或者仅备份指定的表。它支持全量备份和增量备份，可以根据您的需求定期执行备份操作。备份的数据会被压缩和加密，以确保数据的安全性和节省存储空间。 clickhouse-backup 还提供了备份的恢复功能，您可以轻松地将备份数据恢复到 ClickHouse 数据库中。恢复过程快速且可靠，并且具有去重和并发优化功能，以提高恢复的效率。 除了备份和恢复功能，clickhouse-backup 还提供了其他有用的功能。例如，您可以使用它来检查备份的完整性，验证备份是否完整且没有错误。此外，clickhouse-backup 还具有导出和导入数据的功能，您可以将备份数据导出到其他位置或导入到其他 ClickHouse 数据库中。 总之，clickhouse-backup 是一个功能丰富且易于使用的备份工具，可以帮助您保护 ClickHouse 数据库中的数据，并在需要时轻松地恢复数据。它是 ClickHouse 用户的理想选择。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值