背景介绍：阿里云的ClickHouse已经提供了可靠的云端备份功能，但是为了进一步提高数据的安全性和可恢复性，我们决定采取额外的措施，将阿里云
ClickHouse
实例中的数据进行本地备份。通过在本地服务器上保存备份，我们能够在面对意外事件时更灵活地访问数据，并确保业务连续性。这种双重备份策略能够为我们的数据保护体系提供更坚实的保障。
环境说明：
阿里云clickhouse实例id：cc-gs5irjve4e02axxxx
规格：社区版 S8（标准型 8核 32GB）
版本：23.8
外网地址（本地连ck需要使用）：cc-gs5irjve4e02axxxx.public.clickhouse.ads.aliyuncs.com
本地模拟备份服务器地址：172.16.22.102
注意：低版本实例不⽀持备份数据到OSS，会出现类似“Not found backup engine S3”的异常信息。建议升级实例版本到23.8及以上
一、备份
1、创建备份账号
登录阿里云clickhouse控制台，确认ck实例上有备份账号，这里的备份账号是backup
若无备份账号，则使用超级管理员账号创建
#创建账号
CREATE USER IF NOT EXISTS 'backup' IDENTIFIED BY 'xxxxxxxx';
#授权
GRANT ALL ON *.* TO 'backup';
2、确认阿里云clickhouse有开通公网地址
因为本地服务器无法通过内网与阿里云clickhouse实例进行连接，所以需要使用公网地址
本地服务器测试连通性，如果不通则需要登录阿里云clickhouse控制台放通本地服务器的公网地址白名单
ping cc-gs5irjve4e02axxxx.public.clickhouse.ads.aliyuncs.com
3、本地服务器连接ClickHouse实例，并执行备份到OSS
如果本地服务器本来没有clickhouse-client命令，则需要提前安装，这里我的本地服务器有部署单机的clickhouse，所以无需安装。Clickhouse部署请跳转ClickHouse部署，100%亲测成功！—单机版&集群版
#安装clickhouse-client
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
rpm --import https://packages.clickhouse.com/rpm/clickhouse.repo.gpg
yum install -y clickhouse-client
本地服务器编写备份脚本
vim backup.sh
#!/bin/bash
# ClickHouse服务器的连接配置
CLICKHOUSE_HOST="cc-gs5irjve4e02axxxx.public.clickhouse.ads.aliyuncs.com" #按需更改，本地备份需要填公网地址
CLICKHOUSE_PORT="9000"
CLICKHOUSE_USER="backup"
CLICKHOUSE_PASSWORD="xxxxxxxx" #按需更改
# 阿里云OSS认证信息
S3_ACCESS_KEY="输入ACCESS ID"
S3_SECRET_KEY="输入ACCESS SECRET"
S3_ENDPOINT="https://bucketname.oss-ap-southeast-1-internal.aliyuncs.com" #ck实例是新加坡的，这里可以通过OSS内网地址进行连接
# 获取所有数据库名称
DATABASES=$(clickhouse-client --host=$CLICKHOUSE_HOST --port=$CLICKHOUSE_PORT --user=$CLICKHOUSE_USER --password=$CLICKHOUSE_PASSWORD --query="SHOW DATABASES")
# 排除系统数据库
EXCLUDED_DATABASES=("system" "information_schema" "INFORMATION_SCHEMA")
# 日志文件带时间戳
LOG_FILE="backup_$(date +'%Y%m%d_%H%M%S').log"
# 设置超时时间（秒）
RECEIVE_TIMEOUT=86400 # 24小时
SEND_TIMEOUT=86400 # 24小时
# 备份
for DB in $DATABASES
do
# 检查是否为排除的数据库
if [[ ! " ${EXCLUDED_DATABASES[@]} " =~ " ${DB} " ]]; then
echo "Backing up database: $DB" | tee -a $LOG_FILE
BACKUP_PATH="${S3_ENDPOINT}/cc-gs5irjve4e02axxxx/$(date +'%Y%m%d')/${DB}" #按需更改，cc-gs5irjve4e02axxxx改为对应的ck实例id
# 执行备份命令并记录日志，增加超时设置
clickhouse-client --host=$CLICKHOUSE_HOST \
--port=$CLICKHOUSE_PORT \
--user=$CLICKHOUSE_USER \
--password=$CLICKHOUSE_PASSWORD \
--receive_timeout=$RECEIVE_TIMEOUT \
--send_timeout=$SEND_TIMEOUT \
--query="BACKUP DATABASE $DB TO S3('$BACKUP_PATH', '$S3_ACCESS_KEY', '$S3_SECRET_KEY')" &>> $LOG_FILE
# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "Backup completed for database: $DB" | tee -a $LOG_FILE
else
echo "Backup failed for database: $DB" | tee -a $LOG_FILE
fi
fi
done
echo "All backups completed." | tee -a $LOG_FILE
后台执行备份
chmod +x backup.sh
nohup ./backup.sh >/dev/null 2>&1 &
4、观察是否有在进行备份
方式一：查看日志文件
备份脚本会生成日志文件，可以查看
tail -f backup_20250911_142317.log
Backing up database: default
a24dde32-1a52-49d5-971c-ee9aa014e509 BACKUP_CREATED
Backup completed for database: default
Backing up database: number_major
方式二：看ck监控
登录阿里云clickhouse控制台，进入到clickhouse实例，备份时磁盘吞吐和IOPS会有突增
方式三：看OSS有无创建相应文件
登录阿里云OSS控制台，进到bucket查看有无对应文件生成
方式四：执行sql，看num_files和total_size有无增加
clickhouse :) SELECT
id,
name,
status,
start_time,
end_time,
num_files,
formatReadableSize(total_size) as total_size
FROM system.backups WHERE (status = 'CREATING_BACKUP')
ORDER BY start_time DESC;
SELECT
id,
name,
status,
start_time,
end_time,
num_files,
formatReadableSize(total_size) AS total_size
FROM system.backups
WHERE status = 'CREATING_BACKUP'
ORDER BY start_time DESC
Query id: c9811a01-fbc4-4de5-9423-9217f2e8c856
Row 1:
──────
id: 3936dd17-19ef-47f2-98a0-0efbeed09bf7
name: S3('https://xxxx.oss-ap-southeast-1-internal.aliyuncs.com/cc-gs5irjve4e02axxxx/20250911/number_major', 'xxxx', '[HIDDEN]')
status: CREATING_BACKUP
start_time: 2025-09-11 14:44:29
end_time: 1970-01-01 08:00:00
num_files: 5254
total_size: 99.92 GiB
1 row in set. Elapsed: 0.002 sec.
5、备份成功
日志文件输出“All backups completed”代表备份成功
可以通过sql查看已成功完成的备份任务
SELECT * FROM system.backups
WHERE (status = 'BACKUP_CREATED')
总结：
备份大小：约160G
持续时长：13分钟
ck实例CPU波动：平均约0.4% → 最高达18.5%
ck实例内存波动：平均约2.5% → 最高达3.04%
注意上OSS查看对应的库的路径有metadata目录，否则恢复的时候会报错No such file or directory. (FILE_DOESNT_EXIST)
6、从OSS下载备份文件到本地
安装ossutil
安装文档：
https://help.aliyun.com/zh/oss/developer-reference/install-ossutil2?spm=a2c4g.11186623.0.0.62354e3e2K1HrV
#下载ossutil压缩包
curl -o ossutil-2.1.2-linux-amd64.zip https://gosspublic.alicdn.com/ossutil/v2/2.1.2/ossutil-2.1.2-linux-amd64.zip
#解压
yum -y install unzip
unzip ossutil-2.1.2-linux-amd64.zip
#实现ossutil的全局调用
cd ossutil-2.1.2-linux-amd64
chmod 755 ossutil
sudo mv ossutil /usr/local/bin/ && sudo ln -s /usr/local/bin/ossutil /usr/bin/ossutil
#验证
ossutil
ossutil is a tool for managing OSS (Object Storage Service) data.
Usage: ossutil [command]
Available Commands:
api The API-level commands
append Append content to a appendable object
cat Concatenate an object to standard output
config Creates a config file and stores configuration settings and credentials
cp Upload, Download or Copy Objects
du Get the bucket or the specified prefix(directory) storage size
hash Get the hash value of file
ls List buckets or objects
mb Creates a bucket
mkdir Create an object with name suffix '/'
presign Generate a pre-signed URL for object
probe Detection command
rb Delete bucket
restore Restore frozen state object to read ready status
revert Revert the deleted object to the latest versioning state
rm Deletes objects
set-props Set the property of objects
stat Display meta information of bucket or objects
sync Sync the local file directory or objects from the source to the destination
unrestore Clean an object restored from Archive or Cold Archive state
update Update ossutil
version Show version
Additional help topics:
filter more information about filter flags, and how to use it in commands
Use "ossutil [command] --help" for more information about a command.
Use "ossutil [topic]" for more information about a topic.
0.001355(s) elapsed
配置ossutil
ossutil config
The command creates a config file and stores configuration settings and credentials.
Please enter the config file name,the file name can include path(default "/root/.ossutilconfig", carriage return will use the default file. If you specified this option to other file, you should specify --config-file option to the file when you use other commands):
No config file entered, will use the default config file "/root/.ossutilconfig"For the following settings, carriage return means skip the configuration.
Please enter Access Key ID [****************id]:输入ACCESS ID
input:xxxx
Please enter Access Key Secret [****************sk]:输入ACCESS SECRET
input:xxxx
Please enter Region [None]:ap-southeast-1
input:ap-southeast-1
Please enter Endpoint (optional, use public endpoint by default) [None]:
input:
16.576456(s) elapsed
cat /root/.ossutilconfig
[default]
accessKeyId=xxxx
accessKeySecret=xxxx
region=ap-southeast-1
创建本地备份目录，并授权
ClickHouse 通常以 clickhouse 系统用户身份运行，所以要授权，否则恢复的时候会报错Permission denied [“/var/lib/clickhouse/backup/cc-gs5irjve4e02axxxx/default/.backup”]. (STD_EXCEPTION)
mkdir -p /var/lib/clickhouse/backup/
chown -R clickhouse:clickhouse /var/lib/clickhouse/backup/
从OSS下载备份文件到本地
ossutil cp -r oss://allck-backup/cc-gs5irjve4e02axxxx/20250911/ /var/lib/clickhouse/backup/cc-gs5irjve4e02axxxx/20250911/
等待下载即可完成本地备份！
二、恢复
1、从本地文件恢复
这里我本地原来已经部署了一个clickhouse，先更改本地的clickhouse的配置文件
vim /etc/clickhouse-server/config.xml
#将<backups>下的<allowed_path>改为/var/lib/clickhouse/backup
<allowed_path>/var/lib/clickhouse/backup</allowed_path>
#重启clickhouse-server
systemctl restart clickhouse-server
连接本地clickhouse恢复数据，下面找default库为例子进行恢复
clickhouse-client --user=root
ClickHouse client version 25.7.1.3997 (official build).
Connecting to localhost:9000 as user root.
Password for user (root):
Connecting to localhost:9000 as user root.
Connected to ClickHouse server version 25.7.1.
Warnings:
* Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
localhost :)
localhost :) RESTORE DATABASE default FROM File('/var/lib/clickhouse/backup/cc-gs5irjve4e02axxxx/default/')
RESTORE DATABASE default FROM File('/var/lib/clickhouse/backup/cc-gs5irjve4e02axxxx/default/')
Query id: 114720bf-f318-44a9-aafd-ee1d033a4bfc
┌─id───────────────────────────────────┬─status───┐
1. │ e9a83108-93c5-45c5-bc2a-cd394b2fdb5e │ RESTORED │
└──────────────────────────────────────┴──────────┘
1 row in set. Elapsed: 0.008 sec.
#上面恢复命令看到RESTORED 为恢复成功