南大通用数据库-Gbase-8a-学习-15-Gbase8a通过Dblink访问Gbase8a(95-＞86)

一、测试环境

名称	值
cpu	Intel® Core™ i5-1035G1 CPU @ 1.00GHz
操作系统	CentOS Linux release 7.9.2009 (Core)
内存	4G
逻辑核数	3
Gbase-8a源端节点-IP	192.168.142.11
Gbase-8a目的端节点-IP	192.168.142.10
Gbase-8a源端数据库版本	9.5.3.27.6e43a8ca
Gbase-8a目的端数据库版本	8.6.2.43-R33.132743

二、测试步骤

测试目的端Gbase-8a-86版本通过DBLINK访问源端Gbase-8a-95版本数据。

1、Gbase-8a源端创建用户

[gbase@xdw1 ~]$ gccli

GBase client 9.5.3.27.6e43a8ca. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> create database czg;
Query OK, 1 row affected (Elapsed: 00:00:00.01)

gbase> use czg;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create user czg@'%' identified by 'qwer1234';
Query OK, 0 rows affected (Elapsed: 00:00:00.11)

gbase> grant all privileges on *.* to 'czg'@'%';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

2、Gbase-8a源端测试数据

gbase> create table czg(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)

gbase> insert into czg values(1);
Query OK, 1 row affected (Elapsed: 00:00:00.01)

3、配置透明网关

[gbase@xdw0 pkg]$ tar -xvf GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15.tar

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ ll
总用量 12
drwxrwxrwx 5 gbase gbase  101 8月  15 15:53 conf
drwxrwxrwx 2 gbase gbase   30 11月 30 2020 gateway
-rwxrwxrwx 1 gbase gbase 1896 11月  7 2019 gbaseGatewayServer.sh
-rwxrwxrwx 1 gbase gbase  654 7月  16 2020 gt.sh
drwxrwxrwx 5 gbase gbase  185 7月  16 2020 jre
drwxrwxrwx 4 gbase gbase 4096 7月  16 2020 lib
drwxrwxrwx 2 gbase gbase   30 8月  15 15:31 logs

（1）conf.properties

这个文件不用改动，但大家需要记录一下端口9898。

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# cat conf/conf.properties 
gbase.gt.port=9898
gbase.gt.encode=utf8
gbase.gt.pagesize=1000
#load data type : batch=1, insert values=0
gbase.gt.load.data.type=1
gbase.gt.table.use.decimal=1
#paging query : not=0, yes=1;default=0
gbase.gt.gc.paging.query=0
gbase.gt.st.paging.query=0
gbase.gt.orcl.paging.query=0
#commit type : transaction commit=0, paging commit=1
gbase.gt.commit.type=0
#timeout
gbase.gt.wait.timeout=7200
#timeout to fetch gcluster datasource(second) : default=108000
gbase.gt.gc.fetch.timeout=108000
#thread pool type: 0-fixed thread pool 1-thread pool executor
gbase.gt.thread.pool.type=0
#thread pool size(gbase.gt.thread.pool.type=0 effect)
gbase.gt.thread.pool.size=5000
#core pool size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.core.pool.size=200
#maximum pool size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.maximum.pool.size=5000
#keep alive time(gbase.gt.thread.pool.type=1 effect)
gbase.gt.keep.alive.time=0
#queue size(gbase.gt.thread.pool.type=1 effect)
gbase.gt.queue.size=10

（2）gbase8a_gcluster.properties

我这边的集群是一个管理节点，两个数据节点，我们都要写到这个配置文件里。

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# gcadmin
CLUSTER STATE:  ACTIVE
CLUSTER MODE:   NORMAL

=====================================================================
|               GBASE COORDINATOR CLUSTER INFORMATION               |
=====================================================================
|   NodeName   |       IpAddress       |gcware |gcluster |DataState |
---------------------------------------------------------------------
| coordinator1 |    192.168.142.10     | OPEN  |  OPEN   |    0     |
---------------------------------------------------------------------
=================================================================
|                GBASE DATA CLUSTER INFORMATION                 |
=================================================================
|NodeName |       IpAddress       |gnode |syncserver |DataState |
-----------------------------------------------------------------
|  node1  |    192.168.142.10     | OPEN |   OPEN    |    0     |
-----------------------------------------------------------------

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# cat conf/gcluster/gbase8a_gcluster.properties 
[gc1]
gcluster_IP=192.168.142.10
gcluster_port=5258
gcluster_user=root
gcluster_pwd=qwer1234
gcluster_encode=utf-8
[gn1]
gcluster_IP=192.168.142.10
gcluster_port=5050
gcluster_user=root
gcluster_pwd=qwer1234
gcluster_encode=utf-8

（3）gbase_link1.properties

gbase_link1.properties需要和sample文件同一层。

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# cat conf/dataSource/gbase_link1.properties 
[ds1]
dataSource_IP=192.168.142.11
dataSource_port=5258
dataSource_dbname=czg
dataSource_dbtype=gcluster
dataSource_user=czg
dataSource_pwd=qwer1234
dataSource_charset=utf8

（4）启动程序

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# chown -R gbase:gbase /opt/pkg/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.1

[root@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]# su - gbase
上一次登录：一 8月 15 16:00:06 CST 2022pts/0 上

[gbase@xdw0 ~]$ cd /opt/pkg/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15/

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ sh gt.sh 

[gbase@xdw0 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.15]$ ps -ef|grep java
gbase     24603      1  5 16:55 pts/0    00:00:00 java -Dfile.encoding=UTF-8 -Dcom.sun.management.jmxremote -Xmx2048m -cp gateway/gbaseGateway.jar:lib/commons-beanutils-1.7.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.4.jar:lib/commons-logging-1.1.jar:lib/commons-net-1.4.1.jar:lib/dom4j-1.6.1.jar:lib/ezmorph-1.0.6.jar:lib/gbase-connector-java-8.3.81.53-build55.5.5-bin.jar:lib/jaxen-1.1-beta-6.jar:lib/json-lib-2.4-jdk15.jar:lib/jtds-1.2.5.jar:lib/log4j-1.2.15.jar:lib/ojdbc8.jar:lib/commons-configuration-1.8.jar:lib/oscarJDBC.jar:lib/oscarClusterJDBC.jar cn.com.gbase.gbaseGateway.server.GBaseGateway
gbase     24624  24537  0 16:56 pts/0    00:00:00 grep --color=auto java

（5）修改目的端gcluster层参数

每个管理节点都需要添加此参数。
我的dblink程序是在192.168.142.10节点启动，所以填写此IP。

添加这两个参数到/opt/gcluster/config/gbase_8a_gcluster.cnf中

gbase_dblink_gateway_ip = '192.168.142.10'
gbase_dblink_gateway_port = 9898

我是放在[gbased]下

（6）目的端重启服务

我这边是重启了所有节点的服务，但应该是只重启所有管理节点的服务。

[root@xdw0 dataSource]# service gcware restart
Stopping GCMonit success!
Signaling GCRECOVER (gcrecover) to terminate:              [  确定  ]
Waiting for gcrecover services to unload:...               [  确定  ]
Signaling GCSYNC (gc_sync_server) to terminate:            [  确定  ]
Waiting for gc_sync_server services to unload:             [  确定  ]
Signaling GCLUSTERD  to terminate:                         [  确定  ]
Waiting for gclusterd services to unload:.....             [  确定  ]
Signaling GBASED  to terminate:                            [  确定  ]
Waiting for gbased services to unload:...                  [  确定  ]
Signaling GCWARE (gcware) to terminate:                    [  确定  ]
Waiting for gcware services to unload:..                   [  确定  ]
Starting GCWARE (gcwexec):                                 [  确定  ]
Starting GBASED    :                                       [  确定  ]
Starting GCSYNC :                                          [  确定  ]
Starting GCLUSTERD :                                       [  确定  ]
Starting GCRECOVER :                                       [  确定  ]
Starting GCMonit success!

（7）目的端gbase创建用户

gbase> create user czg identified by 'qwer1234';
Query OK, 0 rows affected (Elapsed: 00:00:00.11)

gbase> grant all privileges on *.* to 'czg'@'%';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> flush privileges;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

（8）目的端创建Dblink

gbase_link1这个名称和gbase_link1.properties的前缀要一样。

gbase> CREATE DATABASE LINK dblink_gbase connect to '' identified by '' using 'gbase_link1';
Query OK, 0 rows affected (Elapsed: 00:00:00.30)

（9）目的端测试Dblink

gbase> select * from czg@dblink_gbase
    -> ;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.07)

4、DBLINK数据字典表

gbase> desc gbase.db_links;         
+-------------+--------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                     | Null | Key | Default           | Extra                       |
+-------------+--------------------------+------+-----+-------------------+-----------------------------+
| owner       | varchar(128)             | NO   | PRI | NULL              |                             |
| db_link     | varchar(128)             | NO   | PRI | NULL              |                             |
| dblink_priv | enum('PUBLIC','PRIVATE') | NO   | PRI | NULL              |                             |
| username    | varchar(128)             | YES  |     | NULL              |                             |
| password    | varchar(41)              | YES  |     | NULL              |                             |
| host        | varchar(2000)            | YES  |     | NULL              |                             |
| created     | timestamp                | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+--------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (Elapsed: 00:00:00.00)

三、错误简记

1、ERROR 1105 (HY000): connect gateway server timeout. Connection refused

（1）错误信息

gbase> select * from czg@dblink_gbase;
ERROR 1105 (HY000): connect gateway server timeout. Connection refused

（2）解决方法

启动gt.sh。

2、ERROR 1105 (HY000): errorCode: 1818, errorMsg: No VC selected.

（1）错误信息

gbase> use vc vc1;                    
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> use czg;                       
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> select * from czg@dblink_gbase;
ERROR 1105 (HY000): errorCode: 1818, errorMsg: No VC selected.

（2）解决方法

gbase> set default_vc for czg = vc2; 
Query OK, 0 rows affected (Elapsed: 00:00:00.13)

3、ERROR 1105 (HY000): SELECT command denied to user ‘czg’@‘192.168.142.10’ for table ‘czg’

（1）错误信息

gbase> select * from czg@dblink_gbase;      
ERROR 1105 (HY000): SELECT command denied to user 'czg'@'192.168.142.10' for table 'czg'

（2）解决方法

gbase> grant all on *.*.* to czg@'%';  
Query OK, 0 rows affected (Elapsed: 00:00:00.04)

gbase> select * from czg@dblink_gbase; 
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.09)