How to setup and configure MySQL with unixODBC under Ubuntu 14.04

There are a lot of tutorials out there describing how to get ODBC work with MySQL under Linux. Unfortunately, most of them were not mentioned to be tutorials and their solutions often do not work reliably and stable.

The following has been tested on a Ubuntu 14.04 machine (MySQL 5.5.37, Apache 2.4.7, PHP 5.5.9, unixODBC 2.2.14) and works stable until now.

Make sure you have root access to your system, either by switching to root user:

su root

or by adding the „sudo“ prefix to each command:

sudo ls
sudo vi
...

Install and configure MySQL:

apt-get install mysql-server mysql-client

Install ODBC drivers:

apt-get install libmyodbc unixodbc-bin

Lookup the ODBC driver’s location:

find / -name 'lib*odbc*.so'

OUTPUT (BEISPIEL):
/usr/lib/i386-linux-gnu/odbc/libodbcnnS.so
/usr/lib/i386-linux-gnu/odbc/libodbctxtS.so
/usr/lib/i386-linux-gnu/odbc/libodbcdrvcfg1S.so
/usr/lib/i386-linux-gnu/odbc/libodbcmyS.so
/usr/lib/i386-linux-gnu/odbc/liboraodbcS.so
/usr/lib/i386-linux-gnu/odbc/libodbcpsqlS.so
/usr/lib/i386-linux-gnu/odbc/liboplodbcS.so
/usr/lib/i386-linux-gnu/odbc/libodbcminiS.so
/usr/lib/i386-linux-gnu/odbc/libmyodbc.so
/usr/lib/i386-linux-gnu/odbc/libodbcdrvcfg2S.so

Create your custom ODBC installation file:

vi /etc/odbcinst.ini

Put this in your odbcinst.ini:

[myodbc_mysql]
Description = ODBC for MySQL
Driver      = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so
Setup       = /usr/lib/i386-linux-gnu/odbc/libodbcmyS.so
UsageCount  = 1

Lookup the MySQL socket path:

mysqladmin -u root -p version

OUTPUT (EXAMPLE):
Server version 5.5.37-0ubuntu0.14.04.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 7 hours 14 min 15 sec

Create your ODBC configuration file:

vi /etc/odbc.ini

Put this in your odbc.ini:

[myodbc_mysql_dsn]                        # this is your system DSN
Description = description of your DSN
Driver      = myodbc_mysql                # custom driver name
Server      = localhost                   # or external IP if needed
Port        = 3306                        # or custom port if needed
Socket      = /var/run/mysqld/mysqld.sock # socket, see above
Database    = mysqldb1                    # MySQL DB name or empty
Option      = 3
ReadOnly    = No

Install the ODBC driver:

odbcinst -i -d -f /etc/odbcinst.ini

Install your system DSN:

odbcinst -i -s -l -f /etc/odbc.ini

Test if your system DSN was installed successfully:

odbcinst -s -q

OUTPUT (EXAMPLE):
[myodbc_mysql_dsn]

Test if you can connect to your MySQL database via ODBC:

isql -v myodbc_mysql_dsn MYSQLUSER MYSQLUSERPASSWORD

OUTPUT (EXAMPLE):
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

13 Gedanken zu „How to setup and configure MySQL with unixODBC under Ubuntu 14.04

  1. Goran Šagovac

    This tutorial is ok, but there’s a one q. I was putting everything as it was,and last part when i put isql -v myodbc_mysql_dsn MYSQLUSER MYSQLUSERPASSWORD i recive [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect any advice is acceptable,
    WHERE DID I GO WRONG

    Antworten
    1. Joger

      Hello,

      I do that:

      odbcinst -s -q

      And I can see that:

      joger@debian-joger:~$ odbcinst -s -q
      [myodbc_mysql_dsn]

      But when I try to do that:

      isql -v myodbc_mysql_dsn myuser mypassword

      I recive that:

      [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
      [ISQL]ERROR: Could not SQLConnect

      Do you hace any idea what is happening?

      Thank you!

      Antworten
  2. CaliforniaChronic

    Hello,
    I appreciate this help. Could not find anything on the web. If I may ask, where can I find a good book about this odbc on linux?

    Antworten
  3. Styx

    Thanks this helped alot.

    for the folks working on 64 bit odbcinst.ini looks like

    [MySQL]
    Description = ODBC for MySQL
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
    FileUsage = 1

    Antworten
  4. Juicy

    Hi, Thanx for this auto but I need help.
    this step :
    Lookup the MySQL socket path:

    mysqladmin -u root -p version
    OUTPUT (EXAMPLE):
    Server version 5.5.37-0ubuntu0.14.04.1
    Protocol version 10
    Connection Localhost via UNIX socket
    UNIX socket /var/run/mysqld/mysqld.sock
    Uptime: 7 hours 14 min 15 sec

    Doesn’t work for me. It always ask me to enter a password and it’s always the same error: „connect to server at ‚localhost‘ failed. error:’Access denied for user ‚root’@’localhost‘ (using password: YES)’“

    Do you know this problem?
    (Ubuntu 14.04 version)

    Antworten
    1. Ingo Hagemann und Oliver Kuna Beitragsautor

      Hi Juicy, looks as if your MySQL server is protected by password. Have you tried the following commands to make MySQL show its socket path?

      mysqladmin --version
      mysqladmin version
      mysqladmin -uroot -pPASS123 version

      PASS123 must be replaced by your root password.

      Antworten
      1. juicy

        Thank you so much for help but finally, I ran
        – locate my.cnf
        – nano /etc/mysql/my.cnf
        and I saw the mysql socket path inside was the right one.
        😉

        Antworten
  5. Juicy

    Hi everybody, another solution for this following problem :
    „IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect“
    I changed in „/etc/odbc.ini “ -> DRIVER = „myodbc_mysql“ by the adress of the driver SERVER=“/usr/lib/xxxx-linux-gnu/odbc/libmyodbc.so
    It worked like that for me.
    Voilà.

    Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert