MySQL/Connectors

Windows - ODBC Connector installation :

myodbc-installer -d -l 
myodbc-installer -d -l -n "<name of the driver>"

• Install of unixODBC

The installation of the unixODBC is pretty straight forward:

[root@*******~]# yum install unixODBC
Loaded plugins: product-id, refresh-packagekit, security, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Setting up Install Process
Package unixODBC-2.2.14-12.el6_3.x86_64 already installed and latest version
Nothing to do

• Configuration odbc.ini and .odbc.ini

The configuration is different for the different drivers which you have installed. All available drivers can be seen in the following files:

[root@appid01-tj ~]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: **/etc/odbcinst.ini**
SYSTEM DATA SOURCES: **/etc/odbc.ini**
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: **/root/.odbc.ini**
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@appid01-tj ~]# 
<sxh>

The first file contains the configuration about the installed drivers on the system:

<sxh bash>
[apainst1@appid01-tj ~]$ cat /etc/odbcinst.ini
[PostgreSQL]
Description             = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64                = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage               = 1

[MySQL]
Description             = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64                = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage               = 1
UsageCount              = 2

While the second one contains information about the declaration of all DSNs:

[apainst1@*******~]$ cat /etc/odbc.ini
[ODBC Data Sources]
MySQL_APACDR01_APACDR=ODBC for MySQL

[MySQL_APACDR01_APACDR]
Driver=/usr/lib64/libmyodbc5.so
Description     = MySQL connection
Trace       = Yes 
TraceFile   = /tmp/mysqltrace.log 
SERVER      = ip-addr
USER        = username
PASSWORD    = password
PORT        = port_number
DATABASE    = database_name

The last file is the same configuration but per user. Each user should have his own: “.odbc.ini”, with the settings which he is able to access. Tracing of the odbc connection can be done via:

[username@****** ~]$  strace -e trace=open isql MySQL_APACDR01_APACDR  2>&1 | grep odbc.ini
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/home/apainst1/.odbc.ini", O_WRONLY|O_CREAT|O_APPEND, 0666) = 3
open("/home/apainst1/.odbc.ini", O_RDONLY) = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
open("/etc/odbc.ini", O_RDONLY)         = 3
[apainst1@appid01-tj ~]$ 

The firewall port 3306 should be unlocked. In case all is fine, you should have the following output:

• Test the connection

[apainst1@appid01-tj ~]$ isql -v MySQL_APACDR01_APACDR
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>