clickhouse-备份恢复

一、简介

备份恢复是数据库常用的手段，可能大多数公司很少会对大数据所使用的数据进行备份，这里还是了解下比较好，下面做了一些简单的介绍，详细情况可以通过官网来查看，经过测试发现Disk中增量备份并不好用，而File中的增量是好用的,也可以看下开源工具clickhouse-backup的备份功能

二、备份恢复详解

1.备份恢复通用命令

# 在 ClickHouse 23.4 版本之前，ALL 仅适用于 RESTORE 命令。BACKUP|RESTORETABLE [db.]table_name [AS [db.]table_name_in_backup][PARTITION[S] partition_expr [,...]] |DICTIONARY [db.]dictionary_name [AS [db.]name_in_backup] |DATABASE database_name [AS database_name_in_backup][EXCEPT TABLES ...] |TEMPORARY TABLE table_name [AS table_name_in_backup] |VIEW view_name [AS view_name_in_backup]ALL TEMPORARY TABLES [EXCEPT ...] |ALL DATABASES [EXCEPT ...] } [,...][ON CLUSTER 'cluster_name']TO|FROM File('<path>/<filename>') | Disk('<disk_name>', '<path>/') | S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>')[SETTINGS base_backup = File('<path>/<filename>') | Disk(...) | S3('<S3 endpoint>/<path>', '<Access key ID>', '<Secret access key>')]

2.在Disk或File备份时需要的设置

可以直接在/etc/clickhouse-server/config.xml文件中更改也可以在/etc/clickhouse-server/config.d中新创建个backup_disk.xml文件,如果使用的是backup_disk.xml文件，要把clickhouse注释去掉

<!-- <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>-->

3.BACKUP 和 RESTORE 的命令详解

• 备份的目标或还原的源： 这是基于之前定义的磁盘。 例如Disk(‘backups’, ‘filename.zip’)
• ASYNC：异步备份或恢复
• PARTITIONS：要恢复的分区列表
• 设置：
  • compression_method（压缩方法）和compression_level（压缩级别）
  • password（磁盘上文件的密码）
  • base_backup：此源的先前备份的目的地。 例如，Disk(‘backups’, ‘1.zip’)
  • Structure_only：如果启用，则允许仅备份或恢复CREATE语句，而无需表的数据
  • storage_policy：正在恢复的表的存储策略。 请参阅使用多个块设备进行数据存储。 此设置仅适用于 RESTORE 命令。 指定的存储策略仅适用于具有 MergeTree 系列引擎的表。
  • s3_storage_class：用于S3备份的存储类别。 例如，STANDARD

4.备份时的异常报错：

Code: 76. DB::Exception: Received from localhost:9000. DB::ErrnoException. DB::ErrnoException: Cannot open file /backups/com2.zip.lock, errno: 13, strerror: Permission denied. (CANNOT_OPEN_FILE)

这是因为我们clickhouse是由用户clickhouse启动的，而指定的备份目录的属组和属主是root,所以我们需要执行如下操作

chown -R clickhouse:clickhouse /backups

5.备份到disk

5.1 全量备份恢复

# 备份
BACKUP TABLE com TO Disk('backups', 'com.zip')
# 恢复，恢复的时候要注意和备份的表名一致，不然会出现找不到表的报错
RESTORE TABLE com FROM Disk('backups', 'com.zip');
# 如果表包含数据，上面的 RESTORE 将失败，您必须删除该表才能测试 RESTORE，或者使用设置allow_non_empty_tables=true：
# 恢复表到一个新的表中
RESTORE TABLE com as com2 FROM Disk('backups', 'com.zip')

5.2 增量备份恢复

SELECT count(1)
FROM comQuery id: b8cb3ed4-4407-4969-b8cc-7bf0861daf5c┌─count()─┐
│     100 │
└─────────┘
# 删除一行数据
delete from com where hash='d98b7d731555ae78ce7cdd7c7f4c8f8e302e71aa';
SELECT count(1)
FROM comQuery id: 7af9fa24-408f-408e-a37d-3519b704dd8d┌─count()─┐
│      99 │
└─────────┘# 增量备份
BACKUP TABLE com TO Disk('backups', 'com_inc.zip') SETTINGS base_backup = Disk('backups', 'com.zip')# 查看备份结果
ll
-rw-r----- 1 clickhouse clickhouse  9405 Aug 17 16:54 com_inc.zip
-rw-r----- 1 clickhouse clickhouse 10574 Aug 17 16:03 com.zip# 增量恢复
RESTORE TABLE com as com3 FROM Disk('backups', 'com_inc.zip')# 插入数据
insert into com select * from com3 limit 3;
# 继续备份
BACKUP TABLE com TO Disk('backups', 'com_inc_2.zip') SETTINGS base_backup = Disk('backups', 'com.zip')# 查看备份结果，通过上面的实验和下面的结果我们可以看到com_inc_2.zip竟然比com_inc.zip还要大，而且还原数据的时候也不需要之前的全量备份，所以我们这里可以下一个结论，clickhouse的增量备份是伪增量备份，或者可以说就是全量备份
ll
-rw-r----- 1 clickhouse clickhouse 12295 Aug 17 17:00 com_inc_2.zip
-rw-r----- 1 clickhouse clickhouse  9405 Aug 17 16:54 com_inc.zip
-rw-r----- 1 clickhouse clickhouse 10574 Aug 17 16:03 com.zip

5.3 加密备份恢复

# 加密备份
BACKUP TABLE com  TO Disk('backups', 'com_pass.zip') SETTINGS password='123456'# 加密恢复
RESTORE TABLE com as com_pass FROM Disk('backups', 'com_pass.zip') SETTINGS password='123456'

5.4 压缩备份恢复

# 压缩备份
BACKUP TABLE com  TO Disk('backups', 'com_comp.zip') SETTINGS compression_method='lzma', compression_level=3# 压缩恢复貌似无法执行，官方也没有实例

5.5 恢复指定分区

RESTORE TABLE pat PARTITIONS '2', '3'FROM Disk('backups', 'pat.zip')

6.备份到s3

# 下面是s3的格式
S3('<S3 endpoint>/<directory>', '<Access key ID>', '<Secret access key>)
# 备份
BACKUP TABLE data TO S3('https://mars-doc-test.s3.amazonaws.com/backup-S3/my_backup', 'ABC123', 'Abc+123')
# 恢复
RESTORE TABLE data AS data3 FROM S3('https://mars-doc-test.s3.amazonaws.com/backup-S3/my_incremental', 'ABC123', 'Abc+123')

也可以通过配置文件来配置s3路径和ak,sk

<clickhouse><storage_configuration><disks><s3_plain><type>s3_plain</type><endpoint></endpoint><access_key_id></access_key_id><secret_access_key></secret_access_key></s3_plain></disks><policies><s3><volumes><main><disk>s3_plain</disk></main></volumes></s3></policies></storage_configuration><backups><allowed_disk>s3_plain</allowed_disk></backups>
</clickhouse>

配置后就可以如下面方式备份了

BACKUP TABLE data TO Disk('s3_plain', 'cloud_backup');
RESTORE TABLE data AS data_restored FROM Disk('s3_plain', 'cloud_backup');

7.备份到file

# 全量备份恢复
BACKUP TABLE com  TO File('/data/tools/backup/com')
RESTORE TABLE com as com6  FROM File('/data/tools/backup/com')
# 插入数据
insert into com select * from com6 limit 60;
# 增量备份
BACKUP TABLE com TO File('/data/tools/backup/com_inc') SETTINGS base_backup = File('/data/tools/backup/com')
# 查看数据大小或者serialization.json文件，可以看到这里确实是增量，file的增量是好用的
du -sh com
112K	com
du -sh com_inc
56K	com_inc
# 恢复增量数据，不用写setting base_backup了
RESTORE TABLE com as com8  FROM File('/data/tools/backup/com_inc')

8.备份状态检查

SELECT*
FROM system.backups
where id='7678b0b3-f519-4e6e-811f-5a0781a4eb52'
FORMAT Vertical