Preparing ODBC Driver to Connect to MySQL

Correct connection to MySQL databases requires installation of ODBC drivers. It supports execution of queries and DBMS commands alone. It supports creating and use of tables, views, procedures and functions depending on the functionality of a certain DBMS and its correspondence to SQL standards.

This article contains the example of creating a database, a table, and a user for connecting to MySQL using the Connector/ODBC driver on Ubuntu 16.04.

First, install MySQL front and back ends:

sudo apt-get install mysql-server

sudo apt-get install mysql-client

Install unixodbc and odbcinst if they have not been installed:

sudo apt-get install unixodbc

sudo apt-get install odbcinst

The libmyodbc library has been deleted from the official repository in Ubuntu 16.04. Therefore, install Connector/ODBC manually.

TIP. It is recommended to download MySQL Connector/ODBC 5.3.13 for MySQL 5.7.

After the ODBC driver is prepared, install or restart the I server if it has been installed before.

Installing Connector/ODBC

Consider the example of installing Connector/ODBC on Ubuntu 16.04.

To install Connector/ODBC, execute the following operations:

  1. Download the archive with source files of Connector/ODBC.

  2. Unpack the archive:

tar xvf mysql-connector-odbc-5.3.13-linux-ubuntu16.04-x86-64bit.tar

  1. Copy contents of the bin and lib folders to the Linux home directory:

cd mysql-connector-odbc-5.3.13-linux-ubuntu16.04-x86-64bit

cp bin/* /usr/local/bin

cp lib/* /usr/local/lib

  1. Register the selected driver version (ANSI, Unicode or both):

//Unicode Driver

myodbc-installer –a –d –n “MySQL ODBC 5.3 Unicode Driver” –t “Driver=/usr/local/lib/libmyodbc5w.so”

//ANSI Driver

myodbc-installer –a –d –n “MySQL ODBC 5.3 ANSI Driver” –t “Driver=/usr/local/lib/libmyodbc5a.so”

  1. Make sure that the driver is installed and registered by means of the command:

myodbc-installer –d –l

After the compilation the console should display names of the registered drivers:

MySQL ODBC 5.3 Unicode Driver

MySQL ODBC 5.3 ANSI Driver

NOTE. Information about registered drivers is stored in the /etc/odbcinst.ini configuration file.

  1. Edit the settings file /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf to connect to the remote MySQL server. One of the settings files contains the line:

bind-adress = 127.0.0.1

To connect to database from any IP address, replaces the line with the following:

bind-adress = 0.0.0.0

  1. Restart MySQL:

sudo systemctl restart mysql

Creating a Database, a Table and a User

To create a database:

  1. Create a database in MySQL with the test_database identifier.

  2. Create a table with the test_table identifier in the database and populate it with values from 1 to 10.

  3. Create a user and grant him permissions to connect to the database:

create user ‘test_user’@’%’ identified by ‘123’;

grant all privileges on *.* to ‘test_user’@’%’ identified by ‘123’;

% - the user can connect from any IP address.

  1. Create DSN for the previously created database.

sudo myodbc-installer –s –a –c2 –n “TestDSN” –t “DRIVER=MySQL ODBC 5.3 Unicode

Driver;SERVER=10.9.162.87;DATABASE=test_database;UID=test_user;PWD=123”

  1. Connect to the created DSN:

isql TestDSN

If the connection to the created DSN is successful, the following message is displayed:

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

NOTE. The created DSN are stored in the /etc/odbc.ini configuration file.

See also:

Additional Settings