Connect to MySQL Using ODBC from a Remote Server

For this example, I will use two $5 a month Ubuntu 20.04 LTS Server Droplets from Digital Ocean.

You will need the IP Addresses of both servers, and both servers should be able to ping each other using each others IP addresses.

Server A

Install MySQL

~# sudo apt install mysql-server

Check it is running

~# sudo service mysql status

It should say active (running).

While still on Server A, create a user that Server B can use to connect to the MySQL running on the Server A.

~# mysql

mysql> CREATE USER 'username'@'Server B Ip Address' IDENTIFIED BY 'password';

Grant the user specific permissions.

If you already had a MySQL Server with a MySQL database on it, then you could allow your new user all privileges to it,

mysql> GRANT ALL PRIVILEGES ON your-database_name.* TO 'username'@'Server B Ip Address';

Otherwise, for the purpose of this exercise, we can just allow access to the existing pre built database on the new MySQL server which is also named mysql.

mysql> GRANT ALL PRIVILEGES ON mysql.* TO 'username'@'Server B Ip Address';

Server B

Now, SSH onto your Server B and install unixodbc

~# sudo apt install unixodbc

Next download the mysql-connector-odbc ubuntu package from Oracle.

~# wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.21-1ubuntu20.04_amd64.deb

And install it

~# dpkg -i mysql-connector-odbc_8.0.21–1ubuntu20.04_amd64.deb

For more information about downloading and which versions of mysql-connector-odbc are available, visit MySQL Connector/ODBC Installation

Now check your unixodbc configuration.

~# odbcinst -j

The response should be something like,

unixODBC 2.3.6
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8