Oracle连接MySQL(odbc)
Oracle连接MySQL的流程(odbc)
1. 软件安装
- odbc rpm安装
rpm -ivh ***.rpm
安装完成之后会在/etc生成odbc.ini和odbcinst.ini
- 安装oracle网关 linux.x64_11gR2_gateways.zip 默认用图形界面安装,安装步骤
DISPLAY=:0.0
export DISPLAY
安装后执行
/u01/app/oracle/product/11g/db_1/root.sh
默认会生成/u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora
2. 更改配置
- 修改/etc/odbc.ini(主要是mysql相关配置)
[Data Sources]
mysql-si = MySQL ODBC 5.3
[mysql-si]
Driver = /usr/lib64/libmyodbc5w.so
Description = Connector/ODBC
Server = 127.0.0.1(安装mysql的服务器ip)
User =wuzehui(mysql连接用户名)
Password = kkkkkk(密码)
Port = 3306(端口)
Database = test(mysql数据库)
OPTION = 3
-
/etc/odbcinst.ini默认不需要改
-
网关配置G /u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora(默认不需要修改)
cp /u01/app/oracle/product/11g/db_1/hs/admin/initdg4odbc.ora /u01/app/oracle/product/11g/db_1/hs/admin/initmyql-si.ora
3. 配置network下的监听
文件:/u01/app/oracle/product/11g/db_1/network/admin/listener.ora
ID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11g/db_1/)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER2=
(SID_LIST=
(SID_DESC=
(SID_NAME= mysql-si)
(ORACLE_HOME= /u01/app/oracle/product/11g/db_1/)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11g/db_1//lib:/u01/app/oracle/product/11g/db_1//odbc/lib)
)
)
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))
)
)
文件:/u01/app/oracle/product/11g/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
##mysql的配置
mysql-si=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1523))
(CONNECT_DATA = (SID = mysql-si))
(HS = OK)
)
4. 配置hs下的监听
文件:/u01/app/oracle/product/11g/db_1/hs/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3306))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=mysql-si)
(ORACLE_HOME=/u01/app/oracle/product/11g/db_1)
(PROGRAM=dg4odbc)
)
)
文件:/u01/app/oracle/product/11g/db_1/hs/admin/tnsnames.ora
mysql-si=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3306))
(CONNECT_DATA=(SID=mysql-si))
(HS=OK)
)
5. 测试连接
isql mysql-si
6. 启动监听
lsnrctl start/stop listener(oracle)
lsnrctl start/stop listener2(mysql)
lsnrctl status
tnsping mysql-si (查看数据监听是否成功)
7. 创建dblink
create public database link SI connect to "wuzehui"(mysql用户名) identified by "kkkkkk"(mysql密码) using 'mysql-si';(这里的设置是/etc/odbc.ini里面的配置)
8. 查询数据
select * from "table_name"@SI;
注:
- table_name和MySQL数据库的用户名、密码都要加双引号,否则可能会出现权限不足的问题
- SI为第七步创建的链接名
配置过程中碰到的问题
- /etc/odbc.ini里面的配置问题
Driver = /usr/lib64/libmyodbc5w.so
这行驱动内容是安装完odbc默认的,所以根本没有检查它是不是真的存在, 然后就掉坑里了。 因为安装软件的过程是其他人做的,排查了好长时间也没发现这个问题,最后抱着试一试的心态在plsql客户端上创建的dblink,并查询了一下,结果报出了/usr/lib64/libmyodbc5w.so不存在的错误,发现目录下有一个类似的文件为/usr/lib64/libmyodbc5.so,然后放在配置文件里面试了一下,问题就解决了。
这个情况可能是软件安装过程的问题,没有进行重装,所以具体原因没有查明。
(完)
- 本文作者:吴泽辉
- 本文链接:https://mutex.top/posts/38a7dcc1/
- 发表日期:2017年12月2日
- 版权声明:本文章为原创,采用《知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议》进行许可