一、简介

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

二、备份恢复详解

1.备份恢复通用命令

# 在 ClickHouse 23.4 版本之前ALL 仅适用于 RESTORE 命令。
 BACKUP|RESTORE
  TABLE [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 com

Query id: b8cb3ed4-4407-4969-b8cc-7bf0861daf5c

┌─count()─┐
│     100 │
└─────────┘
# 删除一行数据
delete from com where hash='d98b7d731555ae78ce7cdd7c7f4c8f8e302e71aa';
SELECT count(1)
FROM com

Query 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