Linux平台Oracle连接MySQL
【前言】Windows平台Oracle连接MySQL的方法已经给大家介绍过了，现在大部分的Oracle和MySQL都是在Linux平台上面，刚好最近也有这种需求，顺手把整个搭建过程记录起来和大家分享。
【原理】通过ODBC连接MySQL的原理图
【说明】Driver Manager: 负责管理应用程序和驱动程序间的通信, 主要功能包括: 解析DSN (数据源名称,ODBC的数据源名称在ODBC.INI文件中配置), 加载和卸载驱动程序,处理ODBC调用,将其传递给驱动程序.
Connector/ODBC(MyODBC驱动程序):实现ODBC API所提供的功能, 它负责处理ODBC函数调用,将SQL请求提交给MySQL服务器，并将结果返回给应用程序.
ODBC.INI是ODBC配置文件，记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager将使用它来确定加载哪个驱动程序（使用数据源名DSN）。驱动程序将根据指定的DSN来读取连接参数。
【配置思路】
在Linux下配置mysql ODBC 需要有以下步骤:
a) 安装Driver Manager , 本案例使用unixODBC 来作为Driver Manager
b) 安装MySQL驱动程序, 本案例使用Connector/ODBC
c) 配置ODBC.INI
【环境说明】
操作系统：CentOS release 6.5 (Final)
目标数据库：ORACLE 11.2.0.3
源数据库：mysql5.23
【操作步骤】
一、ODBC的配置
1.1、在目标数据库上面安装unixODBC,脚本：yum install unixODBC
1.2、安装mysql-connector-odbc,下载链接：http://pan.baidu.com/s/1bnuiWCR 密码：rf98
进行解压后修改文件名放在/usr/local/
[root@test local]# pwd
/usr/local
[root@OTO-DBR-T02 local]# ll
total 56
drwxr-xr-x. 5 root root 4096 Oct 22 11:15 mysql-connector-odbc
1.3、配置odbc.ini文件
[myodbc] #需要记住该名称
Driver = /usr/local/mysql-connector-odbc/lib/libmyodbc5.so
Description = Connector/ODBC 5.1 Driver DSN
SERVER = 192.168.199.244
PORT = 3306
USER = root
PASSWORD = root
DATABASE = test
OPTION = 0
TRACE = OFF
1.4、测试连接
[root@test local]# isql myodbc -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
祝贺，弹出该窗口说明可以连接到mysql了。
二、监听的配置
2.1 进入ORACLE_HOME/hs/admin
[oracle@test admin]$ vi initmyodbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = myodbc
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI= /etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#set =
2.2 配置listener.ora 文件
进入$ORACLE_HOME/network/admin
[oracle@test admin]$ vi listener.ora
# listener.ora Network Configuration File: /oracle/oracle11/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /oracle/oracle11 ) #ORACLE_HOME
(PRESPAWN_MAX = 20)
)
(SID_DESC =
(SID_NAME = myodbc) #与上面的文件名对应
(ORACLE_HOME = /oracle/oracle11 ) #ORACLE_HOME
(PROGRAM = dg4odbc) #11g为dg4odbc，10g不一样
)
)
~
重启监听后看下myodbc的服务是否已经启动
[oracle@test oracle11]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-OCT-2015 15:22:12
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 22-OCT-2015 15:08:57
Uptime 0 days 0 hr. 13 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/oracle11/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "myodbc" has 1 instance(s).
Instance "myodbc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
2.3 配置 tnsnames.ora 文件
myodbc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myodbc) #与上面的监听相对应
)
(HS = OK)
)
2.4 创建dblink
create database link myodbc connect to "root" identified by "root" using 'myodbc' ;
2.5 检查dblink
SQL> select * from dual@myodbc;
D
-
X
说明已经完成在linux平台的ORACLE连接MySQL的配置；
另：配置完2.2步骤之后可能数据库本身的监听服务就不见了，这时候可以执行，让系统进行重新注册
alter system set local_listener='(address=(protocol=tcp)(port=1521)(host= hostname))' scope=both;