Traditionally, your database server and your frontend used to be on the same isolated network. This allowed frontend to talk to the database over an unencrypted channel without much concern about security. All of this has changed over the last few year with the rise of cloud and distributed systems. Your apps are no longer constrained on a single isolated network. Now, more than ever, the communication between the frontend and the database needs to be encrypted and secured.You could achieve this by either using a VPN to virtualize an isolated network. The frontend and the database can be a part of this VPN and the communication between them will be secured. Or you can use TLS, to encrypt the data being sent to and from the database, much same way websites secure their communication with the browsers using HTTPS. We will be installing MySQL and setting it up such that queries and data flow via TLS.

Installing MySQL Server

The guide is assuming that you have a server set aside for MySQL use with an accessible static IP address, maybe on the cloud or somewhere on your local network. The following commands, in this subsection, are to be executed on the server’s shell. Let’s quickly install and setup MySQL on Ubuntu.

The last command will run a script to change some of the insecure defaults of MySQL. First would be a prompt to install a password validation plugin. This would check if the new password you are setting for users is strong enough or not. You can opt out of this plugin, if you want. After this you will be prompted to set MySQL root user password. Go ahead and set a strong root user password.

After this you can pretty much say yes to every other prompt in this script, as the script removes test user, removes test database, disables remote root login and finally reloads its privilege table. Once that is done, since we have disallowed remote root login, let’s create a database and a new user that can access that database remotely without actually having to SSH (or login) into the server’s UNIX/Linux shell. But before we do that, let’s verify if our build of MySQL has TLS built-in or not.

Checking if TLS is available

TLS is available in MySQL only if MySQL is compiled to have it built into it. There’s no dynamic module to load. So if you are unsure that your MySQL package has TLS installed or not, you can check that by running:

If it says that the variables have_openssl and have_ssl have values set to DISABLED then you have SSL and are good to go (you only need to enable it, by reading further). If the values are set to NO, then you have to get a different version of MySQL from your package manager or elsewhere.

Configuring MySQL

By default mysql server listens only on the loopback interface, that is to say on the address ‘localhost’ or ‘127.0.0.1’, for remote connections we want it to listen on the public static IP too. To do this open the file, /etc/mysql/my.cnf and append the following couple of lines at the very end of it.

Here, you replace the <StaticIP> with the actual IP of your server. If you are in doubt about which IP to use, you can use 0.0.0.0 to listen on all interfaces. Now restart the server, for the new configuration to take place.

Creating Remote User

Note: If you want to use the database in production, chances are that the client that will connect to this database – your front-end – will have a static IP. If that is the case, replace the percentage ‘%’ symbol with the appropriate client IP. ‘%’ is just a wildcard, which means ‘any value’. We will be configuring our myUser so that it can login from any IP address (for example, the changing IP address of your domestic broadband connection) which is, arguably, insecure.

Replace ‘password’ with an actual strong password and we have a user named myUser which has complete access to the database myDatabase.

Enabling TLS (also known as ‘SSL’)

While you are logged to the mysql shell as the mysql root user, you can check the connection status by typing \s:

Pay attention to the highlighted lines about Connection and SSL. While this state is fine for a local login of the root user, by the time we login over TLS as the myUser the connection type will be over TCP/IP not a raw socket and an SSL cipher will be in use. There’s a simple command to accomplish this. But first let’s exit our mysql prompt.

Now run,

 
Once this is done, you can look at the have_ssl variable again.
 

Logging in From a Separate MySQL Client

There are new parameters indicating that TLS certificate and key are in place and TLS is enabled. Now you can log out of this machine, open up a MySQL client on your local computer, if you don’t have one (and are using Debian or Ubuntu) get a MySQL shell client:

Replace the myUser and <MySQLServerIP> with your actual username and server IP, enter your chosen password and you should be logged into the database. Check the connection:

You can see that now it is using RSA to encrypt your traffic and the connection is to a specific IP over TCP/IP. Now, your connection to this MySQL database is secure.

Conclusion

This is the simplest way to secure your remote MySQL connections with TLS. Bear in mind that this is not the same as securing a phpMyAdmin client over TLS. That is TLS and HTTP combined, and requires for you to secure the web interface. The connection between the phpMyAdmin, which renders your web UI, and the database might still be unencrypted which is fine as long as they are on the same server.

You can learn more about the TLS connection, underlying CAs, certificates and key management in the official docs of MySQL.