oracle dblink 数据库对接mysql

在项目建设过程中，会遇到数据库之间连接的案例，数据库连接常见的有
1.数据直连 dblink
2.数据之间通过接口实现数据连接，需要其中一个系统提供一个通用接口
本次 我们以数据库之间异构数据连接为例
oracle去连接mysql数据库
oracle通过dblink的方式去访问mysql数据库
oracle——>dg4odbc——>odbc——>mysql
1.查看DG4ODBC版本
[root@erpback upload]# su - oratest
[oratest@erpback ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/erpback/db/tech_st/11.2.0/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
2、安装unixODBC[root@erpback upload]# yum install unixODBC
已加载插件：refresh-packagekit, security, ulninfo
设置安装进程
包 unixODBC-2.2.14-14.el6.x86_64 已安装并且是最新版本
包 unixODBC-devel-2.2.14-14.el6.x86_64 已安装并且是最新版本
包 unixODBC-kde-2.2.14-14.el6.x86_64 已安装并且是最新版本
无须任何处理
3、安装成功后测试[root@erpback upload]# odbcinst -j
unixODBC 2.2.14
DRIVERS…: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES…: /etc/ODBCDataSources
USER DATA SOURCES…: /root/.odbc.ini
SQLULEN Size…: 8
SQLLEN Size…: 8
SQLSETPOSIROW Size.: 8
4、mysql odbc驱动安装[root@erpback upload]# yum install -y mysql-connector-odbc-5.1.5r1144
已加载插件：refresh-packagekit, security, ulninfo
设置安装进程
包 mysql-connector-odbc-5.1.5r1144-7.el6.x86_64 已安装并且是最新版本
无须任何处理
[root@erpback upload]#
安装驱动时候切忌不好偷懒
要实际确认oracle数据库版本
mysql数据库版本
去官网上下载驱动
链接下载地址
5.配置 /etc/odbc.ini
[root@erpback ~]# cat /etc/odbc.ini
[mysql]
#Description = mysql
Description = MySQL ODBC 8.0 Unicode Driver
#Driver = MySQL ODBC 8.0 Unicode Driver
#Driver =/usr/lib64/libmyodbc5.so
Driver =/usr/lib64/libmyodbc8a.so
Server = 你的ip地址
Database = 数据库名称
Port = 端口号
USER = 用户名
Password =密码
#Socket =
Option = 3
#Stmt =
CHARSET = UTF8
6.测试连接是否成功 isql mysql -v
[root@erpback ~]# isql mysql -v
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL> show databases;
±----------------------------------------------------------------+
| Database |
±----------------------------------------------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched
7.配置oracle监听 追加内容：
静态监听下加这么一段
SID_LIST_TEST =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/erpback/db/tech_st/11.2.0)
(SID_NAME = test)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(ORACLE_HOME = /u01/erpback/db/tech_st/11.2.0)
(SID_NAME = mysql)
(ENVS = “LD_LIBRARY_PATH = /u01/erpback/db/tech_st/11.2.0/lib:/usr/lib64”)
)
)
8.配置tns文件
mysql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=域名地址)(PORT=1531))
(CONNECT_DATA=(SID=mysql))
(HS=OK)
)
9.配置odbc监听
cd $ORACL{E}_{H}OME/hs/admin[oratest@erpbackadmin]$ cat initmysql.ora
HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = off
#HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc5.so
#HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so
HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc8a.so
#HS_FDS_SUPPORT_STATISTICS=FALSE
#HS_FDS_SQLLEN_INTERPRETATION=32
#HS_LONG_PIECE_TRANSFER_SIZE=1258291
set DBCINI=/etc/odbc.ini
#HS_IDLE_TIMEOUT =1440
HS_NLS_NCHAR=UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#HS_LANGUAGE=AMERICAN_AMERICA.UTF8**
执行完毕后
依次执行
lnsrctl stop
lsnrctl start
alter system register;
之后执行tnsping 查看是否成功
tnsping mysql
[oratest@erpback admin]$ tnsping mysql
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 08-JUN-2023 14:26:51
Copyright © 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/erpback/db/tech_st/11.2.0/network/admin/test_erpback/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=XXXXXXXXXX.com)(PORT=1531)) (CONNECT_DATA=(SID=mysql)) (HS=OK))
OK (0 msec)
10.创建dblink并测试
SQL> create database link TEST_MYSQL
connect to “root” identified by “root”
using ‘mysql’;
##测试dblink 查询mysql数据
SQL> select 1 from dual@TEST_MYSQL;
no rows selected**