一、背景

• 运行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.ymlservices:clickhouse:image: docker.cnb.cool/srebro/clickhouse:24.5.1container_name: clickhouserestart: alwaysenvironment:CLICKHOUSE_USER: 'default'CLICKHOUSE_PASSWORD: 'srebro@2024' ##这里自定义clickhouse数据库密码CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: '1'networks:- srebroports:- "8123:8123"- "9000:9000"volumes:- /home/application/Database/clickhouse/log:/var/log/clickhouse-server- /home/application/Database/clickhouse/data:/var/lib/clickhousenetworks:srebro:external: true3、运行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/enabled998ed9141382 :) 

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 SELECTprice,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_itemJOIN dish ON menu_item.dish_id = dish.idJOIN menu_page ON menu_item.menu_page_id = menu_page.idJOIN menu ON menu_page.menu_id = menu.id;

5.5 验证数据

# 查看表数据
SELECT count() FROM menu_item_denorm;

#菜品的平均历史价格
SELECTround(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 bashbash-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 --helpNAME: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-client998ed9141382 :) 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 ]]; thenecho "clickhouse-backup Create FAILED" > /var/log/clickhouse-backup.logexit
else#SCP备份到远程主机
scp -rp $LOCAL_BAKDIR/$BAKFILE $REMOTE_HOST:$REMOTE_BAKDIR/if [[ $? != 0 ]]; thenecho "clickhouse-backup FAILED" > /var/log/clickhouse-backup.logelseecho "clickhouse-backup successful" > /var/log/clickhouse-backup.logfi
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 数据库即可