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

一、测试环境

二、测试步骤

测试目的端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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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)
1
2
3
4
5

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
2
3
4
5
6
7
8
9
10
11

（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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

（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     |
-----------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[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
1
2
3
4
5
6
7
8
9
10
11
12
13

（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
1
2
3
4
5
6
7
8
9

（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
1
2
3
4
5
6
7
8
9
10
11
12

（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
1
2

我是放在[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!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（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)
1
2
3
4
5
6
7
8

（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)
1
2

（9）目的端测试Dblink

gbase> select * from czg@dblink_gbase
    -> ;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (Elapsed: 00:00:00.07)
1
2
3
4
5
6
7
8

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
2
3
4
5
6
7
8
9
10
11
12
13

三、错误简记

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
1
2

（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.
1
2
3
4
5
6
7
8

（2）解决方法

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

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'
1
2

（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)
1
2
3
4
5
6
7
8
9
10