高级DBA教你clickhouse 备份导出迁移技术副shell批量脚本例子

一、数据库常规备份

clickhouse没有很方便某个库整个备份导出导入的方法，只能逐一表迭代导出，或者所有表迭代导入。这个原则性不同跟MYSQL其他数据库都不一样。整个导出导入过程比较繁琐。
不能一次性导出表结构

导出数据
步骤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"
 
 
  done  < `ls /home/clickhouse_backup/table.txt`
done  < `ls /home/clickhouse_backup/db.txt`

sed -i 's/"CREATE/;"CREATE/g' ${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/

四、补充笔记

clickhouse从postgresql数据库同步某个表命令
把postgresql某个表同步到clickhouse的命令

CREATE TABLE 表名 ENGINE = MergeTree
order by 主键 AS select * from  postgresql('IP地址:5432','PGSQL数据库名称','表名','用户名','用户名PW') ;  #clickhouse从postgresql数据库同步某个表命令
把postgresql某个表同步到clickhouse的命令

表结构修改（alter）
1、添加字段：
alter table 表名 add column 字段名 字段类型 after 已有字段
alter table 表名 add column 字段名 字段类型

2、删除字段：
alter table 表名 drop column 字段名 字段类型 after 已有字段
alter table 表名 drop column 字段名

3、修改字段：
alter table 表名modify column 字段名 字段类型

4、增加注释：
alter table 表名comment 字段名 ‘注释’

5、移动表|重命名：
rename table a to b
rename table a to newdb.a
rename table a to newdb.b

表数据操作（数据要用单引号）
insert、select和其他数据库一样
修改、删除操作要用到alter关键字

INSERT INTO default.test (test, ip, created_at, updated_at) VALUES(’’, ‘’, ‘’, ‘’);

SELECT test, ip, created_at, updated_at FROM default.test;

ALTER TABLE default.test UPDATE test=’’, ip=’’, created_at=’’, updated_at=’’;

ALTER TABLE default.test DELETE WHERE test=’’ AND ip=’’ AND created_at=’’ AND updated_at=’’;

如：删除test表中的所有数据
alter table test delete where 1=1

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！