clickHouse 数据库备份方案

ALTER TABLE…FREEZE

备份工具Clickhouse-Backup

Clickhouse-Copier

下面就逐个试试吧。

# 数据备份 概述

https://clickhouse.tech/docs/en/operations/backup/

1. 文本文件导入导出

# 测试数据

MySQL中源数据6.70G，表数据量899万

--测试表数据量899万
--MySQL中源数据6.70G
0 rows in set. Elapsed: 71.482 sec. Processed 8.99 million rows, 6.70 GB (125.77 thousand rows/s., 93.71 MB/s.) 

# 导出

clickhouse-client --query="select * from caihao.ch_test_customer" > /data/clickhouse/tmp/caihao.ch_test_customer.tsv

# 导入 (注意FORMAT后面大写) 多个文件可以用 ch_test*

cat /data/clickhouse/tmp/caihao.ch_test_customer.tsv | clickhouse-client --query="insert into caihao.ch_test_customer FORMAT TSV"

速度：导入需要20多秒

# CH文件磁盘占用  368MB

368     ch_test_customer

# 备份文件3.5G 压缩后139MB

[root@clickhouse-01 tmp]# du -hsm *
3539    caihao.ch_test_customer.tsv
[root@clickhouse-01 tmp]# gzip caihao.ch_test_customer.tsv
[root@clickhouse-01 tmp]# du -hsm *
139     caihao.ch_test_customer.tsv.gz

# 对比下占用空间：

MySQL -- 6.7G

ClickHouse -- 368M

导出文本 -- 3.5G

压缩后 -- 139M

2. CTAS表快照

# 1 本地复制表

clickhouse-01 :) create table ch1  as ch_test_customer ;

CREATE TABLE ch1 AS ch_test_customer
Ok.

0 rows in set. Elapsed: 0.006 sec. 

clickhouse-01 :) insert into table ch1 select * from ch_test_customer ;

INSERT INTO ch1 SELECT *
FROM ch_test_customer
Ok.

0 rows in set. Elapsed: 18.863 sec. Processed 8.99 million rows, 6.70 GB (476.59 thousand rows/s., 355.13 MB/s.) 

# 2 远程复制表

https://clickhouse.tech/docs/en/sql-reference/table-functions/remote/

-# 语法
remote(\'addresses_expr\', db, table[, \'user\'[, \'password\']])
remote(\'addresses_expr\', db.table[, \'user\'[, \'password\']])

-# 例子：
dba-docker :) insert into table ch1 select * from remote (\'10.222.2.222\',\'caihao.ch_test_customer\',\'ch_app\',\'qwerty_123\');

INSERT INTO ch1 SELECT *
FROM remote(\'10.222.2.222\', \'caihao.ch_test_customer\', \'ch_app\', \'qwerty_123\')

Ok.

0 rows in set. Elapsed: 17.914 sec. Processed 8.99 million rows, 6.70 GB (501.85 thousand rows/s., 373.95 MB/s.) 
3. ALTER TABLE…FREEZE

语法：

ALTER TABLE table_name FREEZE [PARTITION partition_expr]

该操作为指定分区创建一个本地备份。

如果 PARTITION 语句省略，该操作会一次性为所有分区创建备份。整个备份过程不需要停止服务

注意：FREEZE PARTITION 只复制数据, 不备份元数据. 元数据默认在文件 /var/lib/clickhouse/metadata/database/table.sql

1. 备份的步骤：

# 确认shadow目录为空：

（默认位置：/var/lib/clickhouse/shadow/）

# OPTIMIZE TABLE 把临时分区的数据，合并到已有分区中

OPTIMIZE TABLE caihao.test_restore_tab PARTITION \'2020-10\' FINAL;

或者

OPTIMIZE TABLE caihao.test_restore_tab FINAL;

# 让ClickHouse冻结表：

echo -n \'alter table caihao.ch_test_customer freeze\' | clickhouse-client

# 备份后的文件

[root@clickhouse-01 shadow]# ll /data/clickhouse/data/shadow/
total 8
drwxr-x--- 3 clickhouse clickhouse 4096 Oct 16 15:34 1
-rw-r----- 1 clickhouse clickhouse    2 Oct 16 15:34 increment.txt
[root@clickhouse-01 shadow]# du -hsm *
309     1
1       increment.txt

# 按日期保存备份：

mkdir -p /data/clickhouse/data/backup/20201016/
cp -r /data/clickhouse/data/shadow/ /data/clickhouse/data/backup/20201016/

# 最后，为下次备份清理shadow目录：

rm -rf /data/clickhouse/data/shadow/*

2. 手动恢复

从备份中恢复数据，按如下步骤操作：

如果表不存在，先创建。查看.sql 文件获取执行语句 (将ATTACH 替换成 CREATE).

从 备份的data/database/table/目录中,将数据复制到 /var/lib/clickhouse/data/database/table/detached/目录

运行 ALTER TABLE t ATTACH PARTITION操作，将数据添加到表中

测试把数据恢复到一个新表test_restore_tab中

# 1 获取建表语句：

cat /data/clickhouse/data/metadata/caihao/ch_test_customer.sql 

然后将DDL语句中的 ATTACH TABLE 改为  CREATE TABLE