彻底解决CK备份臃肿！clickhouse-backup 安全增量备份生产级方案（可直接落地）

clickhouse: 
  freeze_by_part: true # 必须开启！增量备份核心依赖配置

ssh root@110.12.117.121 "mkdir -p /home/data_dev/clickhouse_backup/base/"

#!/bin/bash
export PATH=/usr/local/bin:/usr/bin:/bin:$PATH
# ===== 通用配置 =====
. /home/tools/wechat_notify.sh
SERVER_IP=$(hostname -I | awk '{print $1}')
REMOTE_IP="147.126.112.160"
# 远程全量基准标记文件
BASE_MARKER="/home/data_dev/clickhouse_backup/base/.last_full_backup"
# 本地增量追踪文件，记录最新全量基准
LOCAL_TRACKER="/home/tools/.ck_incremental_tracker"

# 时间参数
DOW=$(date +%u) # 1=周一 7=周日
TIMESTAMP=$(date +%Y-%m-%dT%H-%M-%S)
START_TIME=$(date +%s)

# ====================== 周日：执行全量基准备份 ======================
if [ "$DOW" -eq 7 ]; then
 BACKUP_NAME="full_global_backup_${TIMESTAMP}"
 # 本地创建全量备份
 /usr/local/bin/clickhouse-backup create -t webfunny_cloud_db.* $BACKUP_NAME
 if [ $? -ne 0 ]; then
 send_wechat "国内CK全量备份失败❌" "服务器IP：${SERVER_IP}\n备份名称：${BACKUP_NAME}\n阶段：本地创建全量备份失败"
 exit 1
 fi
 # 上传至远程存储
 /usr/local/bin/clickhouse-backup upload $BACKUP_NAME
 UPLOAD_CODE=$?
 if [ ${UPLOAD_CODE} -eq 0 ]; then
 # 本地、远程双记录最新全量基准
 echo "$BACKUP_NAME" > "$LOCAL_TRACKER"
 ssh root@${REMOTE_IP} "echo '$BACKUP_NAME' > ${BASE_MARKER}"
 fi

# ====================== 周一~周六：执行增量备份 ======================
else
 # 校验是否存在全量基准备份
 if [ ! -f "$LOCAL_TRACKER" ]; then
 send_wechat "国内CK增量备份失败❌" "服务器IP：${SERVER_IP}\n原因：未找到全量基准备份记录，请先执行一次全量备份"
 exit 1
 fi
 BASE_BACKUP=$(cat "$LOCAL_TRACKER")
 BACKUP_NAME="inc_global_backup_${TIMESTAMP}"

 # 本地创建增量备份
 /usr/local/bin/clickhouse-backup create -t webfunny_cloud_db.* $BACKUP_NAME
 if [ $? -ne 0 ]; then
 send_wechat "国内CK增量备份失败❌" "服务器IP：${SERVER_IP}\n备份名称：${BACKUP_NAME}\n阶段：本地创建增量备份失败"
 exit 1
 fi

 # 基于远程全量基准，增量上传差异数据
 /usr/local/bin/clickhouse-backup upload \
 --diff-from-remote "$BASE_BACKUP" \
 $BACKUP_NAME
 UPLOAD_CODE=$?
fi

# ====================== 耗时统计 ======================
END_TIME=$(date +%s)
COST_SEC=$((END_TIME - START_TIME))
COST_H=$((COST_SEC / 3600))
COST_M=$(((COST_SEC % 3600) / 60))
COST_S=$((COST_SEC % 60))
TIME_TEXT="${COST_H}时${COST_M}分${COST_S}秒"

# ====================== 微信结果告警 ======================
if [ ${UPLOAD_CODE} -eq 0 ]; then
 send_wechat "ClickHouse备份成功✅" "源服务器IP：${SERVER_IP} → 目标：${REMOTE_IP}\n备份名称：${BACKUP_NAME}\n类型：$([ "$DOW" -eq 7 ] && echo '全量基准' || echo '增量(基准:'${BASE_BACKUP}')')\n总耗时：${TIME_TEXT}"
else
 send_wechat "国内CK远端上传失败❌" "源服务器IP：${SERVER_IP} → 目标：${REMOTE_IP}\n备份名称：${BACKUP_NAME}\n本地备份完整，SFTP远端上传失败"
 exit 1
fi

# ====================== 清理本地备份缓存 ======================
rm -rf /var/lib/clickhouse/backup/$BACKUP_NAME

# ===== 每周三定时清理14天前过期备份 =====
if [ "$DOW" -eq 3 ]; then
 # 筛选远程老旧备份并删除，保留最近7天备份
 OLD_BACKUPS=$(docker exec clickhouse-server clickhouse-backup list remote 2>/dev/null | awk '{print $1}' | grep -E '^(full_|inc_)' | head -n -7)
 for OLD in $OLD_BACKUPS; do
 docker exec clickhouse-server clickhouse-backup delete remote "$OLD" 2>/dev/null
 done
fi

00 00 * * * PATH=/usr/local/bin:/usr/bin:/bin /bin/bash /home/tools/clickhouse_backup_incremental.sh > /home/tools/backup_incremental.log 2>&1

# 1、恢复基准全量备份（本周周日备份）
clickhouse-backup download full_global_backup_2026-06-21T00-00-01
clickhouse-backup restore full_global_backup_2026-06-21T00-00-01 -t "webfunny_cloud_db.*" --rm

# 2、叠加恢复对应日期增量备份
clickhouse-backup download inc_global_backup_2026-06-22T00-00-01
clickhouse-backup restore inc_global_backup_2026-06-22T00-00-01 -t "webfunny_cloud_db.*" --rm