Setup:
MySQL Server version mysqld-4.0.27-nt on Windows 2008 Server x32
Oracle Database 10.2.0.5.0 on Linux 2.6.32
Oracle Database 11.2.0.0.0 "Oracle 11g Gateway Connectivity - dg4odbc" on Linux 2.6.32
MySQL Connector 3.51.27
I uses particular version of MySQL Connector for two reasons:
When I used last 3.51 version which is 3.51.30 in Oracle I got only row from MySQL database. After reading on OTN and Metalink someone suggested 3.51.27 and all worked as a charm.
When I use newer MySQL ODBC connector (5.1.13 or newer) I got error "Connection using old (pre-4.1.1) authentication protol refused (client option 'secure_auth' enabled)".
Procedure:
Install ODBC connector:
[root@localhost ~]# rpm -Uvh mysql-connector-odbc-3.51.27-0.x86_64.rpm
Check if driver exists in odbcinst.ini
odbcinst.ini is located in /etc or /usr/local/etc
vi /usr/local/etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
[MySQL ODBC 3.51 Driver]
DRIVER = /usr/lib64/libmyodbc3.so
UsageCount = 1
Create odbc.ini and enter driver and connection information
vi /usr/local/etc/odbc.ini
[baza]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc3.so
SERVER =
%IP_ADDRESS_OF_MYSQL_SERVER%
PORT = 3306
USER =
%USER_NAME_ON_MYSQL_SIDE%
Password =
%PASSWORD_ON_MYSQL_SIDE%
Database = baza
CHARSET=latin1
Create entry in listener.ora. Listener is created in Oracle 10g home, but later for connection we will use Oracle 11g Gateway Connectivity - dg4odbc which is installed in separate 11G home.
Reason for that is that 64-bit Oracle database could not use hsodbc driver and it is needed to install Oracle 11g Gateway Connectivity - dg4odbc in separate home.
I had both database homes on same server so it was somewhat easier for me.
vi /u01/app/10.2.0/db/network/admin/listener.ora
baza =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
)
)
)
SID_LIST_baza =
(SID_LIST =
(SID_DESC =
(PROGRAM = dg4odbc)
%Using 11g executable%
(ORACLE_HOME = /u01/app/11.2.0/db)
%Using 11g HOME%
(SID_NAME = baza)
(ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/:/usr/local/etc:/usr/lib/:/u01/app/11.2.0/db/lib)
%Metalink recomends using ENVS Variable in listener%
)
)
Create tnsnames.ora enty (In Oracle 10g Home)
vi /u01/app/10.2.0/db/network/admin/tnsnames.ora
baza=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1527)))
(CONNECT_DATA =
(SID = baza))
(HS=OK)
)
Create ODBC HS ini file (In Oracle 11g Home)
/u01/app/11.2.0/db/hs/admin/initbaza.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = baza
# HS_FDS_TRACE_LEVEL = DEBUG
# HS_FDS_TRACE_LEVEL=user
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
# set <envvar>=<value>
After that you only need to create database link in Oracle 10g database. Put username and password in quotation marks if columns in MySQL datavase are declared with a case sensitive or binary collation
create database link baza
connect to "exuser"
identified by "userex"
using 'baza';
If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation
