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就行,除非集群没有使用默认端口。