MySQL MariaDB

MySQL over TLS on Ubuntu 18.04

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.

$ sudo apt update
$ sudo apt install mysql-server
$ sudo mysql_secure_installation

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.

Press y|Y for Yes, any other key for No: n

Please set the password for root here.

New password:

Re-enter new 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:

$ sudo mysql
mysql> SHOW VARIABLE LIKE%ssl%
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

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.

mysql> exit

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.

...
[mysqld]
require_secure_transport = ON
bind-address = <StaticIP>

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.

$ sudo service mysql restart

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.

$sudo mysql
mysql> CREATE DATABASE myDatabase;
mysql> CREATE USER 'myUser'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
mysql> GRANT ALL ON myDatabase.* TO 'myUser'@'%';

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:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper
 
Connection id:          5
Current database:
Current user:           [email protected]
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
...

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.

mysql> exit

Now run,

$ sudo mysql_ssl_rsa_setup --uid=mysql
$ sudo service mysql restart

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

$ sudo mysql
mysql> SHOW VARIABLES LIKE '%ssl%';

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

| Variable_name | Value           |

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

| have_openssl  | YES             |

| have_ssl      | YES             |

| ssl_ca        | ca.pem          |

| ssl_capath    |                 |

| ssl_cert      | server-cert.pem |

| ssl_cipher    |                 |

| ssl_crl       |                 |

| ssl_crlpath   |                 |

| ssl_key       | server-key.pem  |

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

9 rows in set (0.01 sec)

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:

$ sudo apt install mysql-client
$ mysql -u myUser -p -h <MySQLServerIP>

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:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper
 
Connection id:          5
Current database:
Current user:           [email protected]
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:       10
Connection:             <MySQLServerIP> via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 13 min 52 sec

Threads: 2  Questions: 32  Slow queries: 0  Opens: 107  Flush tables: 1
Open tables: 100  Queries per second avg: 0.038
--------------

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.

About the author

Ranvir Singh

Ranvir Singh

I am a tech and science writer with quite a diverse range of interests. A strong believer of the Unix philosophy. Few of the things I am passionate about include system administration, computer hardware and physics.