561   Linux MySQL Oracle

oracle读取mysql数据库内容,一般采用odbc作为跳板,流程图如下

heter004

 

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

Your email address will not be published. Required fields are marked *