Clickhouse备份恢复_clickhouse-client方式backup_restore命令备份恢复的使用介绍

最新推荐文章于 2024-06-20 13:01:51 发布

lusklusklusk

最新推荐文章于 2024-06-20 13:01:51 发布

阅读量583

点赞数 10

分类专栏： Clickhouse 文章标签： clickhouse

本文链接：https://blog.csdn.net/lusklusklusk/article/details/139781360

版权

Clickhouse 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

Clickhouse备份恢复也可以使用clickhouse-client的backup和restore命令，参见https://clickhouse.com/docs/en/operations/backup#command-summary

clickhouse-client的backup命令备份出来的备份包的内容和开源工具clichouse-backup备份出来的备份包的内容类似，应该都是物理备份。clickhouse-client的backup命令备份出来的备份包，也可以拿到异机进行恢复，直接把备份包放到异机配置的system.disks备份目录下面后，再在异机下面直接执行restore database dbname from Disk(‘backupdirname’, ‘backupfilename’)命令就可以正常恢复了。oracle异机恢复如果异机和备份源机器目录一致的话可以顺利的按顺序执行restore spfile和restore controlfile和restore database，oracle异机恢复如果异机和备份源机器目录不一致则需要执行catalog backuppiece和newname for datafile 1 to ’ /datafile/system01.dbf’这种，但是clickhouse-client的异机恢复不需要像oracle一样执行catalog backuppiece。

备份实验
在服务器FStachDEV2上做备份

[root@FStachDEV2 ~]# mkdir /backups/
[root@FStachDEV2 ~]# chmod 766 /backups/
[root@FStachDEV2 ~]# vim /etc/clickhouse-server/config.d/backup_disk.xml
<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/backups/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/backups/</allowed_path>
    </backups>
</clickhouse>

[root@FStachDEV2 ~]# systemctl restart clickhouse-server.service

[root@FStachDEV2 ~]# clickhouse-client

FStachDEV2 :) select name,path,type from system.disks;
   ┌─name────┬─path─────────────────────┬─type──┐
1. │ backups │ /backups/                │ Local │
2. │ default │ /chdata/clickhouse/data/ │ Local │
   └─────────┴──────────────────────────┴───────┘

FStachDEV2 :) create database lukestest1;

FStachDEV2 :) CREATE TABLE lukestest1.table_mergetree (id String,create_time datetime) ENGINE = MergeTree() ORDER BY id;
FStachDEV2 :) insert into lukestest1.table_mergetree (id ,create_time) values ('1','2020-11-17'),('2','2020-11-17'),('3','2020-11-17'),('4','2020-11-17') ('5','2020-11-17'),('6','2020-11-17'),('7','2020-11-17'),('8','2020-11-17'),('9','2020-11-17'),('10','2020-11-17'),('11','2020-11-17'),('12','2020-11-17');

FStachDEV2 :) CREATE TABLE lukestest1.table2_mergetree (id String,create_time datetime) ENGINE = MergeTree() ORDER BY id;
FStachDEV2 :) insert into lukestest1.table2_mergetree (id ,create_time) values ('1','2020-11-17'),('2','2020-11-17'),('3','2020-11-17'),('4','2020-11-17') ('5','2020-11-17'),('6','2020-11-17'),('7','2020-11-17'),('8','2020-11-17'),('9','2020-11-17'),('10','2020-11-17'),('11','2020-11-17'),('12','2020-11-17')

FStachDEV2 :) backup database lukestest1 to Disk('backups', 'lukestest1.zip');
   ┌─id───────────────────────────────────┬─status─────────┐
1. │ db2dba04-0b7a-473b-b88a-d8deee77b2cf │ BACKUP_CREATED │
   └──────────────────────────────────────┴────────────────┘

FStachDEV2 :) SELECT id,name,base_backup_name,status,error,start_time,end_time FROM system.backups;
   ┌─id───────────────────────────────────┬─name──────────────────────────────┬─base_backup_name─┬─status───┬─error─┬──────────start_time─┬────────────end_time─┐
1. │ d75ee6c1-8178-4e44-ae0c-6d64aeb79bb7 │ Disk('backups', 'lukestest1.zip') │                  │ RESTORED │       │ 2024-06-05 03:03:08 │ 2024-06-05 03:03:08 │
   └──────────────────────────────────────┴───────────────────────────────────┴──────────────────┴──────────┴───────┴─────────────────────┴─────────────────────┘

解压备份包，查看备份目录和文件的信息

[root@FStachDEV2]# cd /backups/
[root@FStachDEV2 backups]# ll
-rw-r-----. 1 clickhouse clickhouse 4846 Jun  5 02:57 lukestest1.zip

[root@FStachDEV2 backups]# unzip lukestest1.zip
Archive:  2.zip
  inflating: metadata/lukestest1.sql
  inflating: metadata/lukestest1/table_mergetree.sql
  inflating: metadata/lukestest1/table2_mergetree.sql
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/metadata_version.txt
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/serialization.json
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/default_compression_codec.txt
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/count.txt
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/columns.txt
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/checksums.txt
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/data.bin
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/data.cmrk3
  inflating: data/lukestest1/table2_mergetree/all_1_1_0/primary.cidx
  inflating: .backup

[root@FStachDEV2 backups]# ll
drwxr-xr-x. 3 root       root         23 Jun  5 03:05 data
-rw-r-----. 1 clickhouse clickhouse 4846 Jun  5 02:57 lukestest1.zip
drwxr-xr-x. 3 root       root         44 Jun  5 03:05 metadata
[root@FStachDEV2 backups]# ll data
total 0
drwxr-xr-x. 3 root root 29 Jun  5 03:05 lukestest1
[root@FStachDEV2 backups]# ll data/lukestest1
total 0
drwxr-xr-x. 3 root root 22 Jun  5 03:05 table2_mergetree
[root@FStachDEV2 backups]# ll data/lukestest1/table2_mergetree
total 4
drwxr-xr-x. 2 root root 4096 Jun  5 03:05 all_1_1_0
[root@FStachDEV2 backups]# ll data/lukestest1/table2_mergetree/all_1_1_0
total 36
----------. 1 root root 259 Dec 31  1979 checksums.txt
----------. 1 root root  72 Dec 31  1979 columns.txt
----------. 1 root root   2 Dec 31  1979 count.txt
----------. 1 root root  93 Dec 31  1979 data.bin
----------. 1 root root  58 Dec 31  1979 data.cmrk3
----------. 1 root root  10 Dec 31  1979 default_compression_codec.txt
----------. 1 root root   1 Dec 31  1979 metadata_version.txt
----------. 1 root root  38 Dec 31  1979 primary.cidx
----------. 1 root root 158 Dec 31  1979 serialization.json
[root@FStachDEV2 backups]# ll metadata
total 4
drwxr-xr-x. 2 root root 59 Jun  5 03:05 lukestest1
----------. 1 root root 86 Dec 31  1979 lukestest1.sql
[root@FStachDEV2 backups]# ll metadata/lukestest1
total 8
----------. 1 root root 187 Dec 31  1979 table2_mergetree.sql
----------. 1 root root 186 Dec 31  1979 table_mergetree.sql

本机恢复实验，可以恢复
在服务器FStachDEV2上把数据库和表对应的文件改名，导致表无法查询，再恢复

FStachDEV2 :) select name,data_path,metadata_path from system.databases where name in ('lukestest1');
   ┌─name───────┬─data_path──────────────────────┬─metadata_path───────────────────────────────────────────────────────────┐
1. │ lukestest1 │ /chdata/clickhouse/data/store/ │ /chdata/clickhouse/data/store/314/314036bc-c3de-4628-8f4b-435f9340da52/ │
   └────────────┴────────────────────────────────┴─────────────────────────────────────────────────────────────────────────┘

FStachDEV2 :) select database,name,data_paths,metadata_path from system.tables where database in ('lukestest1') order by 1;
   ┌─database───┬─name─────────────┬─data_paths──────────────────────────────────────────────────────────────────┬─metadata_path───────────────────────────────────────────────────────────────────────────────┐
1. │ lukestest1 │ table2_mergetree │ ['/chdata/clickhouse/data/store/858/858e6ec8-69f7-4a1b-852e-380f757939ca/'] │ /chdata/clickhouse/data/store/314/314036bc-c3de-4628-8f4b-435f9340da52/table2_mergetree.sql │
2. │ lukestest1 │ table_mergetree  │ ['/chdata/clickhouse/data/store/667/66733022-fe6e-4753-9a11-9237f987f457/'] │ /chdata/clickhouse/data/store/314/314036bc-c3de-4628-8f4b-435f9340da52/table_mergetree.sql  │
   └────────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────┘

进入数据库元数据目录/chdata/clickhouse/data/store/314/314036bc-c3de-4628-8f4b-435f9340da52/，把两表的元数据文件改名

[root@FStachDEV2 314]# cd  /chdata/clickhouse/data/store/314/314036bc-c3de-4628-8f4b-435f9340da52/
[root@FStachDEV2 314036bc-c3de-4628-8f4b-435f9340da52]# ll
total 8
-rw-r-----. 1 clickhouse clickhouse 172 Jun  5 02:50 table2_mergetree.sql
-rw-r-----. 1 clickhouse clickhouse 172 Jun  5 02:49 table_mergetree.sql
[root@FStachDEV2 314036bc-c3de-4628-8f4b-435f9340da52]# mv table2_mergetree.sql table2_mergetree.sql.bak
[root@FStachDEV2 314036bc-c3de-4628-8f4b-435f9340da52]# mv table_mergetree.sql table_mergetree.sql.bak

进入表table2_mergetree的目录/chdata/clickhouse/data/store/858，把表的数据目录改名

[root@FStachDEV2 314036bc-c3de-4628-8f4b-435f9340da52]# cd /chdata/clickhouse/data/store/858/858e6ec8-69f7-4a1b-852e-380f757939ca/
[root@FStachDEV2 858e6ec8-69f7-4a1b-852e-380f757939ca]# ll
drwxr-x---. 2 clickhouse clickhouse 4096 Jun  5 02:50 all_1_1_0
drwxr-x---. 2 clickhouse clickhouse    6 Jun  5 02:50 detached
-rw-r-----. 1 clickhouse clickhouse    1 Jun  5 02:50 format_version.txt
[root@FStachDEV2 858e6ec8-69f7-4a1b-852e-380f757939ca]# ll all_1_1_0
total 36
-rw-r-----. 1 clickhouse clickhouse 259 Jun  5 02:50 checksums.txt
-rw-r-----. 1 clickhouse clickhouse  72 Jun  5 02:50 columns.txt
-rw-r-----. 1 clickhouse clickhouse   2 Jun  5 02:50 count.txt
-rw-r-----. 1 clickhouse clickhouse  93 Jun  5 02:50 data.bin
-rw-r-----. 1 clickhouse clickhouse  58 Jun  5 02:50 data.cmrk3
-rw-r-----. 1 clickhouse clickhouse  10 Jun  5 02:50 default_compression_codec.txt
-rw-r-----. 1 clickhouse clickhouse   1 Jun  5 02:50 metadata_version.txt
-rw-r-----. 1 clickhouse clickhouse  38 Jun  5 02:50 primary.cidx
-rw-r-----. 1 clickhouse clickhouse 158 Jun  5 02:50 serialization.json
[root@FStachDEV2 858e6ec8-69f7-4a1b-852e-380f757939ca]# cd ..
[root@FStachDEV2 858]# mv 858e6ec8-69f7-4a1b-852e-380f757939ca 858e6ec8-69f7-4a1b-852e-380f757939ca.bak

进入表table_mergetree的目录/chdata/clickhouse/data/store/667，把表的数据目录改名

[root@FStachDEV2 858e6ec8-69f7-4a1b-852e-380f757939ca]# cd /chdata/clickhouse/data/store/667/66733022-fe6e-4753-9a11-9237f987f457
[root@FStachDEV2 66733022-fe6e-4753-9a11-9237f987f457]# ll
total 8
drwxr-x---. 2 clickhouse clickhouse 4096 Jun  5 02:50 all_1_1_0
drwxr-x---. 2 clickhouse clickhouse    6 Jun  5 02:49 detached
-rw-r-----. 1 clickhouse clickhouse    1 Jun  5 02:49 format_version.txt
[root@FStachDEV2 66733022-fe6e-4753-9a11-9237f987f457]# ll all_1_1_0/
total 36
-rw-r-----. 1 clickhouse clickhouse 259 Jun  5 02:50 checksums.txt
-rw-r-----. 1 clickhouse clickhouse  72 Jun  5 02:50 columns.txt
-rw-r-----. 1 clickhouse clickhouse   2 Jun  5 02:50 count.txt
-rw-r-----. 1 clickhouse clickhouse  93 Jun  5 02:50 data.bin
-rw-r-----. 1 clickhouse clickhouse  58 Jun  5 02:50 data.cmrk3
-rw-r-----. 1 clickhouse clickhouse  10 Jun  5 02:50 default_compression_codec.txt
-rw-r-----. 1 clickhouse clickhouse   1 Jun  5 02:50 metadata_version.txt
-rw-r-----. 1 clickhouse clickhouse  38 Jun  5 02:50 primary.cidx
-rw-r-----. 1 clickhouse clickhouse 158 Jun  5 02:50 serialization.json
[root@FStachDEV2 66733022-fe6e-4753-9a11-9237f987f457]# cd ..
[root@FStachDEV2 667]# mv 66733022-fe6e-4753-9a11-9237f987f457 66733022-fe6e-4753-9a11-9237f987f457.bak
[root@FStachDEV2 667]#


[root@FStachDEV2 667]# systemctl start clickhouse-server
[root@FStachDEV2 667]# clickhouse-client

FStachDEV2 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ lukestest1         │
5. │ system             │
   └────────────────────┘

FStachDEV2 :) select *  from lukestest1.table2_mergetree;
Received exception from server (version 24.4.1):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Unknown table expression identifier 'lukestest1.table2_mergetree' in scope SELECT * FROM lukestest1.table2_mergetree. (UNKNOWN_TABLE)

FStachDEV2 :) select *  from lukestest1.table_mergetree;
Elapsed: 0.001 sec.
Received exception from server (version 24.4.1):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Unknown table expression identifier 'lukestest1.table_mergetree' in scope SELECT * FROM lukestest1.table_mergetree. (UNKNOWN_TABLE)

FStachDEV2 :) restore database lukestest1 from Disk('backups', 'lukestest1.zip');
   ┌─id───────────────────────────────────┬─status───┐
1. │ d75ee6c1-8178-4e44-ae0c-6d64aeb79bb7 │ RESTORED │
   └──────────────────────────────────────┴──────────┘

FStachDEV2 :) select *  from lukestest1.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 │
 5. │ 2  │ 2020-11-17 00:00:00 │
 6. │ 3  │ 2020-11-17 00:00:00 │
 7. │ 4  │ 2020-11-17 00:00:00 │
 8. │ 5  │ 2020-11-17 00:00:00 │
 9. │ 6  │ 2020-11-17 00:00:00 │
10. │ 7  │ 2020-11-17 00:00:00 │
11. │ 8  │ 2020-11-17 00:00:00 │
12. │ 9  │ 2020-11-17 00:00:00 │
    └────┴─────────────────────┘

FStachDEV2 :) select *  from lukestest1.table2_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 │
 5. │ 2  │ 2020-11-17 00:00:00 │
 6. │ 3  │ 2020-11-17 00:00:00 │
 7. │ 4  │ 2020-11-17 00:00:00 │
 8. │ 5  │ 2020-11-17 00:00:00 │
 9. │ 6  │ 2020-11-17 00:00:00 │
10. │ 7  │ 2020-11-17 00:00:00 │
11. │ 8  │ 2020-11-17 00:00:00 │
12. │ 9  │ 2020-11-17 00:00:00 │
    └────┴─────────────────────┘

异机恢复实验，可以恢复
把服务器FStachDEV2的备份直接拷贝到服务器FStachDEV3就可以直接恢复(虽然拷贝到FStachDEV3后在FStachDEV3上查询system.backups没有结果，但是可以恢复)

root@FStachDEV3:~# mkdir /backups/
root@FStachDEV3:~# chmod 766 /backups/
root@FStachDEV3:~# cat /etc/clickhouse-server/config.d/backup_disk.xml
<clickhouse>
    <storage_configuration>
        <disks>
            <backups>
                <type>local</type>
                <path>/backups/clickhouse/</path>
            </backups>
        </disks>
    </storage_configuration>
    <backups>
        <allowed_disk>backups</allowed_disk>
        <allowed_path>/backups/clickhousedir/</allowed_path>
    </backups>
</clickhouse>

root@FStachDEV3:~# systemctl restart clickhouse-server.service

root@FStachDEV3:~# clickhouse-client

FStachDEV3 :) select name,path,type from system.disks;
   ┌─name────┬─path─────────────────┬─type──┐
1. │ backups │ /backups/clickhouse/ │ Local │
2. │ default │ /var/lib/clickhouse/ │ Local │
   └─────────┴──────────────────────┴───────┘

FStachDEV3 :) select * from system.backups;
0 rows in set. Elapsed: 0.002 sec.

root@FStachDEV3:~# scp root@FStachDEV2:/backups/lukestest1.zip /backups/clickhouse/
root@FStachDEV3:~# clickhouse-client

FStachDEV3 :) select * from system.backups;
0 rows in set. Elapsed: 0.002 sec.

FStachDEV3 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ system             │
   └────────────────────┘

FStachDEV3 :) restore database lukestest1 from Disk('backups','lukestest1.zip');
   ┌─id───────────────────────────────────┬─status───┐
1. │ 6a37bf36-e1a0-4b6f-8513-cd32865deec8 │ RESTORED │
   └──────────────────────────────────────┴──────────┘
FStachDEV3 :) show databases;
   ┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default            │
3. │ information_schema │
4. │ lukestest1         │
5. │ system             │
   └────────────────────┘

FStachDEV3 :) use lukestest1;

FStachDEV3 :) show tables;
   ┌─name─────────────┐
1. │ table2_mergetree │
2. │ table_mergetree  │
   └──────────────────┘



FStachDEV3 :) select * from table2_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 │
 5. │ 2  │ 2020-11-17 08:00:00 │
 6. │ 3  │ 2020-11-17 08:00:00 │
 7. │ 4  │ 2020-11-17 08:00:00 │
 8. │ 5  │ 2020-11-17 08:00:00 │
 9. │ 6  │ 2020-11-17 08:00:00 │
10. │ 7  │ 2020-11-17 08:00:00 │
11. │ 8  │ 2020-11-17 08:00:00 │
12. │ 9  │ 2020-11-17 08:00:00 │
    └────┴─────────────────────┘

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 │
 5. │ 2  │ 2020-11-17 08:00:00 │
 6. │ 3  │ 2020-11-17 08:00:00 │
 7. │ 4  │ 2020-11-17 08:00:00 │
 8. │ 5  │ 2020-11-17 08:00:00 │
 9. │ 6  │ 2020-11-17 08:00:00 │
10. │ 7  │ 2020-11-17 08:00:00 │
11. │ 8  │ 2020-11-17 08:00:00 │
12. │ 9  │ 2020-11-17 08:00:00 │
    └────┴─────────────────────┘

个人备份脚本(历史备份不会删除，所以代码里面需要加上一段删除30天之前的备份代码)

root@DDLACHDBDEV001:~# cat /root/script/clickhouse-client_backup.sh
#/bin/bash
backupdate=`date +%Y%m%d`
deletedate=`date -d '30 days ago' +%Y%m%d`

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


clickhouse-client -h 127.0.0.1 -u default --password '123456' --port 9000 -q "backup database etl to Disk('backups','etl.zip');">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
clickhouse-client -h 127.0.0.1 -u default --password '123456' --port 9000 -q "backup database shared to Disk('backups','shared.zip');">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log

echo `date`>>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log
echo "Backup Completed!">>/root/clickhouse_backuplog/clickhouse_backup_$backupdate.log

mkdir /mnt/datadomaindir/clickhouse_backup/Stage/ODSONESCRCHSTG1/$backupdate

mv /var/lib/docker/volumes/root_clickhouse_backup/_data/etl.zip /mnt/datadomaindir/clickhouse_backup/Stage/ODSONESCRCHSTG1/$backupdate/etl.zip
mv /var/lib/docker/volumes/root_clickhouse_backup/_data/shared.zip /mnt/datadomaindir/clickhouse_backup/Stage/ODSONESCRCHSTG1/$backupdate/shared.zip

rm -rf /mnt/datadomaindir/clickhouse_backup/Stage/ODSONESCRCHSTG1/$deletedate

lusklusklusk

关注

10
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Clickhouse备份恢复_clickhouse-client方式backup_restore命令备份恢复的使用介绍

Clickhouse备份恢复也可以使用clickhouse-client的backup和restore命令,clickhouse-client的backup命令备份出来的备份包的内容和开源工具clichouse-backup备份出来的备份包的内容类似，应该都是物理备份。也可以拿到异机进行恢复，直接把备份包放到异机配置的system.disks备份目录下面后，再在异机下面直接执行restore database dbname from Disk()...
复制链接

扫一扫