1.下载clickhouse-backup
wget https://github.com/AlexAkulov/clickhouse-backup/releases/download/v1.0.0/clickhouse-backup.tar.gz
tar -zxvf clickhouse-backup.tar.gz # 解压
cd clickhouse-backup
cp clickhouse-backup /usr/local/bin/ # 将可执行文件复制到/usr/local/bin
mkdir /etc/clickhouse-backup # /etc下创建一个目录，用来放置配置文件config.yml
cp config.yml /etc/clickhouse-backup/
vim /etc/clickhouse-backup # 修改配置文文件
配置文件config.yml如下：
general:
remote_storage: sftp # 通过sftp，上传到远程服务器的话，需要这个参数,否则为none
max_file_size: 1099511627776
disable_progress_bar: false
backups_to_keep_local: 2 # 本地备份的个数，大于2则自动删除旧的备份，默认为0，不删除备份
backups_to_keep_remote: 2 # 远程备份的个数
log_level: info
allow_empty_backups: false
clickhouse:
username: default # 本地clickhouse的连接参数
password: ""
host: localhost
port: 9010
disk_mapping: {}
skip_tables:
- system.*
- db.*
- information_schema.*
- INFORMATION_SCHEMA.*
timeout: 5m
freeze_by_part: false
secure: false
skip_verify: false
sync_replicated_tables: true
skip_sync_replica_timeouts: true
log_sql_queries: false
s3:
access_key: ""
secret_key: ""
bucket: ""
endpoint: ""
region: us-east-1
acl: private
force_path_style: false
path: ""
disable_ssl: false
part_size: 536870912
compression_level: 1
compression_format: tar
sse: ""
disable_cert_verification: false
storage_class: STANDARD
gcs:
credentials_file: ""
credentials_json: ""
bucket: ""
path: ""
compression_level: 1
compression_format: tar
cos:
url: ""
timeout: 2m
secret_id: ""
secret_key: ""
path: ""
compression_format: tar
compression_level: 1
api:
listen: localhost:7171
enable_metrics: true
enable_pprof: false
username: ""
password: ""
secure: false
certificate_file: ""
private_key_file: ""
create_integration_tables: false
ftp:
address: ""
timeout: 2m
username: ""
password: ""
tls: false
path: ""
compression_format: tar
compression_level: 1
sftp:
address: "host"
port: 端口号
username: "用户名"
password: "密码"
key: ""
path: "/home/data_dev/clickhouse_backup" # 上传文件到远程服务器的路径
compression_format: tar
compression_level: 1
azblob:
endpoint_suffix: core.windows.net
account_name: ""
account_key: ""
sas: ""
container: ""
path: ""
compression_level: 1
compression_format: tar
sse_key: ""
2.本地和远程创建备份
clickhouse-backup tables # 查看可以备份的表
clickhouse-backup create # 全库备份，一般这个备份的位置是原clickhouse数据存储路径下新建了一个backup文件夹，这个文件夹下会存备份
clickhouse-backup create --database=<database_name> <backup_name>
其中 <database_name> 是你想要备份的数据库名称，<backup_name> 是你给这次备份指定的名字。如果省略 <backup_name>，则默认使用当前日期和时间作为备份名称。 例如，如果你要备份名为 mydb 的数据库，并将其命名为 mydb_backup_20230401，命令将是：
clickhouse-backup create --database=mydb mydb_backup_20230401
clickhouse-backup create -t 数据库名称.* 备份名称 备份某个数据库的全部表
clickhouse-backup create -t 数据库.表名1,数据库.表名2 # 多表备份，单表类似
clickhouse-backup list # 查看已经生成的备份，如果设置了remote_storage: sftp,此条命令还会检测是否成功连接上了远程服务器
clickhouse-backup restore --help # 查看数据恢复相关参数
# 使用scp，将备份上传到服务器上，如果config.yml中设置了sftp，则不需要这一步
scp -rp /var/lib/clickhouse/backup/备份名 name@host:/data/clickhouse-backup/
# 通过配置文件里的sftp上传备份到服务器
clickhouse-backup upload 备份名
3.crontab定时备份
crontab -e 或者vim /etc/crontab
输入以下内容
40 15 * * * sh /data/clickhouse_backup.sh > /data/a.log 2>&1 # 表示每天15：40分会运行clickhouse_backup.sh
vim /data/clickhouse_backup.sh
输入以下内容
#!/bin/bash
BACKUP_NAME=ch_backup_$(date +%Y-%m-%dT%H-%M-%S)
/usr/local/bin/clickhouse-backup create $BACKUP_NAME #本地备份
/usr/local/bin/clickhouse-backup upload $BACKUP_NAME # 本地备份之后，上传到远程服务器
4、如何恢复远程备份的文件
首先把远程备份的文件上传到数据服务器上
scp -r webfunny_cloud_db_backup_2024-08-28T00-00-01/** root@47.116.223.95:/var/lib/clickhouse/backup/20240829/因为备份的文件都是压缩的tar文件，所以要先对每个tar进行解压
写个解压脚本命令
#!/bin/bash
# 指定包含 .tar文件的目录
dir="/var/lib/clickhouse/backup/20240829/shadow/webfunny_cloud_db/"
# 遍历目录中的所有 .tar 文件
for tar_file in $(find "$dir" -type f -name "*.tar"); do
# 检查文件是否存在
if [ -f "$tar_file" ]; then
# 解压每个 .tar 文件
echo "Extracting $tar_file..."
result="${tar_file%.tar}"
aw=$(echo "$tar_file" | awk -F 'default_' '{print $1}')
a="default"
#mkdir "$result"
mkdir "$aw$a"
tar -xvf "$tar_file" -C "$aw$a"
else
echo "File does not exist: $tar_file"
fi
done然后执行恢复命令
clickhouse-backup restore 20240829 -s -d --rm