When setting up a large website or application, it's good practice to have your database and web hosting on separate servers. This allows each component to have dedicated resources and ensures you don't have a single point of failure for both. However, doing so also adds extra complexity to your management – you must log in to two separate servers. This isn't a big deal if you're running one application or site, but it quickly becomes annoying if you manage multiple or frequently perform database tasks.

While you can install server management software such as Cockpit, setting up a standard remote connection to your SQL database is often more straightforward and has less overhead. We'll walk you through the process below.

How to allow remote access to MySQL

By default, and for good reason, MySQL instances are configured to listen to local connections only. Having a database open to the internet when it doesn't need to be just wouldn't make sense from a security perspective. As a result, however, the first step in our process is to make some adjustments to our MySQL config file.

To allow remote access to MySQL, you need to open your mysqld.cnf file and change the bind-address to a non-local IP. Use the following to do so:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

You'll be looking for a line towards the top of the file that looks something like this:

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
tmpdir = /tmp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1   <---- This one
mysqlx-bind-address = 127.0.0.1

Either change this to the IP of the client you want to connect from or temporarily set it to 0.0.0.0 and change it to the correct IP once everything works.

Press Ctrl + O to save the changes, followed by Ctrl + X to exit nano.

Creating a remote database user

The second step in this process is creating a new remote MySQL user that can connect from their specified IP address. You'll need your MySQL root username and password handy to achieve this.

Log into your MySQL root user with:

mysql -u root -p

Once you're in, create a user in the usual fashion, but replace the string after "@" with the IP address of the server/client you want to connect from:

CREATE USER 'remoteuser'@'remote.server.ip' IDENTIFIED BY 'password';

Your user will, of course, need the privileges to manage your database. The command below will allow the user to do almost everything, so make sure to adjust them to suit your needs:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'remoteuser'@'remote.server.ip' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Now type exit and restart MySQL to apply the config changes:

sudo systemctl restart mysql

Allowing MySQL connections through the firewall

The final step is to configure your firewall to allow traffic from your specified IP address through MySQL's default port:

sudo ufw allow from remote.ip.address to any port 3306

Naturally, you'll have to modify this if the IP address of the machine you're connecting to changes or add additional IPs if you want to allow other connections.

We do not recommend using a command such as sudo ufw allow 3306 as this will let anyone access your database.

How to connect to your remote MySQL database

With that done, log on to the client/remote server you want to log into your database from and install the MySQL client if you haven't already:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-client

Run the following command to connect:

mysql -u remoteuser -h your.database.server.ip -p

You should swap remoteuser for the MySQL user you set up earlier, and you should naturally replace  your.database.server.ip. with the IP address of your database server.

If you're on Windows, use the MySQL installer and untick the MySQL server components under Custom > Select Products and Features > MySQL Servers". You just want the MySQL Shell and Workbench. After installation, you can run the same command as above to connect.

If you set your bind address to bind-address = 0.0.0.0, go back to your MySQL config on your database server and change it to the IP address of your client. You'll then want to run sudo systemctl restart mysql to apply the changes and try connecting again.

Securing your remote database

Now that you can connect to your database remotely, you should take additional steps to secure it. First up is forcing SSL-encrypted connections to prevent packet sniffing.

First, go into your config:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line after your bind address:

require_secure_transport = on

And save, then exit. Restart MySQL to apply the changes:

sudo systemctl restart mysql

You can now check your MySQL libraries for keys, which should have been generated automatically.  You can check using:

cd /var/lib/mysql
ls

Transfer ca.pem to your remote client server using a secure method such as SFTP, SCP, etc.

On your client server, you can now point your MySQL config to the key. The config is typically at: 

sudo nano ~/.my.cnf

Add the following to it:

ssl-ca=/path/to/ca.pem

You can verify that the connection is over SSL by connecting to your remote database and then running status. In the SSL line, you should see something like this:

SSL: Cipher in use is TLS_AES_256_GCM_SHA384

You should now be able to successfully connect to your database server over SSL, making your traffic more difficult to intercept.