一、背景

  • 运行3年的clickhouse需要迁移机房，迁移单库单表的140亿条的数据。
  • 采用clickhouse-backup 的方式进行备份迁移，打包备份，再加上数据拷贝，数据恢复 一共花费30分钟。
  • 数据在一定量级，避免使用SQL 导入导出的方式，效率太低

image-20241010191118098

image-20241010191124552

二、clickhouse-backup工具介绍

image-20241010153103227

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;

image-20241010215040282

#菜品的平均历史价格
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;

image-20241010215129714

六、使用clickhouse-backup 工具

image-20241010215613280

  • 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

image-20241010220430055

6.2.2 查看可备份的表

【默认的配置文件中已经过滤掉system和default 库下面的所有表】

$ clickhouse-backup tables

image-20241010220740428

6.2.3 全库备份

$ clickhouse-backup create

image-20241010221015373

  • 备份存储在中 $data_path/backup 下，即 /var/lib/clickhouse/backup 下； 备份名称默认为时间戳
$ ls -l /var/lib/clickhouse/backup

image-20241010221150450

  • 也可手动指定备份名称，如 srebro-10-10
$ clickhouse-backup create  srebro-10-10

$ ls -l /var/lib/clickhouse/backup

image-20241010221408656

  • 备份包含两个目录：

    • metadata目录: 包含重新创建所需的DDL SQL
    • shadow目录: 包含作为ALTER TABLE ... FREEZE操作结果的数据
    $ ls -l /var/lib/clickhouse/backup/srebro-10-10/

    image-20241010221554179

6.2.4 单表备份

#备份单表
$ clickhouse-backup create  -t demo.dish

#备份多个表
$ clickhouse-backup create  -t demo.dish,demo.menu

image-20241010221907547

6.2.5 查看备份记录

$ clickhouse-backup list

image-20241010222018292

6.2.6 删除备份文件

$ clickhouse-backup delete local srebro-10-10

#再次查看备份记录，发现没有srebro-10-10
$ clickhouse-backup list

image-20241010222144194

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;

image-20241010222636663

  • 查看可备份的表
clickhouse-backup tables

image-20241010222816716

  • 全库备份数据
$ clickhouse-backup create

image-20241010224031695

  • 查看备份文件是否存在
$ clickhouse-backup list

image-20241010224140418

  • 模拟删除demo数据库
$ clickhouse-client

998ed9141382 :) drop database demo;
998ed9141382 :) show databases;

image-20241010224301603

  • 使用备份文件恢复数据库
$ clickhouse-backup restore 2024-10-10T14-40-20 -s -d --rm

image-20241010224652237

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;

image-20241010224908937

发现数据和删除前数量是一致的，我们使用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 数据库即可