当前位置： 首页 > article >正文

GBase 8a 9.5.3.27 DBlink配置---源端GBase

article 2025/1/30 16:07:01

原理图

在这里插入图片描述

1.目标端集群将数据请求由gcluster的5258端口发送至dblink的9898端口
2.Dblink将请求由9898端口转发至源端集群的5258端口
3.源端数据库将接收的请求生成执行计划，由gcluster的5258端口下发至各gnode的5050端口
4.源端的5050端口接收到执行计划进行查询，并将返回结果与目标端集群的gnode的5050端口直接通讯。
5.目标端Gnode将查询结果汇总到gcluster层。

需求描述

多vc模式，源数据库B的t1数据库下存在t11两张表，现需要从数据库A建立DBlink访问数据库B的表。

数据库A：
10.185.195.10 coor
10.185.195.101 data
10.185.195.102 data
vc：vc1

数据库B：
10.185.195.199 coor
10.185.195.200 data
10.185.195.201 data
vc：vc1

---------------------------------安装---------------------------------------

一、源数据库B操作
### 1.1 建立用户设置默认VC
create user source_user identified by 'source_password';
set default_vc for source_user = vc1;

### 1.2 授予库的权限
grant all on vc1.t1.* to source_user;
#select不授权会报错"ERROR 1105 (HY000): Table 'information_schema.table_distribution' doesn't exist"，后面两个权限貌似可以不用授权
grant select on gbase.* to gbase_user;
#grant all on gctmpdb.* to gbase_user;
#grant all on vc1.gclusterdb.* to gbase_user;



二、数据库A操作
### 2.1 建立用户
create user gbase_user identified by 'gbase_password';

### 2.2 创建数据库与授权
create database t1_new;
grant all on vc1.t1_new.* to gbase_user;

### 2.3 授权[第一个管理节点]
grant select on gbase.* to gbase_user;
grant all on gctmpdb.* to gbase_user;
grant all on vc1.gclusterdb.* to gbase_user;

### 2.4 授权[所有数据节点]
gccli -ugbase -p -h 10.185.195.101  -P5050 -e "grant all on gctmpdb.* to gbase_user"
gccli -ugbase -p -h 10.185.195.102  -P5050 -e "grant all on gctmpdb.* to gbase_user"

### 2.5 检查DBlink配置
# 所有管理节点的"[gbasedump]"上方加DBlink的ip与端口，我的DBlink准备配置在数据库A的管理节点上。配置完需要重启集群哦！
[gbase@GBASEMA01 config]$ pwd
/opt/10.185.195.10/gcluster/config
[gbase@GBASEMA01 config]$ cat gbase_8a_gcluster.cnf 
...
gbase_dblink_gateway_ip = 10.185.195.10
gbase_dblink_gateway_port = 9898
gcluster_dblink_direct_data_exchange = 0
...
[gbasedump]



三、数据库A DBlink配置
### 3.1 数据源配置[这里写的是源数据库B的信息]
[gbase@GBASEMA01 dataSource]$ pwd
/opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.26/conf/dataSource
[gbase@GBASEMA01 dataSource]$ vim gbase_link_t1.properties
[gbase1]
dataSource_IP=10.185.195.199
dataSource_port=5258
dataSource_dbname=t1
dataSource_dbtype=gcluster
dataSource_user=source_user
dataSource_pwd=source_password
dataSource_charset=utf8

### 3.2 数据库配置[这里写的是数据库A的信息]
[gbase@GBASEMA01 gcluster]$ pwd
/opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.26/conf/gcluster
[gbase@GBASEMA01 gcluster]$ cat gbase8a_gcluster.properties
[gc1]
gcluster_IP=10.185.195.10
gcluster_port=5258
gcluster_user=gbase_user
gcluster_pwd=gbase_password
gcluster_encode=utf-8

[gn2]
gcluster_IP=10.185.195.101
gcluster_port=5050
gcluster_user=gbase_user
gcluster_pwd=gbase_password
gcluster_encode=utf-8

[gn2]
gcluster_IP=10.185.195.102
gcluster_port=5050
gcluster_user=gbase_user
gcluster_pwd=gbase_password
gcluster_encode=utf-8

### 3.3 启动DBlink[端口是9898可以检查下]
[gbase@GBASEMA01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.26]$ pwd
/opt/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.26
[gbase@GBASEMA01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.26]$ bash gbaseGatewayServer.sh start

### 3.4 建立DBlink
gbase> use vc vc1;
gbase> create database link gbase_link_t1 connect to '' identified by '' using 'gbase_link_t1';

### 3.5 连接测试
[gbase@GBASEMA01 config]$ gccli -ugbase_user -pgbase_password
use vc vc1;
use t1_new;
select * from t11@gbase_link_t1;

---------------------------------验证---------------------------------------

一、数据库权限验证
### 1.1 源数据库B权限验证
gbase> show grants for source_user;           
+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for source_user@%                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'source_user'@'%' IDENTIFIED BY PASSWORD '*A06F45AD457FDD278DBDFB5061E99288102FA8D3' WITH TASK_PRIORITY 2 |
| GRANT ALL PRIVILEGES ON "vc00001"."t1".* TO 'source_user'@'%'                                                                     |
| GRANT ALL PRIVILEGES ON "vc00001"."t2".* TO 'source_user'@'%'                                                                     |
| GRANT SELECT ON "gbase".* TO 'source_user'@'%'                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------+

### 1.2 数据库B权限验证
# 第一个管理节点
gbase> show grants for gbase_user;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for gbase_user@%                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.*.* TO 'gbase_user'@'%' IDENTIFIED BY PASSWORD '*88F4CD22002C0CB142518B20244FBCCA8A94493A' WITH TASK_PRIORITY 2 |
| GRANT SELECT ON "gbase".* TO 'gbase_user'@'%'                                                                                    |
| GRANT ALL PRIVILEGES ON "gctmpdb".* TO 'gbase_user'@'%'                                                                          |
| GRANT ALL PRIVILEGES ON "vc00001"."gclusterdb".* TO 'gbase_user'@'%'                                                             |
| GRANT ALL PRIVILEGES ON "vc00001"."t1_new".* TO 'gbase_user'@'%'                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (Elapsed: 00:00:00.00)

# 所有数据节点
[gbase@GBASEMA01 logs]$ gccli -ugbase -p -h 10.185.195.101  -P5050 -e "show grants for  gbase_user"
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for gbase_user@%                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gbase_user'@'%' IDENTIFIED BY PASSWORD '*88F4CD22002C0CB142518B20244FBCCA8A94493A' WITH TASK_PRIORITY 2 |
| GRANT ALL PRIVILEGES ON "gctmpdb".* TO 'gbase_user'@'%'                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+
[gbase@GBASEMA01 logs]$ gccli -ugbase -p -h 10.185.195.102  -P5050 -e "show grants for  gbase_user"
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for gbase_user@%                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gbase_user'@'%' IDENTIFIED BY PASSWORD '*88F4CD22002C0CB142518B20244FBCCA8A94493A' WITH TASK_PRIORITY 2 |
| GRANT ALL PRIVILEGES ON "gctmpdb".* TO 'gbase_user'@'%'                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------+



二、数据验证
### 2.1 源数据库B
[gbase@GBASEMA01 ~]$ gccli -usource_user -psource_password -e "use t1;select count(*) from t11;"
+----------+
| count(*) |
+----------+
|        2 |
+----------+

### 2.2 数据库A
[gbase@GBASEMA01 logs]$ gccli -ugbase_user -pgbase_password

GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2025, GBase.  All Rights Reserved.

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

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

gbase> select * from t11@gbase_link_t1;
+------+------+
| id   | name |
+------+------+
|    1 | mmm  |
|    2 | ddd  |
+------+------+
2 rows in set (Elapsed: 00:00:00.07)

三、数据库A：DBlink验证
gbase> select * from gbase.db_links;                                                           
+--------+---------------+-------------+----------+----------+---------------+---------------------+
| owner  | db_link       | dblink_priv | username | password | host          | created             |
+--------+---------------+-------------+----------+----------+---------------+---------------------+
| public | gbase_link_t1 | PUBLIC      |          | NULL     | gbase_link_t1 | 2025-01-24 17:08:29 |
+--------+---------------+-------------+----------+----------+---------------+---------------------+
1 row in set (Elapsed: 00:00:00.08)

http://www.kler.cn/a/523415.html

相关文章：

  • Unity游戏(Assault空对地打击)开发(1) 创建项目和选择插件
  • Kmesh v1.0 正式发布
  • 低代码产品表单渲染架构
  • 一组开源、免费、Metro风格的 WPF UI 控件库
  • 【漫话机器学习系列】066.贪心算法（Greedy Algorithms）
  • 区块链在能源行业的应用场景
  • Xposed-Hook
  • 每日一题 430. 扁平化多级双向链表
  • Nginx 性能优化技巧与实践（二）
  • GORM 支持的数据库解析
  • 力扣 【99. 恢复二叉搜索树】Java题解(二叉树的 Morris 遍历)
  • Vue.js 配合 Vue Router 使用 Vuex
  • 【漫话机器学习系列】065.梯度（Gradient）
  • 数组at()方法：负索引的救赎与JavaScript标准化之路
  • jemalloc 5.3.0的tsd模块的源码分析
  • 关于存储磁盘固件版本：打破版本一致性迷思
  • Python 函数魔法书：基础、范例、避坑、测验与项目实战
  • 大模型概述
  • 第一个3D程序！
  • 在虚拟机里运行frida-server以实现对虚拟机目标软件的监测和修改参数（一）（android Google Api 35高版本版）
  • 借DeepSeek-R1东风，开启创业新机遇
  • nosql mysql的区别
  • SQL server 数据库使用整理
  • 实时数据处理与模型推理：利用 Spring AI 实现对数据的推理与分析
  • 29. 【.NET 8 实战--孢子记账--从单体到微服务】--项目发布
  • 如何保证缓存与数据库的数据一致性？