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 |
| |
+---------------------------------------+
Thanks! Really useful article. In fact, the only article I found to be working about odbc in Ubuntu.
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
I had the same problem, you must specify -U before username and -P before password
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!
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?
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
In odbc.ini the comments caused some problems.
When I removed the comments, everything worked correctly.
I think i have manage to solve core dump seg fault for xub 15.10. So last part where is said isql u put iusql…..
Thanks, you’ve saved my days 😀
Terima kasih
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)
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 --versionmysqladmin version
mysqladmin -uroot -pPASS123 version
PASS123 must be replaced by your root password.
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.
😉
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à.