一、背景
- 运行3年的clickhouse需要迁移机房,迁移单库单表的140亿条的数据。
- 采用clickhouse-backup 的方式进行备份迁移,打包备份,再加上数据拷贝,数据恢复 一共花费30分钟。
- 数据在一定量级,避免使用SQL 导入导出的方式,效率太低
二、clickhouse-backup工具介绍
clickhouse-backup 是社区开源的一个 ClickHouse 备份工具,可用于实现数据迁移。其原理是先创建一个备份,然后从备份导入数据,类似 MySQL 的 mysqldump + SOURCE。这个工具可以作为常规的异地冷备方案。
- clickhouse-backup社区地址: https://github.com/Altinity/clickhouse-backup
2.1、工作原理
clickhouse-backup
的备份原理主要依赖于 ClickHouse 数据库的存储结构和操作流程,结合增量备份和文件系统操作来实现高效的备份。其核心思想是通过对数据库表的数据和元数据进行快照,保存这些信息到指定的存储位置,并确保在备份过程中数据的一致性。
2.1.1、ClickHouse 数据存储结构
ClickHouse 将数据存储在表的分区中,每个分区包含多个数据块文件,这些文件存储在 ClickHouse 服务器的本地文件系统中。ClickHouse 使用的存储引擎(如 MergeTree)通过这些文件来管理数据,因此备份过程实际上就是将这些文件(表数据和元数据)复制到备份存储中。
2.1.2 、备份操作步骤
- 数据冻结(freeze)
在进行备份时,clickhouse-backup
会使用 ALTER TABLE ... FREEZE
命令将数据分区冻结。冻结操作会创建数据的一个一致性快照,确保数据在备份过程中不受写入操作的影响。这个快照是通过将分区中的数据硬链接(hard link)到特殊的备份目录中来实现的。由于是硬链接,创建快照不会占用额外的磁盘空间,并且操作非常快速。
- 备份数据的复制
冻结后的数据文件被复制到备份目录中。clickhouse-backup
会将这些文件以及相关的元数据(如表结构)保存到指定的存储后端(本地存储、S3 兼容存储或云存储等)。在这个过程中,备份的文件包括:表结构(schema):包含创建表的 SQL 语句及相关信息。数据文件:包含实际的分区和数据块。
- 增量备份
对于增量备份,clickhouse-backup
仅备份自上次备份以来新增或修改的数据分区。这是通过比较文件的哈希值或时间戳来确定哪些分区已发生变化的,从而避免重复备份未改变的数据块。这一机制大幅减少了备份的数据量和所需时间。
2.1.3、 数据恢复原理
在恢复时,clickhouse-backup
根据备份中的表结构信息重新创建表,并将数据文件复制回 ClickHouse 的数据目录。恢复过程可以是全量恢复或增量恢复:
全量恢复:从全量备份中恢复所有数据。
增量恢复:先恢复全量备份,再应用增量备份中的数据。
恢复操作确保恢复的数据与备份时的数据完全一致。
2.1.4、一致性保障
ClickHouse 的 FREEZE
命令确保在备份期间即使有写入操作,已冻结的数据文件也不会被修改。备份过程使用文件系统的硬链接特性,在不复制文件数据的情况下创建一致性的快照,因此备份时的数据保持不变。
2.1.5、备份存储位置
clickhouse-backup
可以将备份存储在多种存储后端,包括:
- 本地文件系统:将备份保存到本地目录。
- S3 兼容存储:如 Amazon S3、MinIO 等,使用对象存储来保存备份。
- Google Cloud Storage:将备份上传到云端存储。
三、环境信息
- 本文不去复现140亿数据怎么迁移,通过演示部署一台clickhouse ,去使用官方的demo 数据演示如何备份,迁移;只要使用对了方法,后面任何迁移都不用担心
软件名称 | 版本 | 备注 |
---|---|---|
openeuler | 22.03 LTS SP4 | |
clickhouse-backup | 2.2.5 | docker部署 |
clickhouse | 24.5.1 | docker部署 |
四、部署clickhouse
**为了快速验证环境,本文采用docker-compose部署方式 **
- 代码库地址,提供Dockerfile 文件: https://cnb.cool/srebro/clickhouse
- 镜像地址:docker.cnb.cool/srebro/clickhouse:24.5.1
- 指定了docker网络,需要提前创建好docker 网络,
docker network create -d bridge --subnet "10.22.33.0/24" --gateway "10.22.33.1" srebro
1、创建目录
mkdir -p /home/application/Database/clickhouse/{data,log}
2、创建docker-compose.yaml 文件,见 docker-compose.yaml 文件
vim /home/application/Middleware/kafka/docker-compose.yml
services:
clickhouse:
image: docker.cnb.cool/srebro/clickhouse:24.5.1
container_name: clickhouse
restart: always
environment:
CLICKHOUSE_USER: 'default'
CLICKHOUSE_PASSWORD: 'srebro@2024' ##这里自定义clickhouse数据库密码
CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: '1'
networks:
- srebro
ports:
- "8123:8123"
- "9000:9000"
volumes:
- /home/application/Database/clickhouse/log:/var/log/clickhouse-server
- /home/application/Database/clickhouse/data:/var/lib/clickhouse
networks:
srebro:
external: true
3、运行docker-compose创建容器
docker-compose up -d
五、导入官方演示数据,并验证
- 演示官方数据:【纽约公共图书馆“菜单上有什么?”数据集】 https://clickhouse.com/docs/zh/getting-started/example-datasets/menus
5.1 下载官方数据
# 创建工作目录,下载数据集
$ mkdir -p /home/application/Database/clickhouse/data/clickhouse-demo
$ cd /home/application/Database/clickhouse/data/clickhouse-demo
$ wget https://s3.amazonaws.com/menusdata.nypl.org/gzips/2021_08_01_07_01_17_data.tgz
# 解压数据集
# 解压后的的大小约为 150 MB
$ tar xvf 2021_08_01_07_01_17_data.tgz
数据集由四个表组成:
Menu
- 有关菜单的信息,其中包含:餐厅名称,看到菜单的日期等Dish
- 有关菜肴的信息,其中包含:菜肴名称以及一些特征。MenuPage
- 有关菜单中页面的信息,每个页面都属于某个Menu
。MenuItem
- 菜单项。某个菜单页面上的菜肴及其价格:指向Dish
和MenuPage
的链接。
5.2 进入到容器中
# 进入到容器
$ docker exec -it clickhouse bash
#登录数据库
$ clickhouse-client
ClickHouse client version 24.5.1.1763 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.5.1.
Warnings:
* Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
998ed9141382 :)
5.3 创建数据库
# 创建数据库
998ed9141382 :) create database demo;
5.4 创建表
# 进入数据库,创建表
998ed9141382 :) use demo;
#分开单独创建每一个表
CREATE TABLE dish
(
id UInt32,
name String,
description String,
menus_appeared UInt32,
times_appeared Int32,
first_appeared UInt16,
last_appeared UInt16,
lowest_price Decimal64(3),
highest_price Decimal64(3)
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu
(
id UInt32,
name String,
sponsor String,
event String,
venue String,
place String,
physical_description String,
occasion String,
notes String,
call_number String,
keywords String,
language String,
date String,
location String,
location_type String,
currency String,
currency_symbol String,
status String,
page_count UInt16,
dish_count UInt16
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu_page
(
id UInt32,
menu_id UInt32,
page_number UInt16,
image_id String,
full_height UInt16,
full_width UInt16,
uuid UUID
) ENGINE = MergeTree ORDER BY id;
CREATE TABLE menu_item
(
id UInt32,
menu_page_id UInt32,
price Decimal64(3),
high_price Decimal64(3),
dish_id UInt32,
created_at DateTime,
updated_at DateTime,
xpos Float64,
ypos Float64
) ENGINE = MergeTree ORDER BY id;
5.4 导入数据
$ cd /var/lib/clickhouse/clickhouse-demo
$ clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO demo.dish FORMAT CSVWithNames" < Dish.csv
$ clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO demo.menu FORMAT CSVWithNames" < Menu.csv
$ clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --query "INSERT INTO demo.menu_page FORMAT CSVWithNames" < MenuPage.csv
$ clickhouse-client --format_csv_allow_single_quotes 0 --input_format_null_as_default 0 --date_time_input_format best_effort --query "INSERT INTO demo.menu_item FORMAT CSVWithNames" < MenuItem.csv
我们将再创建一个表“menu_item_denorm”,其中将包含所有联接在一起的数据:
#登录数据库
$ clickhouse-client
# 进入数据库,创建表
998ed9141382 :) use demo;
#创建表
CREATE TABLE menu_item_denorm
ENGINE = MergeTree ORDER BY (dish_name, created_at)
AS SELECT
price,
high_price,
created_at,
updated_at,
xpos,
ypos,
dish.id AS dish_id,
dish.name AS dish_name,
dish.description AS dish_description,
dish.menus_appeared AS dish_menus_appeared,
dish.times_appeared AS dish_times_appeared,
dish.first_appeared AS dish_first_appeared,
dish.last_appeared AS dish_last_appeared,
dish.lowest_price AS dish_lowest_price,
dish.highest_price AS dish_highest_price,
menu.id AS menu_id,
menu.name AS menu_name,
menu.sponsor AS menu_sponsor,
menu.event AS menu_event,
menu.venue AS menu_venue,
menu.place AS menu_place,
menu.physical_description AS menu_physical_description,
menu.occasion AS menu_occasion,
menu.notes AS menu_notes,
menu.call_number AS menu_call_number,
menu.keywords AS menu_keywords,
menu.language AS menu_language,
menu.date AS menu_date,
menu.location AS menu_location,
menu.location_type AS menu_location_type,
menu.currency AS menu_currency,
menu.currency_symbol AS menu_currency_symbol,
menu.status AS menu_status,
menu.page_count AS menu_page_count,
menu.dish_count AS menu_dish_count
FROM menu_item
JOIN dish ON menu_item.dish_id = dish.id
JOIN menu_page ON menu_item.menu_page_id = menu_page.id
JOIN menu ON menu_page.menu_id = menu.id;
5.5 验证数据
# 查看表数据
SELECT count() FROM menu_item_denorm;
#菜品的平均历史价格
SELECT
round(toUInt32OrZero(extract(menu_date, '^\\d{4}')), -1) AS d,
count(),
round(avg(price), 2),
bar(avg(price), 0, 100, 100)
FROM menu_item_denorm
WHERE (menu_currency = 'Dollars') AND (d > 0) AND (d < 2022)
GROUP BY d
ORDER BY d ASC;
六、使用clickhouse-backup 工具
- clickhouse-backup 工具可采用docker 的部署方式,需要把clickhouse 的数据目录挂在到clickhouse-backup 容器中
- 需要在env 中指定
CLICKHOUSE_PASSWORD
,CLICKHOUSE_HOST
,CLICKHOUSE_PORT
变量,具体的变量名称,可以见官方的https://github.com/Altinity/clickhouse-backup#explain-config-parameters
6.1 运行clickhouse-backup容器
⚠️ 这里我定义的clickhouse的数据目录在宿主机上的
/home/application/Database/clickhouse
目录下,需要把这个目录也挂载到clickhouse-backup容器 中
$ docker run --rm -it --network host -v "/home/application/Database/clickhouse/data:/var/lib/clickhouse" -e CLICKHOUSE_PASSWORD="srebro@2024" -e CLICKHOUSE_HOST="localhost" -e CLICKHOUSE_PORT="9000" altinity/clickhouse-backup:2.2.5 bash
bash-5.1#
6.2 clickhouse-backup 相关命令
6.2.1 查看默认的配置项
$ clickhouse-backup default-config
6.2.2 查看可备份的表
【默认的配置文件中已经过滤掉system和default 库下面的所有表】
$ clickhouse-backup tables
6.2.3 全库备份
$ clickhouse-backup create
- 备份存储在中 $data_path/backup 下,即
/var/lib/clickhouse/backup
下; 备份名称默认为时间戳
$ ls -l /var/lib/clickhouse/backup
- 也可手动指定备份名称,如 srebro-10-10
$ clickhouse-backup create srebro-10-10
$ ls -l /var/lib/clickhouse/backup
备份包含两个目录:
- metadata目录: 包含重新创建所需的DDL SQL
- shadow目录: 包含作为ALTER TABLE … FREEZE操作结果的数据
$ ls -l /var/lib/clickhouse/backup/srebro-10-10/
6.2.4 单表备份
#备份单表
$ clickhouse-backup create -t demo.dish
#备份多个表
$ clickhouse-backup create -t demo.dish,demo.menu
6.2.5 查看备份记录
$ clickhouse-backup list
6.2.6 删除备份文件
$ clickhouse-backup delete local srebro-10-10
#再次查看备份记录,发现没有srebro-10-10
$ clickhouse-backup list
6.2.7 数据恢复
$ clickhouse-backup restore --help
NAME:
clickhouse-backup restore - Create schema and restore data from backup
USAGE:
clickhouse-backup restore [-t, --tables=<db>.<table>] [-s, --schema] [-d, --data] [--rm, --drop] <backup_name>
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 只还原表结构, 简写 -s
--data 只还原数据, 简写 -d
--rm 数据恢复之前,先删除需要恢复的表
七、使用clickhouse-backup备份与恢复数据-场景实战
- 备份前,先查看
demo.menu_item_denorm
表里的数据量,一共是1329175条数据
SELECT count() FROM demo.menu_item_denorm;
- 查看可备份的表
clickhouse-backup tables
- 全库备份数据
$ clickhouse-backup create
- 查看备份文件是否存在
$ clickhouse-backup list
- 模拟删除demo数据库
$ clickhouse-client
998ed9141382 :) drop database demo;
998ed9141382 :) show databases;
- 使用备份文件恢复数据库
$ clickhouse-backup restore 2024-10-10T14-40-20 -s -d --rm
PS: 如果遇到,下面的情况:
2024/10/10 14:46:13.769364 error can't create table `demo`.`menu_page`: code: 57, message: Directory for table data store/738/738c9b6f-354b-41d9-ad2a-c9cc27142853/ already exists after 5 times, please check your schema dependencies
解决方法: 重启clickhouse 数据库即可
- 验证数据是否完整
SELECT count() FROM demo.menu_item_denorm;
发现数据和删除前数量是一致的,我们使用clickhouse-backup 恢复成功
八、使用脚本定期异机远程备份
**环境: **
- clickhouse 数据库 192.168.99.102
- 备份存储服务器 192.168.99.101
- 使用二进制方式,部署的clickhouse-backup 工具
**条件: **
- 备份存储服务器 建立备份目录,/data/clickhouse-back
- clickhouse 数据库 可以免密到 存储服务器 上,免密传输备份文件
- 需要创建,
/etc/clickhouse-backup/config.yml
配置文件,指定 clickhouse的数据目录,数据库密码,监控地址,端口,以及本地备份保存策略
vim /var/lib/clickhouse/clickhouse-backup.sh
#!/bin/bash
#Author srebro.cn
####################################################
##
## clickhouse-back script
## backup data at remote host
## you should config ssh trust
##
####################################################
MSNAME=srebro
BAKFILE=$MSNAME-`date +%Y%m%d%H%M%S`
LOCAL_BAKDIR=/var/lib/clickhouse/backup
REMOTE_BAKDIR=/data/clickhouse-back
REMOTE_HOST=root@192.168.99.101
#备份到本地
/usr/bin/clickhouse-backup create $BAKFILE
if [[ $? != 0 ]]; then
echo "clickhouse-backup Create FAILED" > /var/log/clickhouse-backup.log
exit
else
#SCP备份到远程主机
scp -rp $LOCAL_BAKDIR/$BAKFILE $REMOTE_HOST:$REMOTE_BAKDIR/
if [[ $? != 0 ]]; then
echo "clickhouse-backup FAILED" > /var/log/clickhouse-backup.log
else
echo "clickhouse-backup successful" > /var/log/clickhouse-backup.log
fi
fi
#本地保留的时间,可以再配置文件中定义,如 backups_to_keep_local: 7,就表示本地保留7天备份数据;
#可以见官方的https://github.com/Altinity/clickhouse-backup#explain-config-parameters 中定义的参数
#定期删除远程备份文件
ssh $REMOTE_HOST "find $REMOTE_BAKDIR/srebro* -maxdepth 0 -mtime +30 -type d | xargs rm -rf {}"
九、常见问题
9.1 问题现象:使用clickhouse-backup 恢复数据时,提示UUID 问题
clickhouse-backup restore 2021-08-21T06-35-10 -s -d --rm
2021/08/21 14:40:51 error can't create table `default`.`t`: code: 57, message:
Directory for table data store/c57/c5780d8a-7d5a-47a3-8578-0d8a7d5a37a3/ already exists after 1 times, please check your schema depencncies
解决方法:
去掉 备份文件中 ${backup_path}/2021-08-21T06-35-10/metadata/default/t.json 中的UUID
UUID '80ea6411-9c37-4d47-80ea-64119c374d47'
再次执行恢复
clickhouse-backup restore 2021-08-21T06-35-10 -s -d --rm
SELECT count(1)
FROM datasets.hits_v1
┌─count(1)─┐
│ 17747796 │
└──────────┘
1 rows in set. Elapsed: 0.016 sec.
localhost :) exit
9.2 问题现象:使用clickhouse-backup 恢复数据时,提示 message: Directory for table data store already exists 问题
2024/10/10 14:46:13.769364 error can't create table `demo`.`menu_page`: code: 57, message: Directory for table data store/738/738c9b6f-354b-41d9-ad2a-c9cc27142853/ already exists after 5 times, please check your schema dependencies
解决方法:
重启clickhouse 数据库即可