clickhouse-backup备份还原表需要注意的问题
通过clickhouse-bakcup备份而后还原一些对象，比如表。
还原的时候会直接将表的数据状态重置为备份时候的样子，就是备份是什么样子，还原之后就是什么样子。
（Oracle的数据泵用久了就觉得其他数据库的类型工具功能太简单了，有点不习惯）
备份一张表。
CK01 :) select count(*) from zkm.zkm; SELECT count(*) FROM zkm.zkm Query id: 23cd0253-2d96-4522-bfd9-68fd860f7f46 ┌─count()─┐ │ 100 │ └─────────┘ 1 rows in set. Elapsed: 0.003 sec. [root@dev-app76 zkm]# clickhouse-backup create -t zkm.zkm [root@dev-app76 zkm]# clickhouse-backup list 2023/03/27 11:17:48.609140 info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse 2023/03/27 11:17:48.612478 info clickhouse connection open: tcp://localhost:9000 logger=clickhouse 2023/03/27 11:17:48.612580 info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse 2023/03/27 11:17:48.619137 info SELECT * FROM system.disks; logger=clickhouse 2023-03-27T03-17-21 4.42KiB 27/03/2023 03:17:21 local 2023/03/27 11:17:48.627518 info clickhouse connection closed logger=clickhouse
模拟业务对表数据做变更。
CK01 :) insert into zkm.zkm select * from zkm.zkm; INSERT INTO zkm.zkm SELECT * FROM zkm.zkm Query id: 25e8bad9-9f09-44e9-a3fc-bb1db39b1cca Ok. 0 rows in set. Elapsed: 0.014 sec. CK01 :) select count(*) from zkm.zkm; SELECT count(*) FROM zkm.zkm Query id: ecd76c71-b978-4bcb-bd8e-70b336730e81 ┌─count()─┐ │ 200 │ └─────────┘ 1 rows in set. Elapsed: 0.003 sec. CK01 :) optimize table zkm.zkm final; OPTIMIZE TABLE zkm.zkm FINAL Query id: 52d0047f-4042-4396-a8ca-d2fd1912a6a8 Ok. 0 rows in set. Elapsed: 0.006 sec.
表在磁盘生分区目录情况。
[root@dev-app76 zkm]# pwd /clickhouse/data/zkm/zkm [root@dev-app76 zkm]# ll total 20 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_1_2_1 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_2_2_0 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 detached -rw-r----- 1 clickhouse clickhouse 1 Mar 27 11:15 format_version.txt ...隔一段时间... [root@dev-app76 zkm]# ll total 12 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:18 all_1_2_1 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:15 detached -rw-r----- 1 clickhouse clickhouse 1 Mar 27 11:15 format_version.txt
PS：中间分区目录合并后，目录“all_1_1_0”里边的文件硬链接数原本是2，合并完成后目录消失。
而目录“/clickhouse/backup/2023-03-27T03-17-21/shadow/zkm/zkm/default/all_1_1_0”的硬链接数从2变为1。
备份文件的对应表的分区目录情况。
[root@dev-app76 default]# pwd /clickhouse/backup/2023-03-27T03-17-21/shadow/zkm/zkm/default [root@dev-app76 default]# ll total 4 drwxr-x--- 2 root root 4096 Mar 27 11:17 all_1_1_0
还原表。
[root@dev-app76 zkm]# clickhouse-backup restore 2023-03-27T03-17-21 -t zkm.zkm 2023/03/27 11:30:41.426243 info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse 2023/03/27 11:30:41.430581 info clickhouse connection open: tcp://localhost:9000 logger=clickhouse 2023/03/27 11:30:41.430648 info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse 2023/03/27 11:30:41.437394 info SELECT * FROM system.disks; logger=clickhouse 2023/03/27 11:30:41.446092 info SELECT engine FROM system.databases WHERE name = 'zkm' logger=clickhouse 2023/03/27 11:30:41.453617 info DROP TABLE IF EXISTS `zkm`.`zkm` NO DELAY logger=clickhouse 2023/03/27 11:30:41.459703 info CREATE DATABASE IF NOT EXISTS `zkm` logger=clickhouse 2023/03/27 11:30:41.463289 info CREATE TABLE zkm.zkm UUID '70b9d6ee-793b-48d9-992a-53c247e95885' (`SEQ_NO` String, `CODE_SCC` String, `TRADE_CODE` String, `IE_FLAG` String, `CODE_TS` String, `TRADE_MARKET` String, `TRADE_MODE` String, `AMOUNT_RMB` Float32, `AMOUNT_USD` Float32, `STAT_DATE` Date, `CREATE_TIME` DateTime, `CRE_DATE` Date) ENGINE = MergeTree ORDER BY CREATE_TIME SETTINGS index_granularity = 8192 logger=clickhouse 2023/03/27 11:30:41.473515 info SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil' logger=clickhouse 2023/03/27 11:30:41.480224 info SELECT name FROM system.databases WHERE engine IN ('MySQL','PostgreSQL','MaterializedPostgreSQL') logger=clickhouse 2023/03/27 11:30:41.485506 info SELECT countIf(name='data_path') is_data_path_present, countIf(name='data_paths') is_data_paths_present, countIf(name='uuid') is_uuid_present, countIf(name='create_table_query') is_create_table_query_present, countIf(name='total_bytes') is_total_bytes_present FROM system.columns WHERE database='system' AND table='tables' logger=clickhouse 2023/03/27 11:30:41.493416 info SELECT database, name, engine , data_paths , uuid , create_table_query , coalesce(total_bytes, 0) AS total_bytes FROM system.tables WHERE is_temporary = 0 AND match(concat(database,'.',name),'zkm\.zkm') ORDER BY total_bytes DESC SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1 logger=clickhouse 2023/03/27 11:30:41.594886 info SELECT sum(bytes_on_disk) as size FROM system.parts WHERE active AND database='zkm' AND table='zkm' GROUP BY database, table logger=clickhouse 2023/03/27 11:30:41.603278 info ALTER TABLE `zkm`.`zkm` ATTACH PART 'all_1_1_0' logger=clickhouse 2023/03/27 11:30:41.608316 info done backup=2023-03-27T03-17-21 operation=restore table=zkm.zkm 2023/03/27 11:30:41.608378 info done backup=2023-03-27T03-17-21 duration=135ms operation=restore 2023/03/27 11:30:41.608416 info done backup=2023-03-27T03-17-21 operation=restore 2023/03/27 11:30:41.608478 info clickhouse connection closed logger=clickhouse
表在磁盘生分区目录情况。
[root@dev-app76 zkm]# pwd /clickhouse/data/zkm/zkm [root@dev-app76 zkm]# ll total 12 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:30 all_1_1_0 drwxr-x--- 2 clickhouse clickhouse 4096 Mar 27 11:30 detached -rw-r----- 1 clickhouse clickhouse 1 Mar 27 11:30 format_version.txt
数据条数变为了100条。
CK01 :) select count(*) from zkm; SELECT count(*) FROM zkm Query id: d3563bee-fe41-476b-9d96-362056e7ef10 ┌─count()─┐ │ 100 │ └─────────┘ 1 rows in set. Elapsed: 0.003 sec.