Connect CentOS to MySQL ODBC

Connect CentOS to MySQL ODBC

MySQL Connector/ODBC logo

There are many uses for this, usually reporting, or some other data manipulation. Let’s get started, login and su to root

ODBC on Linux has two parts: the unixODBC driver manager, which applications and tools such as isql talk to, and a database driver (here MySQL Connector/ODBC) that unixODBC loads. Drivers are defined in /etc/odbcinst.ini and named connections (DSNs) in /etc/odbc.ini.

Prerequisites

  • A remote MySQL server reachable on TCP 3306 from this host (firewall open, MySQL not bound to 127.0.0.1 only).
  • A MySQL account allowed to connect from this client’s IP, for example:
CREATE USER 'unixodbc'@'192.168.1.%' IDENTIFIED BY 'StrongPassword';
GRANT SELECT ON unixODBC.* TO 'unixodbc'@'192.168.1.%';

1) Install the driver

Root shell prompt on the CentOS-Base server before setting up MySQL ODBC

install some basic tools

Running yum -y install mysql-connector-odbc.x86_64 on CentOS

yum -y install mysql-connector-odbc.x86_64 also pulls in unixODBC as a dependency.

2) Check where unixODBC reads its config

view the basics of your config in a nice info file

odbcinst -j output showing unixODBC 2.2.14 and the odbcinst.ini and odbc.ini paths

odbcinst -j prints the unixODBC version and the paths of the driver file (DRIVERS) and system DSN file (SYSTEM DATA SOURCES). If your files are somewhere else, this is where you find out.

3) Review the driver definitions

view the ODBC config instance file, this is for different database types

Running cat /etc/odbcinst.ini to view the ODBC driver definitions

as you can see MySQL and PostgreSQL are allready configured

odbcinst.ini with PostgreSQL and MySQL driver entries already defined

The [MySQL] section name is what a DSN refers to with Driver = MySQL. Make sure the Driver64 library path exists on disk (ls -l /usr/lib64/libmyodbc5.so).

4) Create the DSN

from here we need to create specific instances/connections.

unixODBC DSN in odbc.ini for a remote MySQL server on port 3306 using the MySQL driver

here’s a look at a sample ODBC connection config

Sample unixODBC connection config with SERVER, PORT, DATABASE, USERNAME and PASSWORD fields

A DSN in /etc/odbc.ini looks like this (the section name is the DSN name you connect to):

[unixODBC]
Description = Connection to Remote MySQL Box
Trace       = Off
TraceFile   = stderr
Driver      = MySQL
SERVER      = 192.168.1.50
PORT        = 3306
DATABASE    = unixODBC
USER        = unixodbc
PASSWORD    = StrongPassword

The screenshot uses USERNAME; the keys documented by Connector/ODBC are USER (or UID) and PASSWORD (or PWD). You can also leave the credentials out of the file and pass them to isql, which keeps the password out of a world-readable file.

save + quit

5) Register the driver and DSN

from here install the ODBC driver, and install SystemDSN

Running odbcinst -i -d -f /etc/odbcinst.ini and odbcinst -i -s -l -f /etc/odbc.ini
odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

-i -d installs driver definitions, -i -s -l installs the DSNs as system-wide data sources. Confirm with odbcinst -q -d (drivers) and odbcinst -q -s (DSNs).

6) Test the connection

test the connection using the Database name {space} username

Running isql unixODBC unixodbc and getting the Connected! prompt

The syntax is isql DSN [user [password]]. Add -v to see the real error message if it fails.

as we can see basic SQL Queries work

SHOW DATABASES run through isql over ODBC listing the unixODBC database

that’s it!

Troubleshooting

  • Data source name not found: the DSN name does not match a section in the odbc.ini that odbcinst -j reports.
  • Can’t open lib: the Driver/Driver64 path in odbcinst.ini is wrong or the package is not installed.
  • Access denied for user: the MySQL account or its host part does not match this client; check the GRANT.
  • Can’t connect to MySQL server: firewall, wrong SERVER/PORT, or MySQL listening on localhost only (bind-address).

RHEL, AlmaLinux and Rocky 9/10

Use dnf. dnf install unixODBC mariadb-connector-odbc installs the driver manager and the MariaDB Connector/ODBC from AppStream, which also connects to MySQL servers for normal queries; MySQL’s own Connector/ODBC is available from the MySQL repository. Find the driver library with rpm -ql mariadb-connector-odbc | grep '\.so' and point Driver in odbcinst.ini at it. The odbcinst and isql commands are the same.

Hosted Linux Servers at www.zwiegnet.com/go

Related guides: MySQL Replication CentOS All Databases | Configure MySQL Multi-Master Replication

Leave a Reply

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