GBase8a单库迁移-卸数方式

功能介绍： 将数据库中的表数据卸成数据文件，再通过加载方式，加载到数据库。

1、导出脚本 [runoutfile.sh]

[gbase@keep-gbase8a-node1 db_load]$ cat runoutfile.sh
#!/bin/bash

base_path=`dirname $0`
# 通过传参方式，传递库名
dbname=$1
# 操作系统用户
dbaUser=gbase
# 数据库用户
dbUser=gbase
# 数据库用户密码
dbapassword=gbase20110531
# 数据目录
data_path=$base_path/data
# 日志目录
log_path=$base_path/log
time_now=`date '+%Y%m%d_%H%M%S'`

# dba用户判断
if [ `whoami` != "$dbaUser" ];then
        echo "Please use $dbaUser run me!"
        exit 1
fi
# 入参判断
if [ $# -ne 1 ]
then
        echo "Usage: sh runoutfile.sh dbName"
        exit 1
fi
# data文件路径
if [ ! -d "$data_path" ]
then
        mkdir -p "$data_path/$dbname"
fi
# 日志目录
if [ ! -d "$log_path" ]
then
        mkdir -p "$log_path"
fi

# 导出表结构
# 如果不需要导出存储过程及函数，把 -R 删掉
# 单库导出，不带建库语句
gcdump $dbname -R > gcdump_${dbname}.sql

# 导出文件是否只需要在$data_path下
# gccli -u$dbUser -p$dbapassword -D$1 -N -e "set global gbase_export_directory=1;"
# 存在视图会把数据导出
# gccli -u$dbUser -p$dbapassword -D$1 -N -e "show tables;" > $1_tb.tblist

tblist=`gccli -u$dbUser -p$dbapassword -D$dbname -N -e "
select TABLE_NAME from information_schema.tables
where table_type !='SYSTEM VIEW' and table_type != 'VIEW' and engine = 'EXPRESS' and TABLE_SCHEMA = '${dbname}'
group by TABLE_NAME ;
"`

# echo $tblist
pwd_path=`realpath ${data_path}/$dbname`
for tbname in $tblist
do
        gccli -u$dbUser -p$dbapassword -D$dbname -vvv -e "
select * from ${dbname}.${tbname} into outfile '${pwd_path}/${tbname}'
fields terminated by '|\x01'
LINES TERMINATED BY '@\x02' null_value '%NULL_VALUE%'
character set utf8 WRITEMODE BY OVERWRITES ;
" >> $log_path/runoutfile_${time_now}.log
done

2、导入脚本 [runloaddata.sh]

[gbase@keep-gbase8a-node1 db_load]$ cat runloaddata.sh
#!/bin/bash

base_path=`dirname $0`
# 通过传参方式，传递库名
dbname=$1
# 操作系统用户
dbaUser=gbase
# 数据库用户
dbUser=gbase
# 数据库用户密码
dbapassword=gbase20110531
# 数据目录
data_path=$base_path/data
# 日志目录
log_path=$base_path/log
time_now=`date '+%Y%m%d_%H%M%S'`

# dba用户判断
if [ `whoami` != "$dbaUser" ];then
        echo "Please use $dbaUser run me!"
        exit 1
fi
# 入参判断
if [ $# -ne 1 ]
then
        echo "Usage: sh runoutfile.sh dbName"
        exit 1
fi
# data文件路径
if [ ! -d "$data_path" ]
then
        mkdir -p "$data_path/$dbname"
fi
# 日志目录
if [ ! -d "$log_path" ]
then
        mkdir -p "$log_path"
fi


# 建库
gccli -u$dbUser -p$dbapassword -vvv -e " create database if not exists ${dbname} ;" >> $log_path/runloaddata_${time_now}.log
# 导入表结构
gccli -u$dbUser -p$dbapassword -D${dbname} -vvv -e "source $base_path/gcdump_${dbname}.sql" >> $log_path/runloaddata_${time_now}.log

tblist=`gccli -u$dbUser -p$dbapassword -D$dbname -N -e "
select TABLE_NAME from information_schema.tables
where table_type !='SYSTEM VIEW' and table_type != 'VIEW' and engine = 'EXPRESS' and TABLE_SCHEMA = '${dbname}'
group by TABLE_NAME ;
"`

# echo $tblist
pwd_path=`realpath ${data_path}/$dbname`
for tbname in $tblist
do
        gccli -u$dbUser -p$dbapassword -D$dbname -vvv -e "
LOAD DATA INFILE 'file://192.168.122.21/${pwd_path}/${tbname}/${tbname}' INTO TABLE ${dbname}.${tbname}
NULL_VALUE '%NULL_VALUE%' DATA_FORMAT 3
fields terminated by '|\x01' lines terminated by '@\x02'
datetime format '%Y-%m-%d %H:%i:%s.%f' ;
" >> $log_path/runloaddata_${time_now}.log
done

3、操作步骤

（1）导出语句：sh runoutfile.sh test2

（2）导入语句：sh runloaddata.sh test2

注：

（1）导入时，可以修改加载语句，可通过ftp/sftp/gbfs等协议加载

（2）导出文件时，如果使用了gbase_export_directory=1参数，需要修改load data路径

（3）此脚本导入数据时，会自动删表重建。如果需要不自动重建表，可把导入表结构语句注释。