oracle database link mysql_Oracle通过database link连接到mysql

有1个需要从oracle连到mysql的需求，此采用的database link连过去。以下所有的操作都是oracle机器上完成。

mysql机器保证能连通即可。

机器信息：

两机OS：

CentOS release 6.6 (Final)

Kernel \r on an \m

oracle:11201 64位

mysql：

mysql> select version();

+------------+

| version()  |

+------------+

| 5.5.44-log |

+------------+

1 row in set (0.00 sec)

1.安装相关的软件

[[email protected] Packages]# yum install unixODBC unixODBC-devel

[[email protected] Packages]# yum install mysql-libs mysql-connector-odbc

2.增加以下文件以内容：

[[email protected] ~]# vi /etc/odbc.ini

[test1]

Driver=/usr/lib64/libmyodbc5.so

Server=192.168.12.181

User=root

Password=tan

Port=3306

database=test_box1

CHARSET=UTF8

3.测试odbc连接

[[email protected] ~]$ isql test1 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

出现以上结果，说明ok，可以继续往下。

4.oracle中增加静态监听和tnsnames

[[email protected] admin]$ more listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(SDU=32767)

(ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU=32767)

(GLOBAL_DBNAME = db01)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = orcl)

)

(SID_DESC=

(SID_NAME=test1)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

(ENVS ="LD_LIBRARY_PATH=/usr/lib64/mysql/:/usr/lib:$ORACLE_HOME/lib:$OR

ACLE_HOME/odbc/lib")

)

)

[[email protected] admin]$ more tnsnames.ora

test1  =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))

(CONNECT_DATA=(SID=test1))

(HS=OK)

)

--注意：以上tnsnames.ora中填的host是oracle机器的IP

5.配置HS

[[email protected] admin]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/hs/admin

[[email protected] admin]$ more inittest1.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 =test1

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

HS_NLS_NCHAR=UCS2

#

# ODBC specific environment variables

#

set ODBCINI= /etc/odbc.ini

#

#set =--

6.测试以上的配置:

[[email protected] admin]$ tnsping test1

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JUL-2015 10:38:25

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) (CONNECT_DATA=(SID=test1)) (HS=OK))

OK (30 msec)

7.创建database link

create public database link test1 connect to "root" identified by "tan" using 'test1';

8.进行查询

SQL> select count(*) from "nodes"@test1;

--注意：mysql中的对象需要用引号。