clickhourse-007之click-backup工具介绍

## 一、官方资料和使用说明: **参考文档资料如下:** https://github.com/AlexAkulov/clickhouse-backup https://segmentfault.com/a/1190000038604737 **使用限制:** 支持1.1.54390以上的ClickHouse 仅MergeTree系列表引擎 **此外,clickhouse-backup文档还包含以下重要警告:** 切勿在中更改文件权限/var/lib/clickhouse/backup。此路径包含硬链接。磁盘上相同数据的所有硬链接上的权限始终相同。这意味着,如果您更改备份路径中硬链接上的权限/所有者/属性,与ClickHouse一起使用的文件的权限也将更改。这可能会导致数据损坏。 ## 二、安装方式: **2.1、二进制文件安装:** **clickhouse-backup下载:** ``` tar -xf clickhouse-backup.tar.gz cd clickhouse-backup / sudo cp clickhouse-backup /usr/local/bin ``` **2.2、下载rpm安装包安装:** ``` rpm -ivh clickhouse-backup-1.0.0-1.x86_64.rpm ``` **2.3、亲自测试可用配置文件如下:** ``` [root@tidb06 backup]# cat /etc/clickhouse-backup/config.yml general: remote_storage: s3 max_file_size: 1099511627776 disable_progress_bar: false backups_to_keep_local: 3 backups_to_keep_remote: 3 log_level: info allow_empty_backups: false clickhouse: username: default password: "j780UJy9D2tn" host: localhost port: 9000 data_path: "" #disk_mapping: {} skip_tables: - system.* - default.* timeout: 5m freeze_by_part: false secure: false skip_verify: false sync_replicated_tables: true skip_sync_replica_timeouts: true log_sql_queries: false s3: access_key: "HYdGDq289324ustSWI82JHw2sU8" secret_key: "J38l8nq289324ustSWI8SR22Jl" bucket: "test-bucket" endpoint: "oss-cn-beijing-internal.aliyuncs.com" path: "./files_download/" #region: us-east-1 #acl: private force_path_style: false disable_ssl: false part_size: 536870912 compression_level: 1 compression_format: tar sse: "" disable_cert_verification: false storage_class: STANDARD ``` ## 三、命令具体使用演示: **3.1、查看全部默认的配置项** clickhouse-backup default-config **3.2、查看可备份的表:** ``` [root@tidb06 clickhouse-backup]# clickhouse-backup tables db01.t_order_mt 0B default db01.test_table 255B default test001.hits_v1 0B default test001.t_order_mt 0B default test001.test_table 1.25KiB default test001.test_table01 0B default test001.test_table02 0B default test002.test_table 0B default test008.test_table 805B default testdb01.test_table 723B default testdb01.test_table01 723B default testdb01.test_table02 0B default tutorial.hits_v1 1.18GiB default tutorial.hits_v2 1.18GiB default tutorial.visits_v1 527.47MiB default ``` **3.3、创建本地备份和删除本地备份,利用本地备份进行恢复:** **1. 全库备份** ``` clickhouse-backup create [root@tidb06 data1]# clickhouse-backup create 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=db01.t_order_mt 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=db01.test_table 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test001.hits_v1 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test001.t_order_mt 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test001.test_table 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test001.test_table01 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test001.test_table02 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test002.test_table 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=test008.test_table 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=testdb01.test_table 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=testdb01.test_table01 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=testdb01.test_table02 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=tutorial.hits_v1 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=tutorial.hits_v2 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create table=tutorial.visits_v1 2021/06/25 22:58:13 info done backup=2021-06-25T14-58-13 operation=create ``` **默认备份在下面的目录:** ``` /var/lib/clickhouse/backup [root@tidb06 backup]# ll total 4 drwxr-x--- 4 clickhouse clickhouse 4096 Jun 25 22:58 2021-06-25T14-58-13 ``` **备份存储在中 $data_path/backup 下,备份名称默认为时间戳,可手动指定备份名称。例如:** ``` [root@tidb06 backup]# clickhouse-backup create click-bak0625 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=db01.t_order_mt 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=db01.test_table 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test001.hits_v1 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test001.t_order_mt 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test001.test_table 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test001.test_table01 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test001.test_table02 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test002.test_table 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=test008.test_table 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=testdb01.test_table 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=testdb01.test_table01 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=testdb01.test_table02 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=tutorial.hits_v1 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=tutorial.hits_v2 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create table=tutorial.visits_v1 2021/06/25 23:02:19 info done backup=click-bak0625 operation=create [root@tidb06 backup]# ls 2021-06-25T14-58-13 click-bak0625 ``` **备份包含两个目录:** 'metadata'目录: 包含重新创建所需的DDL SQL 'shadow'目录: 包含作为ALTER TABLE ... FREEZE操作结果的数据。 **2、单表备份** **语法:** clickhouse-backup create [-t, --tables=.] **备份表test001.test_table 备份名称:test_table0625** ``` [root@tidb06 backup]# clickhouse-backup create -t test001.test_table test_table0625 2021/06/25 23:06:20 info done backup=test_table0625 operation=create table=test001.test_table 2021/06/25 23:06:20 info done backup=test_table0625 operation=create [root@tidb06 backup]# ``` **3.备份多个表** ``` clickhouse-backup create -t db01.t_order_mt,db01.test_table db01_0625 [root@tidb06 backup]# clickhouse-backup create -t db01.t_order_mt,db01.test_table db01_0625 2021/06/25 23:10:29 info done backup=db01_0625 operation=create table=db01.t_order_mt 2021/06/25 23:10:29 info done backup=db01_0625 operation=create table=db01.test_table 2021/06/25 23:10:29 info done backup=db01_0625 operation=create 2021/06/25 23:10:29 info done backup=2021-06-25T14-58-13 location=local operation=delete ``` ``` [root@tidb06 backup]# clickhouse-backup create -t test001.test_table,tutorial.visits_v1 test001_tutorial_0625 2021/06/25 23:15:07 info done backup=test001_tutorial_0625 operation=create table=test001.test_table 2021/06/25 23:15:07 info done backup=test001_tutorial_0625 operation=create table=tutorial.visits_v1 2021/06/25 23:15:07 info done backup=test001_tutorial_0625 operation=create 2021/06/25 23:15:07 info done backup=test_table0625 location=local operation=delete [root@tidb06 shadow]# ls test001 tutorial [root@tidb06 shadow]# pwd /var/lib/clickhouse/backup/test001_tutorial_0625/shadow [root@tidb06 metadata]# ls test001 tutorial [root@tidb06 metadata]# pwd /var/lib/clickhouse/backup/test001_tutorial_0625/metadata ``` **查看本地的备份文件:** ``` [root@tidb06 metadata]# clickhouse-backup list db01_0625 971B 25/06/2021 15:10:29 local db01_tutorial_0625 527.48MiB 25/06/2021 15:12:38 local test001_tutorial_0625 527.48MiB 25/06/2021 15:15:07 local 2021/06/25 23:16:24 error InvalidParameter: 1 validation error(s) found. - minimum field size of 1, ListObjectsV2Input.Bucket. ``` **4、删除本地备份文件** ``` root@tidb06 metadata]# clickhouse-backup delete local db01_tutorial_0625 2021/06/25 23:17:42 info done backup=db01_tutorial_0625 location=local operation=delete [root@tidb06 metadata]# clickhouse-backup list db01_0625 971B 25/06/2021 15:10:29 local test001_tutorial_0625 527.48MiB 25/06/2021 15:15:07 local 2021/06/25 23:17:54 error InvalidParameter: 1 validation error(s) found. - minimum field size of 1, ListObjectsV2Input.Bucket. [root@tidb06 metadata]# [root@tidb06 metadata]# cd /var/lib/clickhouse/backup/ [root@tidb06 backup]# ls db01_0625 test001_tutorial_0625 ``` **5、数据恢复** **语法:** clickhouse-backup restore 备份名 ``` [root@tidb06 db01_0625]# clickhouse-backup restore -help NAME: clickhouse-backup restore - Create schema and restore data from backup USAGE: clickhouse-backup restore [-t, --tables=.
] [-s, --schema] [-d, --data] [--rm, --drop] OPTIONS: --config FILE, -c FILE Config FILE name. (default: "/etc/clickhouse-backup/config.yml") [$CLICKHOUSE_BACKUP_CONFIG] --table value, --tables value, -t value --schema, -s Restore schema only --data, -d Restore data only --rm, --drop Drop table before restore ``` **一些参数:** ``` --table 只恢复特定表,可使用正则。 如针对特定的数据库:--table=dbname.* --schema  只还原表结构 --data     只还原数据 ``` **利用备份还原db01库演示:** ``` [root@tidb06 backup]# clickhouse-client -udefault --password=j780UJy9D2tn drop database db01; [root@tidb06 shadow]# clickhouse-backup restore db01_0625 2021/06/25 23:28:36 info done backup=db01_0625 operation=restore table=db01.t_order_mt 2021/06/25 23:28:36 info done backup=db01_0625 operation=restore table=db01.test_table 2021/06/25 23:28:36 info done backup=db01_0625 operation=restore tidb06 :) show tables; SHOW TABLES ┌─name───────┐ │ t_order_mt │ │ test_table │ └────────────┘ 2 rows in set. Elapsed: 0.002 sec. tidb06 :) select count(*) from t_order_mt; SELECT count(*) FROM t_order_mt ┌─count()─┐ │ 0 │ └─────────┘ 1 rows in set. Elapsed: 0.002 sec. tidb06 :) select count(*) from test_table; SELECT count(*) FROM test_table ┌─count()─┐ │ 1 │ └─────────┘ ``` **指定备份的表tutorial.visits_v还原:** ``` use tutorial drop table visits_v1; [root@tidb06 shadow]# clickhouse-backup restore -t tutorial.visits_v1 test001_tutorial_0625 2021/06/25 23:34:23 info done backup=test001_tutorial_0625 operation=restore table=tutorial.visits_v1 2021/06/25 23:34:23 info done backup=test001_tutorial_0625 operation=restore tidb06 :) select count(*) from visits_v1; SELECT count(*) FROM visits_v1 ┌─count()─┐ │ 1676861 │ └─────────┘ ``` ## 四、备份到远程目标阿里oss或者AWS S3存储以及从oss存储的备份文件下载到本地 **Clickhouse-backup 支持从远程对象存储(例如S3,GCS或IBM的COS)上载和下载备份** **上传服务器本地的备份到阿里oss存储bucket test-bucket 的./files_download/ 文件下面:** ``` [root@tidb06 backup]# clickhouse-backup upload db01_0625 2021/06/25 23:50:10 info done backup=db01_0625 duration=15ms operation=upload size=354B table=db01.t_order_mt 2021/06/25 23:50:11 info done backup=db01_0625 duration=484ms operation=upload size=10.41KiB table=db01.test_table========================================] 100.00% 0s 2021/06/25 23:50:11 info done backup=db01_0625 duration=1.063s operation=upload size=12.44KiB [root@tidb06 backup]# ls db01_0625 test001_tutorial_0625 [root@tidb06 backup]# du -sh test001_tutorial_0625/ 529M test001_tutorial_0625/ [root@tidb06 backup]# clickhouse-backup upload test001_tutorial_0625 2021/06/25 23:50:52 info done backup=test001_tutorial_0625 duration=125ms operation=upload size=46.61KiB table=test001.test_table=========================] 100.00% 0s 2021/06/25 23:51:03 info done backup=test001_tutorial_0625 duration=11.03s operation=upload size=527.75MiB table=tutorial.visits_v1======================] 100.00% 10s 2021/06/25 23:51:03 info done backup=test001_tutorial_0625 duration=11.257s operation=upload size=527.80MiB [root@tidb06 backup]# clickhouse-backup upload db01_0625 2021/06/26 14:24:29 info done backup=db01_0625 duration=28ms operation=upload size=354B table=db01.t_order_mt 2021/06/26 14:24:30 info done backup=db01_0625 duration=975ms operation=upload size=10.41KiB table=db01.test_table========================================] 100.00% 0s 2021/06/26 14:24:30 info done backup=db01_0625 duration=1.159s operation=upload size=12.44KiB ``` ![10.png](http://www.icode9.com/i/li/?n=2&i=images/20210710/1625915334101956.png?,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=) ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ``` [root@tidb06 clickhouse-backup]# clickhouse-backup create -t db01.t_order_mt,db01.test_table db01_0625 2021/06/26 14:05:48 info done backup=db01_0625 operation=create table=db01.t_order_mt 2021/06/26 14:05:48 info done backup=db01_0625 operation=create table=db01.test_table 2021/06/26 14:05:48 info done backup=db01_0625 operation=create [root@tidb06 backup]# pwd /var/lib/clickhouse/backup [root@tidb06 backup]# ll total 4 drwxr-x--- 4 clickhouse clickhouse 4096 Jun 26 14:05 db01_0625 [root@tidb06 backup]# du -sh * 80K db01_0625 ``` **查看本地备份文件:** [root@tidb06 backup]# clickhouse-backup list db01_0625 971B 26/06/2021 06:05:48 local **下载阿里的备份文件到服务器本地:** ``` [root@tidb06 backup]# rm -fr db01_0625 [root@tidb06 backup]# clickhouse-backup download db01_0625 2021/06/26 14:29:45 info done backup=db01_0625 operation=download table=db01.t_order_mt 2021/06/26 14:29:45 info done backup=db01_0625 operation=download table=db01.test_table 2021/06/26 14:29:45 info done backup=db01_0625 duration=0s operation=download size=716B table=db01.t_order_mt 2021/06/26 14:29:46 info done backup=db01_0625 duration=13ms operation=download size=971B table=db01.test_table===========================================] 100.00% 0s 2021/06/26 14:29:46 info done backup=db01_0625 duration=115ms operation=download size=971B [root@tidb06 backup]# pwd /var/lib/clickhouse/backup [root@tidb06 backup]# ll total 4 drwxr-x--- 4 root root 4096 Jun 26 14:29 db01_0625 ``` **配置文件如下:** ``` [root@tidb06 backup]# cat /etc/clickhouse-backup/config.yml general: remote_storage: s3 max_file_size: 1099511627776 disable_progress_bar: false backups_to_keep_local: 3 backups_to_keep_remote: 3 log_level: info allow_empty_backups: false clickhouse: username: default password: "j780UJy9D2tn" host: localhost port: 9000 data_path: "" #disk_mapping: {} skip_tables: - system.* - default.* timeout: 5m freeze_by_part: false secure: false skip_verify: false sync_replicated_tables: true skip_sync_replica_timeouts: true log_sql_queries: false s3: access_key: "HYdGDq289324ustSWI82JHw2sU8" secret_key: "J38l8nq289324ustSWI8SR22Jl" bucket: "test-bucket" endpoint: "oss-cn-beijing-internal.aliyuncs.com" path: "./files_download/" #region: us-east-1 #acl: private force_path_style: false disable_ssl: false part_size: 536870912 compression_level: 1 compression_format: tar sse: "" disable_cert_verification: false storage_class: STANDARD ``` ## 五、使用Clickhouse-backup API **最后,clickhouse-backup可以作为提供REST API的服务运行。这是一个新功能。该API反映了命令行命令和选项,并且可能是与现有调度或CI / CD系统集成的更方便的方法。** **本地服务器clickhouse-backup启动server:** ``` sudo clickhouse-backup server & [root@tidb06 backup]# sudo clickhouse-backup server & [1] 31796 [root@tidb06 backup]# 2021/06/26 14:35:21 info Starting API server on localhost:7171 [root@tidb06 backup]# ss -lntup|grep 7171 tcp LISTEN 0 32768 127.0.0.1:7171 *:* users:(("clickhouse-back",pid=31797,fd=9)) [root@tidb06 backup]# ``` **配置文件内容:** ``` [root@tidb06 clickhouse-backup]# cat config.yml|tail -10 api: listen: 0.0.0.0:7171 enable_metrics: true enable_pprof: false #username: "default" #password: "j780UJy9D2tn" secure: false #certificate_file: "/etc/clickhouse-backup/keys/testclick.cn.pem" #private_key_file: "/etc/clickhouse-backup/keys/testclick.cn.key" create_integration_tables: false ``` **配置文件说明:允许通过api 任何人可以访问备份文件信息:** ``` [root@tidb06 backup]# curl -s localhost:7171/backup/list {"name":"db01_0625","created":"2021-06-26 06:05:48","size":971,"location":"local","desc":""} {"name":"test001_tutorial_0625","created":"2021-06-26 06:54:20","size":553103847,"location":"local","desc":""} {"name":"","created":"0001-01-01 00:00:00","location":"remote","desc":"broken (can't stat metadata.json)"} {"name":"db01_0625","created":"2021-06-26 06:05:48","size":1025,"location":"remote","desc":"tar"} ``` **配置文件说明:允许通过api 输入账户和密码可以访问备份文件信息:** ``` [root@tidb06 clickhouse-backup]# cat config.yml|tail -10 api: listen: 0.0.0.0:7171 enable_metrics: true enable_pprof: false username: "default" password: "j780UJy9D2tn" secure: false #certificate_file: "/etc/clickhouse-backup/keys/testclick.cn.pem" #private_key_file: "/etc/clickhouse-backup/keys/testclick.cn.key" create_integration_tables: false [root@tidb06 clickhouse-backup]# curl -udefault:j780UJy9D2tn http://172.16.0.247:7171/backup/list {"name":"db01_0625","created":"2021-06-26 06:05:48","size":971,"location":"local","desc":""} {"name":"test001_tutorial_0625","created":"2021-06-26 06:54:20","size":553103847,"location":"local","desc":""} {"name":"","created":"0001-01-01 00:00:00","location":"remote","desc":"broken (can't stat metadata.json)"} {"name":"db01_0625","created":"2021-06-26 06:05:48","size":1025,"location":"remote","desc":"tar"} ``` **配置文件说明:允许通过api 输入账户和密码以及开启证书加密 访问备份文件信息:** ``` [root@tidb06 clickhouse-backup]# cat config.yml|tail -10 api: listen: 0.0.0.0:7171 enable_metrics: true enable_pprof: false username: "default" password: "j780UJy9D2tn" secure: false certificate_file: "/etc/clickhouse-backup/keys/testclick.cn.pem" private_key_file: "/etc/clickhouse-backup/keys/testclick.cn.key" create_integration_tables: false ``` ``` [root@tidb06 clickhouse-backup]# curl -s -udefault:j780UJy9D2tn http://172.16.0.247:7171/backup/list | jq { "name": "db01_0625", "created": "2021-06-26 06:05:48", "size": 971, "location": "local", "desc": "" } { "name": "test001_tutorial_0625", "created": "2021-06-26 06:54:20", "size": 553103847, "location": "local", "desc": "" } { "name": "", "created": "0001-01-01 00:00:00", "location": "remote", "desc": "broken (can't stat metadata.json)" } { "name": "db01_0625", "created": "2021-06-26 06:05:48", "size": 1025, "location": "remote", "desc": "tar" } curl -s -udefault:j780UJy9D2tn http://172.16.0.247:7171/backup/tables |jq ``` 关于click-backup备份工具使用演示完毕
上一篇:007--TypeScript之类的修饰符


下一篇:[计数] aw3759. 第k个字符串(枚举+思维+aw周赛007_2)