clickhouse 数据备份恢复脚本

1.创建数据备份目录

mkdir -p /data01/ck_table_backup

2.创建需要备份的数据库文件，把需要备份的数据库名写入到该文件中

echo "xuanji_dwd" > /data01/ck_table_backup/db.txt

3.上传脚本目录(脚本权限775 用户root)

cd /opt/xinghai/clickhouse/software/clickhouse/bin

4.执行备份脚本命令

sh ck_backup.sh

#!/bin/bash

## backup clickhouse data for databases (db.txt),to OUTDIR
# you must create $OUTDIR/db.txt which you want to backup,and run ck_backup.sh script
# 1. backup schema for all table
# 2. backup table data for engine=Distributed table to gz file

OUTDIR=/data01/ck_table_backup
DUMP_DATA="Y"
CK_BIN=$(dirname $(readlink -f $0))

mkdir $OUTDIR


# backup meta data
rm -rf ${OUTDIR}/schema.sql
while read -r db
do
	${CK_BIN}/clickhouse-client --port 9010 -u admin --password **  -q "SHOW TABLES FROM $db" > $OUTDIR/table_schema.txt
        while read -r table
	do
		if [ "$db" == "system" ]; then
                echo "skip system db"
                continue 2;
                fi

                if [[ "$table" == ".inner"* ]]; then
                echo "skip materialized view $table ($db)"
                continue;
                fi
		echo "export schema table $table from database $db"
		# dump schema
                ${CK_BIN}/clickhouse-client --port 9010 -u admin --password ** -q "SHOW CREATE TABLE ${db}.${table} format CSV" >> "${OUTDIR}/schema.sql"
	done < `ls $OUTDIR/table_schema.txt`	
done < `ls $OUTDIR/db.txt`

# distributed table list
rm -rf ${OUTDIR}/table.txt

while read -r db_name
do
	${CK_BIN}/clickhouse-client -u admin --password ** --port 9010 -q "select concat(database,'.',name) from system.tables where database='${db_name}' and engine='Distributed'" >> $OUTDIR/table.txt
done < `ls $OUTDIR/db.txt`

# backup table data
while read -r table_name 
do
	db_table=(${table_name//./ })
	db=${db_table[0]}
	table=${db_table[1]}
        if [ "$db" == "system" ]; then
        echo "skip system db"
        continue 2;
        fi

        if [[ "$table" == ".inner"* ]]; then
        echo "skip materialized view $table ($db)"
        continue;
        fi

        echo "dump table $table from database $db"

	if [ "$DUMP_DATA" == "Y" ]; then
	# dump 
	${CK_BIN}/clickhouse-client -u admin --password ** --port 9010 -q "SELECT * FROM ${db}.${table} FORMAT JSONEachRow"|gzip  > "${OUTDIR}/${table}_data.json.gz"
	fi

done  < `ls $OUTDIR/table.txt`

1.创建恢复表文件，只需要填写分布式表名即可 table_list.txt

echo "table_01" > /data01/ck_table_backup/table_list.txt

2 上传脚本目录(脚本权限775 用户root)

cd /opt/xinhai/clickhouse/software/clickhouse/bin

3 执行恢复脚本命令

sh ck_recovery.sh 恢复的数据库名

#!/bin/bash

OUTDIR=/data01/ck_table_backup
CK_BIN=$(dirname $(readlink -f $0))
DATABASE_NAME="$1"
TABLE_LIST=${OUTDIR}/table_list.txt

# format schema

echo "format schema.txt to schema_format.txt"
echo "create database ${DATABASE_NAME} on cluster xhraptor;" > ${OUTDIR}/schema_format.sql;
cat ${OUTDIR}/schema.sql |sed -r 's/"CREATE(.*)/CREATE\1 on cluster xhraptor/'|sed -r 's/^ENGINE(.*)"$/ENGINE\1 \n;/'|sed -r 's/^SETTINGS(.*)"$/SETTINGS\1 \n;/' >> ${OUTDIR}/schema_format.sql

# creat table
echo "create table from schema_format.txt"
${CK_BIN}/clickhouse-client --port 9010 -u admin --password **  --multiquery < ${OUTDIR}/schema_format.sql

# recovery data into table
while read -r table_name
do
	db_table=(${table_name//./ })
	db=${db_table[0]}
	table=${db_table[1]}
        echo "import table $table_name from ${OUTDIR}/${db}_${table}data.json.gz....."
	zcat ${OUTDIR}/${db}_${table}data.json.gz | ${CK_BIN}/clickhouse-client -u admin --password ** --port 9010 -q "INSERT INTO ${DATABASE_NAME}.$table_name FORMAT JSONEachRow" 

done < `ls ${TABLE_LIST}`