Connecting MySQL via Dblink in Oracle

There is a demand in the business, need to sync some Oracle data to MySQL, if each time is manual synchronization, it is too troublesome, so spent a little time to study the next Oracle direct connection MySQL way.

Reference Document: Detailed Overview of connecting Oracle to MySQL Using dg4odbc Database Link (Doc ID 1320645.1)

Version information:

oracle:11.2.0.1.0 Os:centos 5.9

mysql:5.5.27 Os:centos 5.8

Principle:

Oracle uses the DG4ODBC data gateway to connect to other non-Oracle databases, and its schematic diagram is as follows:

From the figure above, Oracle connection MySQL needs to involve the following components: DG4ODBC, ODBC Driver Manager, ODBC Driver, this article will explain their configuration.

1) to determine 32 or 64-bit

Because the 32-bit and 64-bit configurations are not the same, 64 bits are more complex, so we first have to determine whether Oracle and DG4ODBC are 32 or 64 digits:

[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

The output from above is 64 bits.

2) Download and install ODBC Driver Manager

To this page (http://www.unixodbc.org/download.html) Download UNIXODBC according to your OS (Note: Version cannot be lower than 2.2.14)

$ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/ Download

Decompression:

$ TAR-ZXVF unixodbc-2.2.14-linux-x86-64.tar.gz

After decompression, the USR directory is automatically created in the current directory, and we create a directory (~/app/unixodbc-2.2.14) to place the UNIXODBC and then migrate usr to that directory:

$ mkdir ~/app/unixodbc-2.2.14

$ mv usr ~/app/unixodbc-2.2.14

3 Download and follow ODBC Driver for MySQL

To this page (http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads) download ODBC-5.2.5 based on your OS, this example selects the 64-bit tar version:

$ 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

After the decompression is successful, it is a folder that migrates the folder to the ~/app directory and creates a soft link to it:

$ 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