前置:Linux系统本地已经部署Clickhouse数据库

方案

1. 每天进行全量备份,保留3天

2. 每7天为一周期进行一次全量和多次增量备份,保留7天(暂未实现)

实现流程

安装clickhouse-backup

1. 官网下载解压本地压缩tar -xzvf clickhouse-backup-linux-amd64.tar.gz

2. 移动文件,使其能够全局执sudo mv clickhouse-backup /usr/local/bin/

3. 添加执行权chmod +x /usr/local/bin/clickhouse-backup

4. 验证安装成clickhouse-backup --help

配置clickhouse-backup

1. 创建配置文件
   mkdir -p /etc/clickhouse-backup && vim /etc/clickhouse-backup/config.yml

2. 编辑配置文件,注意port端口是clickhouse-backup的端口一般写9000

clickhouse:
  username: default
  password: "hontye_buyaowangjiwoa"
  host: 127.0.0.1
  port: 9000 
   
backup:
  backup_path: /var/lib/clickhouse/backup

编写自动备份脚本

编写自动备份脚vim clickhouse_autobackup.sh

#!/bin/bash
# 1. 全局定义
DATE=$(date +%Y-%m-%d-%H-%M-%S)
BACKUP_BIN="/usr/local/bin/clickhouse-backup"
# 2. 创建全量备份
$BACKUP_BIN create "daily-$DATE"
sleep 2
# 3. 删除3天前的备份
$BACKUP_BIN delete local $($BACKUP_BIN list | grep "daily-" | awk '{print $1}' | head -n -3)

加权并移动脚chmod +x clickhouse_autobackup.sh && mkdir -p /opt/scripts/ && mv clickhouse_autobackup.sh /opt/scripts/

设定自动任务

1. 编辑系统crontacrontab -e

2. 添加任务,每天23:00自动全量备份数据库到默认备份位置,每月1号0:00自动清理上月的日志文件

0 23 * * * /usr/local/bin/clickhouse_autobackup.sh >> /var/log/clickhouse_autobackup.log 2>&1
0 0 1 * * rm -rf /var/log/clickhouse_autobackup.log

编写用户操作脚本

编写用户操作脚vim backup_tools.sh

#!/bin/bash
# 全局定义
DATE=$(date +%Y-%m-%d-%H-%M-%S)
BACKUP_DIR="/var/lib/clickhouse/backup"
BACKUP_BIN="/usr/local/bin/clickhouse-backup"

# 显示帮助信息
show_help() {
    echo "用法: $0 [参数]"
    echo ""
    echo "参数说明："
    echo "  -h                  显示本帮助信息"
    echo "  -backup             执行一次手动备份,名称为 manual_YYYY-MM-DD-HH-MM-SS"
    echo "  -restore <NAME>     还原指定备份,<NAME>为备份名称"
    echo "  -list               显示所有备份"
}

# 备份
do_backup() {
    BACKUP_NAME="manual_$DATE"
    echo "执行备份：$BACKUP_NAME"
    $BACKUP_BIN create "$BACKUP_NAME"
    if [ $? -eq 0 ]; then
        echo "✅ 备份成功：$BACKUP_NAME"
    else
        echo "❌ 备份失败，请检查 clickhouse-backup 配置"
    fi
}

# 还原
do_restore() {
    if [ -z "$1" ]; then
        echo "❌ 错误：缺少备份名称参数"
        exit 1
    fi

    BACKUP_NAME="$1"
    if [ ! -d "$BACKUP_DIR/$BACKUP_NAME" ]; then
        echo "❌ 指定的备份不存在：$BACKUP_NAME"
        exit 1
    fi

    echo "⚠️ 将还原备份：$BACKUP_NAME"
    read -p "是否确认还原？这将覆盖现有数据！[y/N]: " confirm
    if [[ "$confirm" =~ ^[Yy]$ ]]; then
        $BACKUP_BIN restore "$BACKUP_NAME"
        if [ $? -eq 0 ]; then
            echo "✅ 还原成功"
        else
            echo "❌ 还原失败"
        fi
    else
        echo "已取消还原操作"
    fi
}

# 显示备份列表
list_backups() {
    echo "📦 当前备份列表："
    $BACKUP_BIN list | sed '1,5d;$d'
}

# 参数判断
case "$1" in
    -h)
        show_help
        ;;
    -backup)
        do_backup
        ;;
    -restore)
        shift
        do_restore "$1"
        ;;
    -list)
        list_backups
        ;;
    *)
        echo "❌ 未知参数：$1"
        show_help
        exit 1
        ;;
esac

加chmod +x backup_tools.sh