clickhouse-backup 备份还原

clickhouse-backup 部署

下载安装

tar xf clickhouse-backup-linux-amd64.tar.gz

cp build/linux/amd64/clickhouse-backup /usr/local/sbin/

验证版本信息

/usr/local/sbin/clickhouse-backup -v

Version:         2.8.0
Git Commit:      4598867acacfa854c10133ad4a9631f7abdc6c77
Build Date:      2026-07-16
Runtime Architecture:    linux / amd64
Build Architecture:      linux/amd64
FIPS 140-3:      false

生成配置文件

mkdir -p /etc/clickhouse-backup

/usr/local/sbin/clickhouse-backup default-config > /etc/clickhouse-backup/config.yml

按需修改

general:
  remote_storage: sftp         
  backups_to_keep_local: 5
  backups_to_keep_remote: 5
clickhouse:
  host: localhost
  port: 39000                   # 你的端口
  username: default
  password: ""                  # 如有密码则填写
  skip_tables:
    - system.*
    - INFORMATION_SCHEMA.*
    - information_schema.*
    
sftp:
    address: ""
    port: 
    username: ""
    password: ""
    key: ""
    path: "/data/backup/clickhouse"
    object_disk_path: ""
    compression_format: tar
    compression_level: 1
    concurrency: 120
    max_packet_size: 0
    debug: false

查看所有表

clickhouse-backup tables

备份测试

/usr/local/sbin/clickhouse-backup create test_conn

备份路径在数据目录下：
'/data/clickhouse/lib/backup/test_conn'
'/data/clickhouse/hdd/backup/test_conn'
'/data/clickhouse/ssd/backup/test_conn'

查看备份信息

/usr/local/sbin/clickhouse-backup list

2026-07-21 14:38:13.674 INF pkg/clickhouse/clickhouse.go:1272 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2026-07-21 14:38:13.678 INF pkg/clickhouse/clickhouse.go:1272 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present, countIf(name='cache_path') AS is_cache_path_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies') 
2026-07-21 14:38:13.688 INF pkg/clickhouse/clickhouse.go:1272 > SELECT d.path AS path, argMin(d.name, d.cache_path != '') AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d  LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2026-07-21 14:38:13.694 INF pkg/clickhouse/clickhouse.go:384 > clickhouse connection closed
test_conn   2026-07-21 14:32:18   local      all:846.33GiB,data:846.31GiB,arch:0B,obj:0B,meta:17.73MiB,rbac:0B,conf:0B,nc:0B   regular

删除备份信息

clickhouse-backup delete local test_conn

查看数据库表引擎

SELECT database, name, engine FROM system.tables WHERE database NOT IN ('system','INFORMATION_SCHEMA','information_schema') ORDER BY database, name;

ReplicatedMergeTree  ReplicatedReplacingMergeTree 可以同步其它节点数据

备份数据

备份到sftp

clickhouse-backup create_remote full_migration_202607240952

验证远端备份数据

clickhouse-backup list

full_migration_202607240952   2026-07-24 09:54:55   local       all:854.61GiB,data:854.59GiB,arch:0B,obj:0B,meta:17.74MiB,rbac:0B,conf:0B,nc:0B          regular
full_migration_202607240952   2026-07-24 11:12:22   remote      all:856.12GiB,data:854.59GiB,arch:856.10GiB,obj:0B,meta:23.30MiB,rbac:0B,conf:0B,nc:0B   tar, regular

还原到新节点

验证备份数据

clickhouse-backup list

full_migration_202607240952   2026-07-24 09:54:55   local       all:854.61GiB,data:854.59GiB,arch:0B,obj:0B,meta:17.74MiB,rbac:0B,conf:0B,nc:0B          regular
full_migration_202607240952   2026-07-24 11:12:22   remote      all:856.12GiB,data:854.59GiB,arch:856.10GiB,obj:0B,meta:23.30MiB,rbac:0B,conf:0B,nc:0B   tar, regular

恢复 schema

clickhouse-backup restore_remote --schema full_migration_202607240952

恢复数据

clickhouse-backup restore_remote --data --rm full_migration_202607240952

等待副本同步

SELECT database, table, absolute_delay, queue_size, is_readonly, log_max_index - log_pointer AS log_pending FROM system.replicas WHERE database NOT IN ('system');

is_readonly = 0

absolute_delay = 0

queue_size = 0

三台都达标 → 数据一致。

增量备份

clickhouse-backup create_remote --diff-from=full_migration_202607240952 incr_final_202607241419

full_migration_202607240952   2026-07-24 10:01:12   local                                   all:844.63GiB,data:844.62GiB,arch:0B,obj:0B,meta:17.61MiB,rbac:0B,conf:0B,nc:0B          regular
incr_final_202607241419       2026-07-24 14:22:52   local                                   all:844.67GiB,data:844.65GiB,arch:0B,obj:0B,meta:17.62MiB,rbac:0B,conf:0B,nc:0B          regular
full_migration_202607240952   2026-07-24 11:32:07   remote                                  all:846.12GiB,data:844.62GiB,arch:846.10GiB,obj:0B,meta:23.14MiB,rbac:0B,conf:0B,nc:0B   tar, regular
incr_final_202607241419       2026-07-24 14:23:22   remote   +full_migration_202607240952   all:1.66GiB,data:844.65GiB,arch:1.64GiB,obj:0B,meta:20.02MiB,rbac:0B,conf:0B,nc:0B       tar, regular

恢复增量

clickhouse-backup restore_remote --data --rm incr_final_202607241419

....
2026-07-27 13:12:34.611 INF pkg/backup/restore.go:2114 > done, backup=incr_final_202607241419, operation=restore_data, duration=3h4m9.158s
2026-07-27 13:12:34.612 INF pkg/backup/restore.go:335 > done, duration=3h4m10.047s, operation=restore, version=2.8.0
2026-07-27 13:12:34.612 INF pkg/clickhouse/clickhouse.go:384 > clickhouse connection closed

备份单个表

单表备份

clickhouse-backup create -t "dbname.table_name" backup_single_table

-t：指定表名，格式为 库名.表名
备份文件落在默认备份目录下（如 /data/clickhouse/{lib,hdd,ssd}/backup/backup_single_table/）

备份多张表

clickhouse-backup create -t "db1.table1,db1.table2,db2.table3" backup_multi_table

多个表用逗号分隔。

单表还原

只还原表结构

clickhouse-backup restore backup_single_table --table dbname.table_name --schema

只还原数据

clickhouse-backup restore backup_single_table --table dbname.table_name --data

同时还原结构和数据

clickhouse-backup restore backup_single_table --table dbname.table_name --schema --data

单表增量备份

先创建一个基础备份

clickhouse-backup create -t "db1.table1" base_backup_table1

基于基础备份创建单表增量

# 本地增量
clickhouse-backup create -t "db1.table1" --diff-from-remote=base_backup_table1 incr_table1

# 或者创建并上传到远程
clickhouse-backup create_remote -t "db1.table1" --diff-from=base_backup_table1 incr_table1_remote

还原单表增量

# 先还原基础备份的单表
clickhouse-backup restore base_backup_table1 --table db1.table1 --schema --data

# 再按顺序还原增量
clickhouse-backup restore incr_table1 --table db1.table1 --data

只备份表结构

基本命令格式

clickhouse-backup create --schema --tables=<database>.<table> <backup_name>

备份整个库的 Schema

clickhouse-backup create --schema --tables=my_database.* my_schema_backup

备份单个表的 Schema

clickhouse-backup create --schema --tables=my_database.my_table my_single_table_schema

备份多个指定表的 Schema

clickhouse-backup create --schema --tables=db1.table1,db2.table2 my_mixed_schema_backup

还原表结构

还原单个表结构

clickhouse-backup restore --schema --tables=my_database.my_table my_single_table_schema

常用命令速查

场景	命令
备份单表	`clickhouse-backup create -t "db.table" backup_name`
还原单表结构	`clickhouse-backup restore backup_name --table db.table --schema`
还原单表数据	`clickhouse-backup restore backup_name --table db.table --data`
还原单表（结构+数据）	`clickhouse-backup restore backup_name --table db.table --schema --data`
单表增量备份	`clickhouse-backup create -t "db.table" --diff-from-remote=base_backup incr_name`
查看备份列表	`clickhouse-backup list`
删除本地备份	`clickhouse-backup delete local backup_name`

参考文档

https://github.com/Altinity/clickhouse-backup

posted @ 2026-07-27 13:58 小吉猫阅读(10) 评论(0) 收藏举报

刷新页面返回顶部

小吉猫

clickhouse-backup 备份还原

clickhouse-backup 部署

下载安装

验证版本信息

生成配置文件

按需修改

查看所有表

备份测试

查看备份信息

删除备份信息

查看数据库表引擎

备份数据

备份到sftp

验证远端备份数据

还原到新节点

验证备份数据

恢复 schema

恢复数据

等待副本同步

增量备份

恢复增量

备份单个表

单表备份

备份多张表

单表还原

只还原表结构

只还原数据

同时还原结构和数据

单表增量备份

先创建一个基础备份

基于基础备份创建单表增量

还原单表增量

只备份表结构

基本命令格式

备份整个库的 Schema

备份单个表的 Schema

备份多个指定表的 Schema

还原表结构

还原单个表结构

常用命令速查

参考文档

公告