一、数据库常规备份
导出数据
步骤1 逐一导出表结构（SQL文件）
步骤2 逐一导出表数据（tsv文件）
导出数据
步骤1 逐一导出表结构（SQL文件）
步骤2 逐一导出表数据（tsv文件）

clickhouse导出建表语句的shell脚本代码

#!/bin/bash

OUTDIR=/home/clickhouse_backup/
 
clickhouse-client --user default --password 123456  -q "SHOW DATABASES" > /home/clickhouse_backup/db.txt
while read -r db 
do
        clickhouse-client  --user default --password 123456 -q "SHOW TABLES FROM $db" > /home/clickhouse_backup/table.txt
        #echo "--------------------------------${db}"
        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 table $table from database $db"
 
                # dump schema
                clickhouse-client --user default --password 123456 -q "SHOW CREATE TABLE ${db}.${table} format CSV" >> "${OUTDIR}/schema.sql"

clickhouse导出数据的shell脚本

#!/bin/bash

echo "back_up_start"


clickhouse-client --user default --password 123456   --query="select * from nasen.test1" >/home/clickhouse_backup/test1.tsv
clickhouse-client --user default --password 123456   --query="select * from nasen.test2" >/home/clickhouse_backup/test2.tsv
clickhouse-client --user default --password 123456   --query="select * from nasen.test3" >/home/clickhouse_backup/test3.tsv

echo "back_up_finish"

echo "zip now backUP clickhouse  files!!"

#gzip /home/clickhouse_backup/test1.tsv

echo "zip finish!!"

导出某个表数据命令

clickhouse-client --user default --password 123456 --query="select * from nasen.test1" >/home/test1.tsv

gzip /home/clickhouse_backup/test1.tsv  #压缩
gunzip /home/clickhouse_backup/test1.tsv.gz #解压缩

导入某个表数据命令

cat /home/test1.tsv | clickhouse-client --user default --password 123456 --query="insert into nasen.test1 FORMAT TSV"

二、clickhouse之间互相迁移

create table nasen.test1 ENGINE = MergeTree
order by id AS as  select *  from remote ('192.168.2.1','nasen.test1 ','default','123456');

insert into nasen.test1  select *  from remote select *  from remote ('192.168.2.1','nasen.test1 ','default','123456');

三、借助数据库工具导出导入

https://dbeaver.io/download/