Oracle 11.2.0.4 通过透明网关访问mysql 8.0.16

时间:2022-11-29 18:10:59

环境准备

oracle 服务器 :

IP:192.168.142.11 redhel 6.4

Database Version:11.2.0.4

mysql 服务器

IP:192.168.142.12 centos7

mysql version: 5.6.35-log LLL for mysqltest


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 Driver Manager UnixODBC

 

下载地址 :

请参看官方文档   Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link ( 文档  ID 1320645.1)

 

下载最新版本

 


3. 下载并安装 ODBC Driver

http://dev.mysql.com/downloads/connector/odbc/#downloads

 

以上两个介质上传至: /tmp

 

Root 用户  :

 

[root@ljw tmp]# ls –rtl

 

total 12756

-rw-r--r-- 1 root root 11230673 Jul  1 00:17 mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

-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 -rtl

total 12756

-rwxrwxrwx 1 root root 11230673 Jul  1 00:17 mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

-rwxrwxrwx 1 root root  1830660 Jul  1 00:21 unixODBC-2.3.4.tar.gz

 

 

Root 安装:

[root@ljw /]# tar -zxvf unixODBC-2.3.4.tar.gz

[root@ljw /]# mkdir -p /usr/local/unixODBC

[root@ljw /]# cd /tmp/unixODBC-2.3.4

[root@ljw unixODBC-2.3.4]# ./configure --prefix=/usr/local/unixODBC

[root@ljw unixODBC-2.3.4]# make & make install


[root@ljw unixODBC-2.3.4]# rpm -ivh  mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

 

4 .在安装的 /usr/local/unixODBC/etc/ 路径下配置 odbc.ini 配置文件

 

[root@ljw /]# cd  /usr/local/unixODBC/etc/

[root@ljw etc]# ls –rtl

 

total 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.ini

drwxr-xr-x 2 root root 4096 Jul  1 14:08 ODBCDataSources

 

[root@ljw etc]# vi odbc.ini

 

[myodbc5]
Driver=/usr/lib64/libmyodbc8w.so
Description=Connector/ODBC 8.0.16 Driver DSN
SERVER=192.168.142.12
PORT=3306
USER=mysql
PASSWORD=mysql
DATABASE=ljw
OPTION=0
TRACE=OFF




5.  .bash_profile 配置相关环节变量 LD_LIBRARY_PATH UNIXODBC_DIR ODBCINI ODBCSYSINI

 

[oracle@ljw etc]$ cd

[oracle@ljw ~]$ vi .bash_profile

 

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

 

umask 022

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=/oracle/app/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$PATH

 

LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$ORACLE_HOME/lib:/usr/lib

UNIXODBC_DIR=/usr/local/unixODBC

ODBCINI=$UNIXODBC_DIR/etc/odbc.ini

ODBCSYSINI=$UNIXODBC_DIR/etc

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH UNIXODBC_DIR ODBCINI ODBCSYSINI



6.  验证 ODBC 连接

 

Mysql 服务器端:

 

mysql> create user mysql@ 192.168.142.11 identified by 'mysql';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant all on  ljw .* to 'mysql'@'192.168.142.11';

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.ora

 

myodbc5 =

  (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/app

 

SID_LIST_LISTENER=

     (SID_LIST=

        (SID_DESC=

             (SID_NAME=myodbc5)

             (ORACLE_HOME=/oracle/app/product/11.2.0/db_1)

             (PROGRAM=dg4odbc)

             (ENV="LD_LIBRARY_PATH=/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.ora

 

HS_FDS_CONNECT_INFO = myodbc5
HS_FDS_TRACE_LEVEL = off
HS_FDS_TRACE_FILE_NAME = myodbc5.trc
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SHAREABLE_NAME =/usr/local/unixODBC/lib/libodbc.so
set LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/lib:/usr/lib64
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini



10.  使上述配置文件生效

 

[oracle@ljw /]$ lsnrctl reload


11.  验证配置是否正确

 

[oracle@ljw /]$ tnsping myodbc5

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JUL-2017 14:45:39

 

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

 

Used parameter files:

/oracle/app/product/11.2.0/db_1/network/admin/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK))

OK (0 msec)


12.  创建 dblink 名称 mysql_link 并查询 ljw 数据库 table_test 表中的数据

 

[oracle@ljw /]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 1 14:47:45 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> 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