转载

linux平台oracle连接mysql

【前言】windows平台oracle连接mysql的方法已经给大家介绍过了,现在大部分的ORACLE和MySQL都是在LINUX平台上面,刚好最近也有这种需求,顺手把整个搭建过程记录起来和大家分享。

【原理】通过ODBC连接MySQL的原理图

linux平台oracle连接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;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

本文作者:JOHN,某上市公司DBA,业余时间专注于数据库的技术管理,从管理的角度去运用技术。

技术博客:猎人笔记                                                数据库技术群:367875324 (请备注数据库类型)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

正文到此结束
Loading...