Configure ODBC for MySQL on CentOS

Migrate Users from MySQL to MySQL

Migrate Users from MySQL to MySQL

We have already installed ODBC drivers for Oracle and PostgreSQL in prior articles.  In this article, we will install ODBC drivers on CentOS to connect us to an MySQL database.  The general idea is similar to the steps we followed for the PostgreSQL drivers, but ever so different as to require different instructions.

Install unix ODBC and Mysql-connector-odbc with Yum

# yum install unixODBC mysql-connector-odbc



Installing these packages will these following driver files to help us setup our connections.

# ls -lh odb*
-rw-r--r-- 1 root root   0 Jul 10 02:15 odbc.ini
-rw-r--r-- 1 root root 575 Jul 10 02:15 odbcinst.ini

The odbcinst.ini file maps all of the files needed for the driver to operatre properly. We can take a peek at the file below:

# cat /etc/odbcinst.ini
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[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

The other file (/etc/odbc.ini) is where we configure the details required to connect to our MySQL database. This includes the IP Address, userid, password, database name, etc… for the connection.

Edit the file now and add the information necessary for our specific database instance.

# cat /etc/odbc.ini
[uptimedb]
Description         = MyUptimeMysqlServer
Driver              = MySQL
Database            = uptimedb1
Server              = 12.34.56.78
User                = myuserhere
Password            = mypasswordhere
Port                = 3306

By the way, you will need to login to the database in question if you haven’t already and create the necessary credentials.TW:

mysql> grant select on *.* to 'myuserhere'@'12.34.56.79' identified by 'mypasswordhere';
Query OK, 0 rows affected (0.03 sec)

That should be it. We should have everything setup properly to connect to our database using the ODBC connection command isql. If there is some issue at this point, you may have an issue with your iptables firewall or some other similar issue.

Use ISQL to Connect to MySQL Using our ODBC Connection Configuration

Now Connect:

# isql -v uptimedb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database                                                        |
+-----------------------------------------------------------------+
| information_schema                                              |
| uptimedb1                                                       |
| uptimedb2                                                       |
| uptimedb3                                                       |
| uptimedb4                                                       |
| mysql                                                           |
| test                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 7
7 rows fetched
SQL>

And Success! We have connected.  The instructions above should probably resolve most of your questions and get you connected.  At some point, you may need some additional resources.

Additional Resources

From MySQL Documentation Website:

http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation-binary-yum.html

From unixODBC website:

http://www.unixodbc.org/doc/

The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *