How to setup and configure MySQL with unixODBC under Ubuntu 14.04

Veröffentlicht am Veröffentlicht in MySQL

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 (EXAMPLE):
    /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                                  |
    |                                       |
    +---------------------------------------+

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

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

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

      1. 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!

  2. 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?

  3. 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)

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

      1. 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.
        😉

  4. 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à.

Schreibe einen Kommentar

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