oracle通过透明网关访问mysql
- 格式：doc
- 大小：80.00 KB
- 文档页数：10
Oracle Database SupportOracle 通过透明网关访问mysql（仅限美创科技内部人员浏览,不得外传）工程师：施嘉伟创建日期：2017-07-01最后修改：2017-07-01服务商：XXXX科技有限公司文档控制修改记录审阅目录文档控制 (2)修改记录 (2)审阅 (2)目录 (3)一部署环境说明 (4)二部署具体步骤 (4)1.首先要检查Oracle和DG4ODBC是32位还是64位. (4)2.下载并安装64位的ODBC Driver Manager UnixODBC (4)3.下载并安装ODBC Driver (4)4．在安装的/usr/local/unixODBC/etc/路径下配置odbc.ini配置文件 (5)5. 在.bash_profile配置相关环节变量LD_LIBRARY_PA TH、UNIXODBC_DIR、ODBCINI及ODBCSYSINI (6)6. 验证ODBC连接 (7)7. 配置tnsnames.ora (7)8. 配置listener.ora (8)9. 创建oracle相关init<sid>.ora文件 (8)10. 使上述配置文件生效 (9)11. 验证配置是否正确 (9)12. 创建dblink名称mysql_link并查询ljw数据库table_test表中的数据 (9)一部署环境说明oracle服务器:IP:192.168.142.13（centos7.2）Database Version:11.2.0.4mysql服务器IP:192.168.142.12（centos7.2）mysql version: 5.6.35二部署具体步骤1.首先要检查Oracle和DG4ODBC是32位还是64位.[oracle@ljw ~]$ file /oracle/app/product/11.2.0/db_1/bin/dg4odbc/oracle/app/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped这里可以看到是64位的。
所以必须使用64位的ODBC Driver Manager和64位的ODBC Driver.2. 下载并安装64位的ODBC DriverManager UnixODBC下载地址:请参看官方文档Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档ID 1320645.1)下载最新版本3. 下载并安装ODBC Driver以上两个介质上传至：/tmpRoot用户:[root@ljw tmp]# ls –rtltotal 12756-rw-r--r-- 1 root root 11230673 Jul 1 00:17 mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz-rw-r--r-- 1 root root 1830660 Jul 1 00:21 unixODBC-2.3.4.tar.gz[root@ljw tmp]# chmod 777 *[root@ljw tmp]# ls -rtltotal 12756-rwxrwxrwx 1 root root 11230673 Jul 1 00:17 mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz -rwxrwxrwx 1 root root 1830660 Jul 1 00:21 unixODBC-2.3.4.tar.gzOracle用户：tar -zxvf mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gzmv mysql-connector-odbc-5.3.8-linux-el6-x86-64bit /oracle/app/my-odbc-drive-5.3.8tar -zxvf unixODBC-2.3.4.tar.gzRoot安装：[root@ljw /]# mkdir -p /usr/local/unixODBC[root@ljw /]# cd /tmp/unixODBC-2.3.4[********************.4]#./configure--prefix=/usr/local/unixODBC[********************.4]#make&makeinstall4. 在安装的/usr/local/unixODBC/etc/路径下配置odbc.ini配置文件[root@ljw /]# cd /usr/local/unixODBC/etc/[root@ljw etc]# ls –rtltotal 4-rw-r--r-- 1 root root 0 Jul 1 14:08 odbcinst.ini-rw-r--r-- 1 root root 0 Jul 1 14:08 odbc.inidrwxr-xr-x 2 root root 4096 Jul 1 14:08 ODBCDataSources[root@ljw etc]# vi odbc.ini[myodbc5]Driver=/oracle/app/my-odbc-drive-5.3.8/lib/libmyodbc5w.soDescription=Connector/ODBC 5.3.8 Driver DSNSERVER=192.168.142.12PORT=3306USER=mysqlPASSWORD=mysqlDA TABASE=ljwOPTION=0TRACE=OFF5. 在.bash_profile配置相关环节变量LD_LIBRARY_PATH、UNIXODBC_DIR、ODBCINI及ODBCSYSINIOracle用户：[oracle@ljw etc]$ cd[oracle@ljw ~]$ vi .bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH=$PA TH:$HOME/binexport PATHumask 022export ORACLE_BASE=/oracle/appexport ORACLE_HOME=/oracle/app/product/11.2.0/db_1export ORACLE_SID=orclexport PATH=$ORACLE_HOME/bin:$PA THLD_LIBRARY_PATH=/usr/local/unixODBC/lib:$ORACLE_HOME/lib:/usr/libUNIXODBC_DIR=/usr/local/unixODBCODBCINI=$UNIXODBC_DIR/etc/odbc.iniODBCSYSINI=$UNIXODBC_DIR/etcexport ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATHUNIXODBC_DIR ODBCINI ODBCSYSINI6. 验证ODBC连接Mysql服务器端mysql> create user mysql@192.168.142.13 identified by 'mysql';Query OK, 0 rows affected (0.00 sec)mysql> grant all on ljw.* to 'mysql'@'192.168.142.13';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)[oracle@ljw ~]$ cd /usr/local/unixODBC/bin/[oracle@ljw bin]$ ./isql myodbc5 -v+---------------------------------------+| Connected! || || sql-statement || help [tablename] || quit || |+---------------------------------------+SQL> quit;7. 配置tnsnames.ora[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/network/admin/tnsnames.oramyodbc5 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521))(CONNECT_DATA =(SID = myodbc5))(HS=OK))8. 配置listener.ora[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /oracle/app/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))ADR_BASE_LISTENER = /oracle/appSID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=myodbc5)(ORACLE_HOME=/oracle/app/product/11.2.0/db_1)(PROGRAM=dg4odbc)(ENV="LD_LIBRARY_PA TH=/usr/local/unixODBC/lib:/oracle/app/product/11.2.0/db_1/lib:/usr/lib") ))9. 创建oracle相关init<sid>.ora文件[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/hs/admin/initmyodbc5.oraHS_FDS_CONNECT_INFO = myodbc5HS_FDS_TRACE_LEVEL = offHS_FDS_TRACE_FILE_NAME = myodbc5.trcHS_LANGUAGE = AMERICAN_AMERICA.UTF8HS_NLS_NCHAR = UCS2HS_FDS_SHAREABLE_NAME =/usr/local/unixODBC/lib/libodbc.soset LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/lib:/usr/lib64set ODBCINI=/usr/local/unixODBC/etc/odbc.ini10. 使上述配置文件生效[oracle@ljw /]$ lsnrctl reload11. 验证配置是否正确[oracle@ljw /]$ tnsping myodbc5TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JUL-2017 14:45:39Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:/oracle/app/product/11.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521)) (CONNECT_DA TA = (SID = myodbc5)) (HS=OK))OK (0 msec)12. 创建dblink名称mysql_link并查询ljw数据库table_test表中的数据[oracle@ljw /]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 1 14:47:45 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create public database link mysql_dblink connect to "mysql" identified by "mysql" using 'myodbc5';Database link created.SQL> select * from "table_test"@mysql_dblink;id name-------------------- -------------------- 1 hzmcdba。
