clickhouse 的导入导出

运维 , , , , 评论

linux

备份脚本

  • 可将以下内容复制到backCk.sh 添加可执行权限
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    #!/bin/bash

    # 定义属性
    PASSWORD="111111"

    BACKUP=/home/dbback
    TMPBACKUP=/tmp/ckdbback

    DATETIME=$(date +%Y%m%d%H%M%S)

    # 打印日志
    function log_correct () {
        USER=$(whoami)
        echo "${DATETIME} ${USER} execute $0 [INFO] $@ " >> "/home/shell/backup_log.txt"
    }

    log_correct "开始执行 CK 备份任务"

    # 创建备份目录
    mkdir -p "${TMPBACKUP}/$DATETIME"

    cd ${TMPBACKUP}/$DATETIME/
    clickhouse-client --user=default --password=$PASSWORD --query="SELECT * FROM test INTO OUTFILE './test.binary' FORMAT RowBinary"

    tar -cvf "CKBACK_$DATETIME.tar" *.binary
    mv "CKBACK_$DATETIME.tar" $BACKUP
    log_correct "数据库【CK】已备份到：${BACKUP} 下"

    rm -rf $TMPBACKUP

    # 删除7天前的文件
    #find $BACKUP -type f -mtime +7 -exec rm -f {} \;

    log_correct "完成 CK 备份任务"

还原导出的备份数据

1
2
3
4
#登陆到clickhouse
clickhouse-client --host 127.0.0.1  --port 9000 --user default --password  "111111"
#从备份文件还原数据
INSERT INTO weilai.sd_chukou FROM INFILE '/ckback/test.binary' FORMAT RowBinary

Windows

docker下运行clickhouse的备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@title CK backup start
@echo off
setlocal enabledelayedexpansion
@color 0a
:: --------------------参数设置------------------------
:: 设置时间变量
echo --------------------CK备份开始--------------------

set YEAR=%date:~0,4%
set MONTH=%date:~5,2%
set DAY=%date:~8,2%
:: 如果在 dos 下输入 time 返回的不是 24 小时制（没有 0 填充），请自行修改此处 
set HOUR=%time:~0,2%
set MINUTE=%time:~3,2%
set SECOND=%time:~6,2%

set "Ymd=%YEAR%%MONTH%%DAY%"


docker exec -it --user root 800d5e4b1667  bash -c " clickhouse-client --user=default --password=111111 --query=\"SELECT * FROM test INTO OUTFILE '/var/log/clickhouse-server/test%Ymd%.binary' FORMAT RowBinary\" "

:: --------------------结束备份------------------------
@echo on

完结

holdenJava & Linux

聊聊？