南大通用GBase 8a通过透明网关GateWay,从ipv6集群采用dblink访问ipv4集群的注意事项

GBase 8a的透明网关GateWay支持2套集群之间的SQL互联,采用dblink的语法。随着ipv4逐步迁移到ipv6,会出现某段时间内,两套集群分别是ipv4和ipv6的情况。本文介绍在过度期间,用dblink连接ipv4和ipv6两套集群的注意事项。 从ipv6访问ipv4

ipv4和ipv6是不能直接连通的,所以需要GateWay做中转,该服务必须能同时连接两套集群的IP才可以。

环境

透明网关

GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19

建议用这个或更加新的版本。

ipv4集群

原有集群,尚未配置ipv6地址。

[root@rh6-1 ~]# gcadmin
CLUSTER STATE:  ACTIVE
CLUSTER MODE:   NORMAL

=================================================================
|             GBASE COORDINATOR CLUSTER INFORMATION             |
=================================================================
|   NodeName   |     IpAddress     |gcware |gcluster |DataState |
-----------------------------------------------------------------
| coordinator1 |    10.0.2.201     | OPEN  |  OPEN   |    0     |
-----------------------------------------------------------------
| coordinator2 |    10.0.2.202     | OPEN  |  OPEN   |    0     |
-----------------------------------------------------------------
=============================================================
|              GBASE DATA CLUSTER INFORMATION               |
=============================================================
|NodeName |     IpAddress     |gnode |syncserver |DataState |
-------------------------------------------------------------
|  node1  |    10.0.2.201     | OPEN |   OPEN    |    0     |
-------------------------------------------------------------
|  node2  |    10.0.2.202     | OPEN |   OPEN    |    0     |
-------------------------------------------------------------
[root@rh6-1 ~]#
[root@rh6-1 ~]# ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:30:04:2F
          inet addr:10.0.2.201  Bcast:10.0.2.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe30:42f/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1282485 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2060190 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:389154896 (371.1 MiB)  TX bytes:234663549 (223.7 MiB)

[root@rh6-1 ~]#

ipv6集群

从ipv4更换ipv6的集群,同时存在ipv4和ipv6地址

[gbase@rh6-3 ~]$ gcadmin
CLUSTER STATE:  ACTIVE
CLUSTER MODE:   NORMAL

=========================================================
|         GBASE COORDINATOR CLUSTER INFORMATION         |
=========================================================
|   NodeName   | IpAddress |gcware |gcluster |DataState |
---------------------------------------------------------
| coordinator1 | 2001::203 | OPEN  |  OPEN   |    0     |
---------------------------------------------------------
=====================================================
|          GBASE DATA CLUSTER INFORMATION           |
=====================================================
|NodeName | IpAddress |gnode |syncserver |DataState |
-----------------------------------------------------
|  node1  | 2001::203 | OPEN |   OPEN    |    0     |
-----------------------------------------------------
[gbase@rh6-3 ~]$
[gbase@rh6-3 ~]$ ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:30:D4:0B
          inet addr:10.0.2.203  Bcast:10.0.2.255  Mask:255.255.255.0
          inet6 addr: 2001::203/64 Scope:Global
          inet6 addr: fe80::a00:27ff:fe30:d40b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:168459 errors:0 dropped:0 overruns:0 frame:0
          TX packets:118510 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:161721591 (154.2 MiB)  TX bytes:9698462 (9.2 MiB)

[gbase@rh6-3 ~]$

透明网关配置

必须安装在ipv6集群上,如果安装在ipv4上,则网关只有ipv4地址,是无法连接ipv6地址的。 而部署在ipv6上,其节点也存在ipv4地址。

详细参数说明,请参考透明网关文档。

数据源集群配置

按照手册,配置GateWay/conf/datasource/的数据源配置。每个数据源一个文件。

文件名是dblink用到的,取一个容易区分的名字,比如我这个叫ipv4_201

配置内容里,按照标准数据库连接配置就好了。dataSource_dbtype 支持gcluster和oracle

[gbase@rh6-3 ~]$ cat /opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf/dataSource/ipv4_201.properties
[ipv6201]
dataSource_IP=10.0.2.201
dataSource_port=5258
dataSource_dbname=testdb
dataSource_dbtype=gcluster
dataSource_user=gbase
dataSource_pwd=XXXXXXXX

[gbase@rh6-3 ~]$

目标集群配置

该配置文件就1个。注意,因安全问题,用户名必须和你登录的用户相同,本例是root。

[gbase@rh6-3 ~]$ cat /opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf/gcluster/gbase8a_gcluster.properties
[gc1]
gcluster_IP=2001::203
gcluster_port=5258
gcluster_user=root
gcluster_pwd=root1234
gcluster_encode=utf-8

如果登录用户有多个,IP有多个,要对应配置多个部分。 判断标准是IP和用户名, 95里【可能】增加vc(dataSource_vc=vc1),不过还是建议用有default vc的用户。

[gc1]
gcluster_IP=2001::203
gcluster_port=5258
gcluster_user=root
gcluster_pwd=XXXXXX
gcluster_encode=utf-8

[gc2]
gcluster_IP=2001::203
gcluster_port=5258
gcluster_user=gbase
gcluster_pwd=XXXXX
gcluster_encode=utf-8

[gc3]
gcluster_IP=2001::213
gcluster_port=5258
gcluster_user=root
gcluster_pwd=root1234
gcluster_encode=utf-8

启动网关

切换到 /opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/

cd /opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/
sh gt.sh

通过ps -ef|grep ga查看是否有个java进程判断启动是否成功。

日志在logs目录下。

目标ipv6集群配置

在每个管理节点的配置文件里,增加如下的几个参数,并重启服务。

注意:其中的gcluster_dblink_direct_data_exchange和_t_gcluster_dblink_insert_select_optimization, 只限于两套集群不通的情况。 如果不确定,可以先不设置这2个参数,确认是否dblink可用。这2个参数开启后,性能差,因为要从gateway转发,而不是两套集群间直接通讯。

[gbase@rh6-3 ~]$ grep dblink /opt/gbase/gcluster/config/gbase_8a_gcluster.cnf
gcluster_dblink_direct_data_exchange=0
gbase_dblink_gateway_ip=10.0.2.203
gbase_dblink_gateway_port=9898
_t_gcluster_dblink_insert_select_optimization=0
[gbase@rh6-3 ~]$

检查参数状态

gbase> show variables like '%dblink%';
+--------------------------------------------------+------------+
| Variable_name                                    | Value      |
+--------------------------------------------------+------------+
| _t_gcluster_dblink_clear_syntax_constraints      | 0          |
| _t_gcluster_dblink_generate_interim_table_policy | 1          |
| _t_gcluster_dblink_insert_select_optimization    | 0          |
| gbase_dblink_gateway_ip                          | 10.0.2.203 |
| gbase_dblink_gateway_port                        | 9898       |
| gbase_dblink_server_ip                           |            |
| gbase_dblink_standby_gateway_ip                  |            |
| gbase_dblink_standby_gateway_port                | 0          |
| gcluster_dblink_direct_data_exchange             | 0          |
| gcluster_dblink_optimize                         | 0          |
| gcluster_dblink_orcl_case_sensitive              | 0          |
| gcluster_ipv4_dblink_insert                      | 1          |
+--------------------------------------------------+------------+
12 rows in set (Elapsed: 00:00:00.00)

使用dblink

建立dblink

gbase> create database link ipv4201 connect to '' identified by '' using 'ipv4_201';
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

查询select

gbase> select * from d@ipv4201;
+---------------------+
| d                   |
+---------------------+
| 2023-05-17 17:01:24 |
+---------------------+
1 row in set (Elapsed: 00:00:00.05)

insert select from DBLINK

gbase> truncate table d;
Query OK, 3 rows affected (Elapsed: 00:00:00.33)

gbase> insert into d select * from d@ipv4201;
Query OK, 1 row affected (Elapsed: 00:00:01.17)
Records: 1  Duplicates: 0  Warnings: 0

gbase>

create table as select from dblink

gbase> create table d2 as select * from d@ipv4201;
Query OK, 2 rows affected (Elapsed: 00:00:00.89)

gbase> select * from d2;
+---------------------+
| d                   |
+---------------------+
| 2023-05-24 06:56:39 |
| 2023-05-17 17:01:24 |
+---------------------+
2 rows in set (Elapsed: 00:00:00.01)

支持dblink端表JOIN

gbase> select * from  d@ipv4201 a ,d@ipv4201 b;
+---------------------+---------------------+
| d                   | d                   |
+---------------------+---------------------+
| 2023-05-24 06:56:39 | 2023-05-24 06:56:39 |
| 2023-05-17 17:01:24 | 2023-05-24 06:56:39 |
| 2023-05-24 06:56:39 | 2023-05-17 17:01:24 |
| 2023-05-17 17:01:24 | 2023-05-17 17:01:24 |
+---------------------+---------------------+
4 rows in set (Elapsed: 00:00:00.16)

不支持dblink表和本地表JOIN

gbase> select * from d left join d@ipv4201 b on d.d=b.d;
ERROR 1149 (42000): (GBA-02SC-1001) DBLink table join with (normal table || from sub query) is forbidden
gbase>

查看db_links 元数据

gbase> select * from gbase.db_links;
+--------+---------+-------------+----------+----------+----------+---------------------+
| owner  | db_link | dblink_priv | username | password | host     | created             |
+--------+---------+-------------+----------+----------+----------+---------------------+
| public | ipv4201 | PUBLIC      |          | NULL     | ipv4_201 | 2023-05-17 17:00:12 |
+--------+---------+-------------+----------+----------+----------+---------------------+
1 row in set (Elapsed: 00:00:00.00)

删除dblink

gbase> drop database link ipv4201;
Query OK, 0 rows affected (Elapsed: 00:00:00.09)

向dblink数据源端发送SQL

目前只支持一些DDL,DML,不支持SELECT,CALL等。暂不支持大对象。

  • insert values
  • insert select
  • delete
  • update
  • truncate
  • merge
  • create
  • drop
gbase> passthrough link ipv4201 using 'insert into d values( now())';
Query OK, 1 row affected (Elapsed: 00:00:00.26)

gbase> passthrough link ipv4201 using 'select * from d';
ERROR 1105 (HY000): SQL command is not supported: select * from d
gbase>

直接insert到dblink的数据源端

我测试的版本尚不支持

gbase> insert into d@ipv4201 values (now());
ERROR 1235 (42000): This version of GBase doesn't yet support 'insert into remote table'
gbase>

注意事项

由于ipv6和ipv4本身并不连通,所以必须要有个转发的地方,也就是同时配置ipv4和ipv6,并能和两面的IP相同的机器做网关。

数据源,一般配置没啥问题,注意下配置文件名字就行了。

目标源这面有些麻烦,老版本网关,每个源也是一个文件,新版本放到了一起,还是有些容易混淆的。

  • ipv6的地址,就用ipv6的,不要写ipv4的,否则IP+用户名,找不到
  • ipv6地址,强烈建议集群用完整/统一格式。2001::200和2001::0200从字符串比对看,是不等的。
  • 用户名,要和登录用户一样。 一般业务系统极少用root或者gbase这2个管理员账号,而是业务账号。
  • 端口写5258就行,除非集群没有使用默认端口。