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