oracle读取mysql数据库内容，一般采用odbc作为跳板，流程图如下
1，oracle数据库首先通过gateway连接odbc的驱动管理器，
2，odbc驱动程序管理器再调用相关的odbc驱动，比如mysql数据库驱动程序，PostgreSQL数据驱动等；
3，odbc驱动程序比如mysql，通过mysql数据库的客户端即上面所说的non-oracle system client，读取mysql数据库服务端的数据
4，数据返回路径上述路径相反，整个交互过程就是这样的啦
下面根据流程图来配置，
服务器和程序参数
服务器：Linux v0A-123-177-205 2.6.18-194.el5xen #1 SMP Tue Mar 16 22:01:26 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
数据库1：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
数据库2：MySQL-5.5.25，Win32，x86，MySQL Community Server (GPL)，
一，首先配置odbc的驱动程序管理器和驱动程序，这里我配置的mysql，用于连接mysql数据库，
1，检查是否按了odbc的驱动程序管理器
[root@v0A-123-177-205 bin]# rpm -qa | grep unixODBC unixODBC-devel-2.2.11-10.el5 unixODBC-2.2.11-10.el5 unixODBC-libs-2.2.11-10.el5 unixODBC-devel-2.2.11-10.el5 unixODBC-libs-2.2.11-10.el5 unixODBC-2.2.11-10.el5 [root@v0A-123-177-205 bin]# rpm -ql unixODBC-2.2.11-10.el5 /usr/bin/dltest /usr/bin/isql /usr/bin/iusql /usr/bin/odbc_config /usr/bin/odbcinst /usr/share/doc/unixODBC-2.2.11 /usr/share/doc/unixODBC-2.2.11/AUTHORS /usr/share/doc/unixODBC-2.2.11/ChangeLog /usr/share/doc/unixODBC-2.2.11/INSTALL /usr/share/doc/unixODBC-2.2.11/NEWS /usr/share/doc/unixODBC-2.2.11/doc ................. [root@v0A-123-177-205 ~]# file /usr/bin/odbcinst /usr/bin/odbcinst: 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, stripped [root@v0A-123-177-205 ~]# odbcinst ********************************************** * unixODBC - odbcinst * ********************************************** * * * Purpose: * * * * An ODBC Installer and Uninstaller. * * Updates system files, and * * increases/decreases usage counts but * * does not actually copy or remove any * * files. * * * * Syntax: * * * * odbcinst Action Object Options * * * * Action: * * * * -i install * * -u uninstall * * -q query * * -j print config info * * --version version * * * * Objects: * * * * -d driver * * -s data source * * * * Options: * * ...............
安装好了，如果没有的话，自己安装，yum方式，或者下载源代码安装，地址：http://www.unixodbc.org/
2，查看odbc驱动程序管理器的配置信息
[root@v0A-123-177-205 bin]# odbcinst -j unixODBC 2.2.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /root/.odbc.ini
oracle用户的odbc配置信息可能不一样，也看看
[oracle@v0A-123-177-205 ~]$ odbcinst -j unixODBC 2.2.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /etc/odbc.ini [oracle@v0A-123-177-205 ~]$
3，检查是否安装mysql驱动程序，以及驱动程序路径
[root@v0A-123-177-205 bin]# rpm -qa | grep odbc mysql-connector-odbc-3.51.26r1127-2.el5 mysql-connector-odbc-5.1.10-1.rhel5 [root@v0A-123-177-205 bin]# rpm -ql mysql-connector-odbc-3.51.26r1127-2.el5 /usr/lib64/libmyodbc3-3.51.26.so /usr/lib64/libmyodbc3.so /usr/lib64/libmyodbc3_r-3.51.26.so /usr/lib64/libmyodbc3_r.so /usr/share/doc/mysql-connector-odbc-3.51.26r1127 /usr/share/doc/mysql-connector-odbc-3.51.26r1127/ChangeLog /usr/share/doc/mysql-connector-odbc-3.51.26r1127/LICENSE.exceptions /usr/share/doc/mysql-connector-odbc-3.51.26r1127/LICENSE.gpl /usr/share/doc/mysql-connector-odbc-3.51.26r1127/README /usr/share/doc/mysql-connector-odbc-3.51.26r1127/README.debug [root@v0A-123-177-205 bin]#
已经安装64位的mysql驱动程序mysql-connector-odbc-3.51.26r1127-2.el5，驱动路径是：/usr/lib64/libmyodbc3_r.so，没有的话自己安装，yum方式，或者下载源代码安装，地址：http://dev.mysql.com/downloads/connector/odbc/
另外一个程序mysql-connector-odbc-5.1.10-1.rhel5，也是mysql的驱动程序，而且具备驱动程序管理器的作用，不过是32位的，详细信息如下：
[root@v0A-123-177-205 ~]# rpm -ql mysql-connector-odbc-5.1.10-1.rhel5 /usr/bin/myodbc-installer /usr/lib/libmyodbc5.so /usr/share/doc/mysql-connector-odbc-5.1.10 /usr/share/doc/mysql-connector-odbc-5.1.10/COPYING /usr/share/doc/mysql-connector-odbc-5.1.10/ChangeLog /usr/share/doc/mysql-connector-odbc-5.1.10/INSTALL /usr/share/doc/mysql-connector-odbc-5.1.10/INSTALL.win /usr/share/doc/mysql-connector-odbc-5.1.10/Licenses_for_Third-Party_Components.txt /usr/share/doc/mysql-connector-odbc-5.1.10/README /usr/share/doc/mysql-connector-odbc-5.1.10/README.debug [root@v0A-123-177-205 ~]# file /usr/bin/myodbc-installer /usr/bin/myodbc-installer: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped [root@v0A-123-177-205 ~]# /usr/bin/myodbc-installer [ERROR] Not enough arguments given +--- | myodbc-installer v5.01.0010 +--- | | Description | | This program can be used to create, edit or remove a DSN. It | can also be used to register or deregister a driver. In other | words - it can be used to manage ODBC system information. | | This operates consistently across platforms. This has been created | specifically for MySQL Connector/ODBC. | | Syntax | .............
这个说得很清楚
| This program can be used to create, edit or remove a DSN. It | can also be used to register or deregister a driver. In other | words - it can be used to manage ODBC system information.
myodbc-installer程序可以创建，编辑或者删除DSN即数据源（DataSourceName），也可以注册和注销驱动，换句话说，可以当做odbc的管理器，下面将会简单介绍它
4，配置odbc驱动程序管理器，添加mysql驱动程序，即添加DSN即数据源（DataSourceName）
[root@v0A-123-177-205 ~]# vim /etc/odbc.ini # 数据源名字，后来的测试会用到 [mysql_aop_prod_odbc_name] # mysql驱动程序路径 Driver = /usr/lib64/libmyodbc3_r.so Description = MySQL # mysql服务器IP和端口 Server = v0A-123-106-201 Port = 3306 # msyql数据库账号和密码 User = username UID = 290 Password = password # mysql数据库名称 Database = gd Option = 3 Socket =charset = gbk STMT = SET NAMES 'GBK'
配置后，查看下
[root@v0A-123-177-205 bin]# odbcinst -s -q [mysql_aop_odbc_ini] [mysql_aop_prod_odbc_name] [root@v0A-123-177-205 bin]#
mysql_aop_prod_odbc_name就是刚刚添加的mysql驱动程序,
也可以通过myodbc-installer查看，就是上面所说的mysql-connector-odbc-5.1.10-1.rhel5，即mysql驱动程序功能，也具备管理驱动程序的作用
+--- [root@v0A-123-177-205 ~]# odbcinst -j -d unixODBC 2.2.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /root/.odbc.ini [root@v0A-123-177-205 ~]# [root@v0A-123-177-205 ~]# [root@v0A-123-177-205 ~]# [root@v0A-123-177-205 ~]# odbcinst -q -d [PostgreSQL] [MySQL] [PostgreSQL64] [MySQL64] [MySQL ODBC 5.1 Driver] [root@v0A-123-177-205 ~]# odbcinst -q -s [mysql_aop_odbc_ini] [mysql_aop_prod_odbc_name] [root@v0A-123-177-205 ~]# myodbc-installer -d -l PostgreSQL MySQL PostgreSQL64 MySQL64 MySQL ODBC 5.1 Driver [root@v0A-123-177-205 ~]# myodbc-installer -s -l mysql_aop_odbc_ini - /usr/lib/libmyodbc5.so mysql_aop_prod_odbc_name - /usr/lib64/libmyodbc3_r.so [root@v0A-123-177-205 ~]#
4，最后测试mysql驱动程序是否能正常链接远程的mysql数据库
[root@v0A-123-177-205 ~]# [root@v0A-123-177-205 ~]# isql -v mysql_aop_prod_odbc_name +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | gd | | test | +-----------------------------------------------------------------+ SQLRowCount returns 3 3 rows fetched SQL> use gd; SQLRowCount returns 0 SQL>
显然，可以正常链接远程的mysql数据库了，这里使用的是mysql_aop_prod_odbc_name数据源配置，利用64位的mysql驱动程序Driver = /usr/lib64/libmyodbc3_r.so；
isql工具是unixODBC-2.2.11-10.el5提供的，它是64位的程序，
[root@v0A-123-177-205 ~]# rpm -ql unixODBC-2.2.11-10.el5 /usr/bin/dltest /usr/bin/isql /usr/bin/iusql /usr/bin/odbc_config /usr/bin/odbcinst ............ [root@v0A-123-177-205 ~]# file /usr/bin/odbcinst /usr/bin/odbcinst: 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, stripped [root@v0A-123-177-205 ~]# file /usr/bin/isql /usr/bin/isql: 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, stripped [root@v0A-123-177-205 ~]#
使用64位的isql工具，通过32位的mysql驱动程序/usr/lib/libmyodbc5.so，会报错，如下所示，
[root@v0A-123-177-205 ~]# isql -v mysql_aop_odbc_ini [01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5.so' : /usr/lib/libmyodbc5.so: wrong ELF class: ELFCLASS32 [ISQL]ERROR: Could not SQLConnect [root@v0A-123-177-205 ~]# cat /etc/odbc.ini [mysql_aop_odbc_ini] #Driver = /usr/lib64/libmyodbc3_r.so Driver = /usr/lib/libmyodbc5.so Description = MySQL Server = v0A-123-106-201 Port = 3306 User = apprp UID = 290 Password = 123456 Database = gd Option = 3 Socket = charset = gbk STMT = SET NAMES 'GBK' [mysql_aop_prod_odbc_name] Driver = /usr/lib64/libmyodbc3_r.so Description = MySQL Server = v0A-123-106-201 Port = 3306 User = apprp UID = 290 Password = 123456 Database = gd Option = 3 Socket = charset = gbk STMT = SET NAMES 'GBK'
所以暂时还是使用64位的mysql驱动程序/usr/lib64/libmyodbc3_r.so，下一遍文章将会再次说到这个问题。
至此，odbc驱动程序管理器和mysql驱动程序已经安装和配置完毕，下面开始配置oracle通过gateway，调用odbc驱动程序管理器的mysql驱动程序，达到链接远程mysql数据库的目的
二，配置oracle的gatewayoracle 10 以前的版本使用hsodbc做为gateway，后来oracle 11版本使用dg4odbc，其实就是hsodbc的升级版。hsodbc官网上说是不支持64位的，悲剧啊，我的环境是64位的，下一篇文章再详细说说如何解决这个问题
Leave a Reply