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,否则为nonemax_file_size: 1099511627776 #设置备份disable_progress_bar: falsebackups_to_keep_local: 7 # 本地备份的个数，大于7则自动删除旧的备份，默认为0，不删除备份log_level: infoallow_empty_backups: false
clickhouse:username: default # 本地clickhouse的连接参数password: "888888"host: localhostport: 9000disk_mapping: {""}skip_tables:- system.*- default.*- information_schema.*- INFORMATION_SCHEMA.*timeout: 5mfreeze_by_part: falsesecure: falseskip_verify: falsesync_replicated_tables: trueskip_sync_replica_timeouts: truelog_sql_queries: false
sftp:address: "127.0.0.1" username: "root" password: "123456" port: 22key: ""path: "/clickhousebackup" concurrency: 1compression_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=restoreDDLACHDBDEV001 :) 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/bashbackupdate=`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
fiecho " ">>/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.logclickhouse-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
)
