最近做了一次Oracle连接到MySQL的实验,主要是通过DG4ODBC来连接的.以下是实验环境和实验步骤.
Oracle服务器:
IP:192.168.0.193
Database Version:11.2.0.1
MySQL服务器
IP:192.168.0.100
MySQL Version:5.5.18
1.首先要检查Oracle和DG4ODBC是32位还是64位.
[oracle@11g ~]$ file $ORACLE_HOME/bin/dg4odbc /oracle/app/oracle/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
这里可以看到是64位的.所以必须使用64位的ODBC Driver Manager和64位的ODBC Driver.
2.下载并安装64位的ODBC Driver Manager UnixODBC 2.2.14.
下载地址:http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
#这里下载到了/home/oracle目录下面 [oracle@11g ~]$ ls -l unixODBC* -rw-r--r-- 1 oracle dba 756418 Dec 13 18:17 unixODBC-2.2.14-linux-x86-64.tar.gz [oracle@11g ~]$ mkdir -p ~/app/unixodbc-2.2.14 [oracle@11g ~]$ cd ~/app/unixodbc-2.2.14/ [oracle@11g unixodbc-2.2.14]$ gunzip -c ~/unixODBC-2.2.14-linux-x86-64.tar.gz | tar xvf - #因为UnixODBC的包lib和bin是在/usr/local文件夹里面.为了方便其见,我们把他们移动到上层目录上来. [oracle@11g unixodbc-2.2.14]$ mv ~/app/unixodbc-2.2.14/usr/local/* . [oracle@11g unixodbc-2.2.14]$ rm -rf usr/ #检查一下目录的内容 [oracle@11g unixodbc-2.2.14]$ ls -l total 12 drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 bin drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 include drwxr-xr-x 2 oracle oinstall 4096 Nov 20 2008 lib
3.下载并安装ODBC Driver
下载地址:http:http://ftp.ntu.edu.tw/pub/MySQL/Downloads/Connector-ODBC/5.1/
#这里下载到了/home/oracle目录下面 [oracle@11g ~]$ ls -l mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz -rw-r--r-- 1 oracle oinstall 5725402 Dec 14 22:40 mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz [oracle@11g ~]$ cd app/ [oracle@11g app]$ gunzip -c ~/mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz | tar xvf - [root@11g app]# ln -s mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit myodbc-5.18
4.配置MYSQL Connector(ODBC driver)
首先在mysql服务器上新建一个用户,并建相关的表
mysql> grant all privileges on test.* to mysql_user@localhost identified by "mysql_user"; Query OK, 0 rows affected (0.00 sec) mysql> use test; Database changed mysql> create table t (id int); Query OK, 0 rows affected (0.09 sec) mysql> insert into t values (20); Query OK, 1 row affected (0.04 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
然后在Oracle所在的服务器上面的/oracle/home/app下面建立odbc.ini文件,添加加以下内容
[myodbc5] Driver = /home/dbs/app/myodbc-5.18/lib/libmyodbc5.so Description = Connector/ODBC 5.1 Driver DSN SERVER = 192.168.0.100 PORT = 3306 USER = mysql_user PASSWORD = mysql_user DATABASE = test OPTION = 0 TRACE = OFF
5.使用isql来进行验证
[oracle@11g ~]$ export ODBCINI=/home/oracle/app/odbc.ini [oracle@11g ~]$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH [oracle@11g bin]$ ./isql myodbc5 -v [S1000][unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on '192.168.0.100' (113) [ISQL]ERROR: Could not SQLConnect
这里报错.这是因为mysql新建的mysql_user用户不允许其他ip访问.切换到mysql服务器上面进行检查.
[root@buddy ~]# mysql --user=mysql_user --password=mysql_user --host=192.168.0.100 --port=3306 ERROR 1130 (HY000): Host '192.168.0.100' is not allowed to connect to this MySQL server
进入数据库查询可以清楚的看到只有localhost才能访问mysql_user;
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user from user; +-----------+------------+ | host | user | +-----------+------------+ | 127.0.0.1 | root | | ::1 | root | | buddy | | | buddy | root | | localhost | | | localhost | mysql_user | | localhost | root | +-----------+------------+ 7 rows in set (0.00 sec) #修改权限. mysql> grant all privileges on test.* to 'mysql_user'@'%' identified by 'mysql_user'; Query OK, 0 rows affected (0.00 sec) mysql> select host,user from user; +-----------+------------+ | host | user | +-----------+------------+ | % | mysql_user | | 127.0.0.1 | root | | ::1 | root | | buddy | | | buddy | root | | localhost | | | localhost | mysql_user | | localhost | root | +-----------+------------+ 8 rows in set (0.00 sec)
如果还是不行,就需要把mysql服务器的防火墙关闭.
[root@buddy init.d]# /etc/init.d/iptables stop
然后切换到oracle服务器上执行isql命令测试
[oracle@11g bin]$ ./isql myodbc5 -v +----------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +----------------------+ SQL> show tables; +----------------------+ | Tables_in_test | +----------------------+ | t | +----------------------+ SQLRowCount returns 1 1 rows fetched
6.配置tnsnames.ora文件,增加下列内容.
myodbc5= (DESCRIPTION= (ADDRESS=(PROTOCOL = TCP) (HOST = 192.168.0.193) (PORT = 1521)) (CONNECT_DATA = (SID = myodbc5) ) (HS=OK) )
7.配置listenner.ora文件,增加下列内容.
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=myodbc5) (ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14) ) )
8.配置静态监听,初始化参数文件.initmyodbc5.ora
[oracle@11g ~]$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora #加入下列内容 [注意]:HS_FDS_CONNECT_INFO是Data Source的名字.这个名字是odbc.ini文件里面定义的. HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini # HS_FDS_TRACE_LEVEL=user HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/lib/libodbc.so HS_FDS_SUPPORT_STATISTICS=FALSE HS_LANGUAGE=AMERICAN.AMERICA.ZHS16GBK # ODBC env variables set ODBCINI=/home/oracle/app/odbc.ini
9.重启监听,让刚才修改的文件生效.
[oracle@11g ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:34 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521))) The command completed successfully [oracle@11g ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 19:50:45 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.193)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 15-DEC-2011 19:50:45 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.193)(PORT=1521))) Services Summary... Service "myodbc5" has 1 instance(s). Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
重启监听之后,可以看到myodbc5已经注册上了,但是状态是UNKNOWN,这种情况是正常的.因为我们还没有尝试使用该服务,最后尝试去TNSPING一下.看看能不能ping通.
[oracle@11g admin]$ tnsping myodbc5 TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2011 20:00:10 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 = 192.168.0.193) (PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK)) OK (0 msec)
10.创建Database Link.
[oracle@11g admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 15 20:02:16 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create public database link myodbc5 connect to "mysql_user" identified by "mysql_user" using 'myodbc5'; Database link created. SQL> select * from "t"@myodbc5; id ---------- 20
[注意]:当通过Link查询mysql的表,推荐在命名使用双引号,除非MySQL Server上设置了ANSI_QUOTES.
至此.Oracle已经能够正常通过DG4ODBC连接到MySQL了.
http://www.dbrabbit.com/archivers/oracle连接mysql-使用dg4odbc.html