业务上有这么一个需求,需要把Oracle的一些数据同步到MySQL,如果每次都是手动同步的话,实在太麻烦,因此花了点时间研究了下Oracle直连MySQL的方式。
版本信息:
Oracle: 11.2.0.1.0 OS: CentOS 5.9
MySQL: 5.5.27 OS: CentOS 5.8
原理:
Oracle使用DG4ODBC数据网关连接其它非Oracle数据库,其原理图如下:
从上图可知,Oracle连接MySQL需要涉及到如下组件:DG4ODBC, ODBC Driver Manager, ODBC Driver,本文将一一讲解它们的配置。
1)判断32位还是64位
因为32位和64位的配置不一样,64位更复杂一些,因此我们首先得确定Oracle和DG4ODBC是32位还是64位:
[oracle@lx16 ~]$ file $ORACLE_HOME/bin/dg4odbc
/home/oracle/app/oracle/product/11.2.0/dbhome_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), not stripped
从上面的输出可知是64位。
2)下载并安装ODBC Driver Manager
到这个页面(http://www.unixodbc.org/download.html)根据你的OS下载unixodbc(注意:版本不能低于2.2.14)
$ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
解压缩:
$ tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz
解压缩后会在当前目录下自动创建usr的目录,我们创建一个目录(~/app/unixodbc-2.2.14)用于放置unixodbc,然后把usr 迁移到该目录下:
$ mkdir ~/app/unixodbc-2.2.14
$ mv usr ~/app/unixodbc-2.2.14
3)下载并按照ODBC Driver for MySQL
$ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.2/mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz/from/http://cdn.mysql.com/
$ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz
解压缩成功后是一个文件夹,把该文件夹迁移至~/app目录下,并给它创建一个软链接:
$ mv mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit ~/app
$ cd ~/app
$ ln -s mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit myodbc-5.2.5
4)配置ODBC Driver
在~/etc目录下创建odbc.ini如下:
[myodbc5]
Driver = /home/oracle/app/myodbc-5.2.5/lib/libmyodbc5w.so
Description = Connector/ODBC 5.2 Driver DSN
SERVER = 192.168.1.15
PORT = 3306
USER = mysql_user
PASSWORD = mysql_pwd
DATABASE = mysql_db
OPTION = 0
TRACE = OFF
其中,Driver指向第3步上按照的ODBC Driver,这里要特别注意:MySQL的Datbase是大小写敏感的。
5)验证ODBC连接
$ export ODBCINI=/home/oracle/etc/odbc.ini
$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:$LD_LIBRARY_PATH
$ cd ~/app/unixodbc-2.2.14/usr/local/bin
$ ./isql myodbc5 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
上面显示连接成功。
6)配置tnsnames.ora
myodbc5 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)
)
(CONNECT_DATA=
(SID=myodbc5)
)
(HS=OK)
)
7)配置listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib)
)
)
如上所示,为了避免和其它已存在的ODBC Driver Manager冲突,强烈设置LD_LIBRARY_PATH在listener.ora
8)创建init.ora文件
创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora,内容如下:
HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/usr/local/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
# ODBC env variables
set ODBCINI=/home/oracle/etc/odbc.ini
9)使上述配置文件生效
$ lsnrctl reload
$ lsnrctl status
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
10)验证配置是否正确
$ tnsping myodbc5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2013 10:54:46
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
OK (0 msec)
11)创建dblink
SQL> create public database link mysqltest connect to "mysql_user" identified by "mysql_pwd" using 'myodbc5';
SQL> select count(*) from trans_expert_map@mysqltest;
COUNT(*)
----------
371