GBase 8a
安装配置
文章
GBASE安装DBLink部署步骤（含脚本）
发表于2024-02-22 11:08:25338次浏览2个评论
准备工作：
1）Gateway安装包、dblink名称gbase_dbilnk
2）数据源（gbase8a）IP：214.185.215.16 214.185.215.18 214.185.215.20；数据库用户：testuser/pass；库表：testdb.cdbc_262
脚本说明：
1）授权dblink数据库用户脚本set_permission.sh
#!/bin/bash
###############################################################################
##creator : Tupac Tu
##create time: 2024-02-01
##Decription: 设置数据节点数据库用户权限（传参：DBA用户 DBA密码 dblink用户）
##Method: sh set_permission.sh dbaUserName dbaUserPasswd dbUserName
##Version: 1.0
###############################################################################
#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
ScriptsDir=/opt/gbase_workspace/scripts/set_dblink
#参数设置
dbaUserName=$1
dbaUserPasswd=$2
dbUserName=$3
#获取配置信息(未用)
config()
{
gbaseMode=`cat ${configDir}/config |grep ^GBaseMode |awk -F= '{print $2}' | awk '{print $1}'`
instanceType=`cat ${configDir}/config |grep ^InstanceType |awk -F= '{print $2}' | awk '{print $1}'`
userName=`cat ${configDir}/config |grep ^UserName |awk -F= '{print $2}' | awk '{print $1}'`
userPasswd=`cat ${configDir}/config |grep ^Password |awk -F= '{print $2}' | awk '{print $1}'`
gccli_command="gccli -u${userName} -p${userPasswd}"
}
#设置数据库用户权限
set_permission()
{
if ! isCmdExist gcadmin; then echo " Failed: execute command (gcadmin) denied to the user"; exit 1; fi
if ! isCmdExist gccli; then echo " Failed: execute command (gccli) denied to the user"; exit 1; fi
if [ "" == "$dbaUserName" ] || [ "" == "$dbaUserPasswd" ] || [ "" == "$dbUserName" ];then echo "Usage: sh $0 dbaUserName dbaUserPasswd dbUserName"; exit 1; fi
check_password
sqlResult=$(${gccli_command} -u${dbaUserName} -p${dbaUserPasswd} -Ns -e "select user from gbase.user where user='${dbUserName}'")
if [ "${sqlResult}" == "" ]; then
echo " * This user is not available in GBase database! * "
exit 1
fi
gbaseMode=$(gcadmin showcluster d|grep "virtual cluster" |wc -l)
vcList=$(gcadmin showcluster d|grep "virtual cluster" | awk -F: '{print $2}' |sed 's/,//g')
gcadmin |grep coordinator[1-9] |awk '{print $4}' > ${configDir}/coor.list
if [ ${gbaseMode} -gt 0 ];then
> ${configDir}/data.list
for vcname in ${vcList}
do
gcadmin showcluster vc ${vcname}|grep node[1-9]|awk '{print $4}' >> ${configDir}/data.list
done
gcadmin showcluster |grep FreeNode[1-9] | awk '{print $4}' >> ${configDir}/data.list
else
gcadmin |grep node[1-9]|awk '{print $4}' > ${configDir}/data.list
fi
gccli -u${dbaUserName} -p${dbaUserPasswd} -e "grant all on gctmpdb.* to ${dbUserName}"
gccli -u${dbaUserName} -p${dbaUserPasswd} -e "grant select on gbase.* to ${dbUserName}"
while read nodeip
do
gccli -u${dbaUserName} -p${dbaUserPasswd} -h${nodeip} -P5050 -e "grant all on gctmpdb.* to ${dbUserName}"
gccli -u${dbaUserName} -p${dbaUserPasswd} -h${nodeip} -P5050 -e "grant select on gbase.* to ${dbUserName}"
done < ${configDir}/data.list
}
#校验密码有效性
check_password()
{
userPasswdLen="${#dbaUserPasswd}"
sqlResult=""
sqlResultLen=""
if [ "${userPasswdLen}" -eq 0 ];then
sqlResult="null password"
echo " * You didn't enter the password for the ${dbaUserName} user! * "
exit 1
else
sqlResult="$(${gccli_command} -u${dbaUserName} -p${dbaUserPasswd} -e quit 2>&1)"
fi
sqlResultLen="${#sqlResult}"
if [ "${sqlResultLen}" -ne 0 ]; then
echo " * The password is incorrect, the GBase 8a cannot be connected! * "
exit 1
fi
}
#校验命令是否有效
isCmdExist() {
local cmd="$1"
if [ -z "$cmd" ]; then
echo "Usage isCmdExist yourCmd"
return 1
fi
which "$cmd" >/dev/null 2>&1
if [ $? -eq 0 ]; then
return 0
fi
return 2
}
main()
{
config
set_permission
}
main $@2）管理节点配置dblink数据库参数脚本set_gateway_variable.sh
#!/bin/bash
###############################################################################
##creator : Tupac Tu
##create time: 2024-02-01
##Decription: 设置数据节点数据库用户权限
##Method: sh set_gateway_variable.sh
##Version: 1.0
###############################################################################
#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
ScriptsDir=/opt/gbase_workspace/scripts/set_dblink
#获取配置信息(未用)
config()
{
gbaseMode=`cat ${configDir}/config |grep ^GBaseMode |awk -F= '{print $2}' | awk '{print $1}'`
instanceType=`cat ${configDir}/config |grep ^InstanceType |awk -F= '{print $2}' | awk '{print $1}'`
userName=`cat ${configDir}/config |grep ^UserName |awk -F= '{print $2}' | awk '{print $1}'`
userPasswd=`cat ${configDir}/config |grep ^Password |awk -F= '{print $2}' | awk '{print $1}'`
gccli_command="gccli -u${userName} -p${userPasswd}"
}
#获得IP列表
get_iplist()
{
if ! isCmdExist gcadmin; then echo " Failed: execute command (gcadmin) denied to the user"; exit 1; fi
gbaseMode=$(gcadmin showcluster d|grep "virtual cluster" |wc -l)
vcList=$(gcadmin showcluster d|grep "virtual cluster" | awk -F: '{print $2}' |sed 's/,//g')
gcadmin |grep coordinator[1-9] |awk '{print $4}' > ${configDir}/coor.list
if [ ${gbaseMode} -gt 0 ];then
> ${configDir}/data.list
for vc in ${vcList}
do
gcadmin showcluster vc ${vc}|grep node[1-9]|awk '{print $4}' >> ${configDir}/data.list
done
gcadmin showcluster |grep FreeNode[1-9] | awk '{print $4}' >> ${configDir}/data.list
else
gcadmin |grep node[1-9]|awk '{print $4}' > ${configDir}/data.list
fi
}
#设置数据库参数
gen_cluster()
{
cip_first=`gcadmin |grep coordinator1 |awk '{print $4}'`
cip_second=`gcadmin |grep coordinator2 |awk '{print $4}'`
for cip in `cat ${configDir}/coor.list`
do
ssh ${cip} "cp /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf.$(date +%Y%m%d)"
ssh ${cip} "sed -i '/gbase_dblink_gateway_ip/d;/gbasedump/i\gbase_dblink_gateway_ip=${cip_first}' /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf"
ssh ${cip} "sed -i '/gbase_dblink_gateway_port/d;/gbasedump/i\gbase_dblink_gateway_port=9898' /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf"
ssh ${cip} "sed -i '/gbase_dblink_standby_gateway_ip/d;/gbasedump/i\gbase_dblink_standby_gateway_ip=${cip_second}' /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf"
ssh ${cip} "sed -i '/gbase_dblink_standby_gateway_port/d;/gbasedump/i\gbase_dblink_standby_gateway_port=9898' /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf"
#ssh ${cip} "sed -i '/gcluster_dblink_direct_data_exchange/d;/gbasedump/i\gcluster_dblink_direct_data_exchange=0' /opt/${cip}/gcluster/config/gbase_8a_gcluster.cnf"
done
}
#校验命令是否有效
isCmdExist() {
local cmd="$1"
if [ -z "$cmd" ]; then
echo "Usage isCmdExist yourCmd"
return 1
fi
which "$cmd" >/dev/null 2>&1
if [ $? -eq 0 ]; then
return 0
fi
return 2
}
main()
{
config
get_iplist
gen_cluster
}
main $@3）数据源配置文件生成脚本gen_linkconf.sh
#!/bin/bash
###############################################################################
##creator : Tupac Tu
##create time: 2024-02-01
##Decription: 生成目标端数据源配置文件（传参：目标端类型 dblink名称 目标端数据库名称 目标端用户账号 目标用户密码）
##Method: sh gen_linkconf.sh gbase|oracle dblinkName database dbUserName dbUserPasswd
##Version: 1.0
###############################################################################
#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
ScriptsDir=/opt/gbase_workspace/scripts/set_dblink
#参数设置
dblinkType=$1
dblinkName=$2
database=$3
dbUserName=$4
dbUserPasswd=$5
#获取配置信息(未用)
config()
{
gbaseMode=`cat ${configDir}/config |grep ^GBaseMode |awk -F= '{print $2}' | awk '{print $1}'`
instanceType=`cat ${configDir}/config |grep ^InstanceType |awk -F= '{print $2}' | awk '{print $1}'`
userName=`cat ${configDir}/config |grep ^UserName |awk -F= '{print $2}' | awk '{print $1}'`
userPasswd=`cat ${configDir}/config |grep ^Password |awk -F= '{print $2}' | awk '{print $1}'`
gccli_command="gccli -u${userName} -p${userPasswd}"
}
#生成目标端数据源配置文件
gen_dblink()
{
if [ "" == "$dblinkType" ] || [ "" == "$dblinkName" ] || [ "" == "$database" ] || [ "" == "$dbUserName" ] || [ "" == "$dbUserPasswd" ];then
echo "Usage: sh $0 dblinkType(gbase|oracle) dblinkName database dbUserName dbUserPasswd"; exit 1;
fi
loop=1
> "${dblinkName}".properties
while read coorip
do
#echo "$loop: $coorip"
if [ "${dblinkType}" == "gbase" ]; then
cat >> "${dblinkName}".properties <<EOF
[ds${loop}]
dataSource_IP=${coorip}
dataSource_port=5258
dataSource_dbname=${database}
dataSource_dbtype=gcluster
dataSource_user=${dbUserName}
dataSource_pwd=${dbUserPasswd}
dataSource_charset=utf8
EOF
elif [ "${dblinkType}" == "oracle" ]; then
cat >> "${dblinkName}".properties <<EOF
[ds${loop}]
dataSource_dbtype=oracle
dataSource_IP=${coorip}
dataSource_port=1521
dataSource_dbname=${database}
dataSource_user=${dbUserName}
dataSource_pwd=${dbUserPasswd}
EOF
else
echo "None."
fi
loop=`expr $loop + 1`
done < ${ScriptsDir}/source.list
}
#校验命令是否有效
isCmdExist() {
local cmd="$1"
if [ -z "$cmd" ]; then
echo "Usage isCmdExist yourCmd"
return 1
fi
which "$cmd" >/dev/null 2>&1
if [ $? -eq 0 ]; then
return 0
fi
return 2
}
main()
{
config
gen_dblink
}
main $@4）本地端配置文件生成脚本gen_gclusterconf.sh
#!/bin/bash
###############################################################################
##creator : Tupac Tu
##create time: 2024-02-01
##Decription: 生成gcluster配置文件（传参：本环境dblink用户账号 本环境dblink用户密码）
##Method: sh gen_gclusterconf.sh gbUserName gbUserPasswd
##Version: 1.0
###############################################################################
#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
ScriptsDir=/opt/gbase_workspace/scripts/set_dblink
#参数设置
gbUserName=$1
gbUserPasswd=$2
#获取配置信息(未用)
config()
{
gbaseMode=`cat ${configDir}/config |grep ^GBaseMode |awk -F= '{print $2}' | awk '{print $1}'`
instanceType=`cat ${configDir}/config |grep ^InstanceType |awk -F= '{print $2}' | awk '{print $1}'`
userName=`cat ${configDir}/config |grep ^UserName |awk -F= '{print $2}' | awk '{print $1}'`
userPasswd=`cat ${configDir}/config |grep ^Password |awk -F= '{print $2}' | awk '{print $1}'`
gccli_command="gccli -u${userName} -p${userPasswd}"
}
#获得IP列表
get_iplist()
{
if ! isCmdExist gcadmin; then echo " Failed: execute command (gcadmin) denied to the user"; exit 1; fi
gbaseMode=$(gcadmin showcluster d|grep "virtual cluster" |wc -l)
vcList=$(gcadmin showcluster d|grep "virtual cluster" | awk -F: '{print $2}' |sed 's/,//g')
gcadmin |grep coordinator[1-9] |awk '{print $4}' > ${configDir}/coor.list
if [ ${gbaseMode} -gt 0 ];then
> ${configDir}/data.list
for vc in ${vcList}
do
gcadmin showcluster vc ${vc}|grep node[1-9]|awk '{print $4}' >> ${configDir}/data.list
done
gcadmin showcluster |grep FreeNode[1-9] | awk '{print $4}' >> ${configDir}/data.list
else
gcadmin |grep node[1-9]|awk '{print $4}' > ${configDir}/data.list
fi
}
#生成gcluster配置文件
gen_cluster()
{
if [ "" == "$gbUserName" ] || [ "" == "$gbUserPasswd" ];then
echo "Usage: sh $0 gbUserName gbUserPasswd"; exit 1;
fi
check_password
> gbase8a_gcluster.properties
while read coorip
do
cat >> gbase8a_gcluster.properties <<EOF
[gc_${gbUserName}_${coorip}]
gcluster_IP=${coorip}
gcluster_port=5258
gcluster_user=${gbUserName}
gcluster_pwd=${gbUserPasswd}
gcluster_encode=utf-8
EOF
done < ${configDir}/coor.list
while read dataip
do
cat >> gbase8a_gcluster.properties <<EOF
[gn_${gbUserName}_${dataip}]
gcluster_IP=${dataip}
gcluster_port=5050
gcluster_user=${gbUserName}
gcluster_pwd=${gbUserPasswd}
gcluster_encode=utf-8
EOF
done < ${configDir}/data.list
}
#校验密码有效性
check_password()
{
userPasswdLen="${#gbUserPasswd}"
sqlResult=""
sqlResultLen=""
if [ "${userPasswdLen}" -eq 0 ];then
sqlResult="null password"
echo " * You didn't enter the password for the ${gbUserName} user! * "
exit 1
else
sqlResult="$(${gccli_command} -u${gbUserName} -p${gbUserPasswd} -e quit 2>&1)"
fi
sqlResultLen="${#sqlResult}"
if [ "${sqlResultLen}" -ne 0 ]; then
echo " * The password is incorrect, the GBase 8a cannot be connected! * "
exit 1
fi
}
#校验命令是否有效
isCmdExist() {
local cmd="$1"
if [ -z "$cmd" ]; then
echo "Usage isCmdExist yourCmd"
return 1
fi
which "$cmd" >/dev/null 2>&1
if [ $? -eq 0 ]; then
return 0
fi
return 2
}
main()
{
config
get_iplist
gen_cluster
}
main $@部署步骤：
#### 1、客户化信息配置
## 1.1 创建本地库用户 (gbase用户执行)
[gbase@GBASE01 gcinstal]# gccli -ugbase -pXXX
gbase> create user testuser identified by 'pass';
gbase> set default_vc for testuser= vc1;
## 1.2 创建数据库及授权 (gbase用户执行)
[gbase@GBASE01 gcinstal]# gccli -ugbase -pXXX
gbase> use vc vc1;
gbase> create database testdb;
gbase> grant all on testdb.* to testuser; grant file on *.*.* to testuser;
#### 2、DBLINK安装配置
## 2.1 授权dblink数据库用户
[gbase@GBASE01 ~]$ cd /opt/gbase_workspace/scripts/set_dblink
[gbase@GBASE01 set_dblink]$ sh set_permission.sh gbase XXX testuser
## 2.2 管理节点配置dblink数据库参数
[gbase@GBASE01 set_dblink]# gccli -ugbase -pXXX -e "show variables like '%gbase_dblink%'"
[gbase@GBASE01 set_dblink]# sh set_gateway_variable.sh
[gbase@GBASE01 set_dblink]$ cexec all: 'gcluster_services all stop'
[gbase@GBASE01 set_dblink]$ cexec all: 'gcluster_services all start'
[gbase@GBASE01 set_dblink]$ gcadmin; gcadmin showcluster vc vc1
## 2.3 配置目标端数据源信息
[gbase@GBASE01 set_dblink]$ echo -e "214.185.215.16\n214.185.215.18\n214.185.215.20" > source.list #IP为目标端数据库IP
#参数描述 dblinkType: gbase/orcale, dblinkName: gbase_link, database: test, userName: user, userPasswd: pass
[gbase@GBASE01 set_dblink]$ sh gen_linkconf.sh gbase gbase_dbilnk testdb testuser pass
[gbase@GBASE01 set_dblink]$ cat gbase_dbilnk.properties
## 2.4 配置GBASE数据源信息
[gbase@GBASE01 set_dblink]$ sh gen_gclusterconf.sh testuser pass
[gbase@GBASE01 set_dblink]$ cat gbase8a_gcluster.properties
## 2.5 解压并启动gateway服务
[gbase@GBASE01 ~]$ cd /opt
[gbase@GBASE01 opt]$ unzip GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15.zip
[gbase@GBASE01 opt]$ mv GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15.zip gbase_workspace/setup/
[gbase@GBASE01 opt]$ cd GBase8a_MPP_Cluster_Gateway_*/
[gbase@GBASE01 Gateway]$ cp -r /opt/gbase_workspace/scripts/set_dblink/gbase_dbilnk.properties conf/dataSource/
[gbase@GBASE01 Gateway]$ cp -r /opt/gbase_workspace/scripts/set_dblink/gbase8a_gcluster.properties conf/gcluster/
[gbase@GBASE01 Gateway]$ sh gt.sh
[gbase@GBASE01 Gateway]$ ps -ef|grep gateway
## 2.6 创建database link
[gbase@GBASE01 Gateway]$ gccli -ugbase -pXXX
gbase> use vc vc1;
gbase> create database link gbase_dbilnk connect to '' identified by '' using 'gbase_dbilnk';
## 2.7 dblink验证
#源端数据库验证
gccli -utestuser -ppass -e "select count(1) from testdb.cdbc_262"
#本地端查询验证
[gbase@GBASE01 Gateway]$ gccli -utestuser -ppass
gbase> use testdb;
gbase> select count(1) from cdbc_262@gbase_dbilnk;
热门帖子
- 12025-12-01浏览数：182762
- 22023-05-09浏览数：25056
- 42023-09-25浏览数：18525
- 52020-05-11浏览数：17528