When you want to reach a database server with a client that is not on that server, you usually set up a Virtual Private Network (VPN) in order to access the server securely without having to open it up to the internet as a whole. In certain situations, though, this is not an option; you may not have a VPN, you may not know how to set it up or consider it too complicated, or the client or server do not support it.

For these situations MySQL offers SSL support. This ensures your database is accessed securely even though you’re doing it over a public line. What’s more: you don’t even have to open up your full database server for this. You can require users to use SSL and only allow users to connect from certain IPs and/or with SSL.

In this tutorial, I’m going to show you how to set up SSL for MySQL. I’m using Ubuntu 14.04’s default MySQL, but MariaDB will also work. I have no reason to believe this shouldn’t work on other Linux distributions as well, as long as the proper server version is installed.

Setting up the server

First of all, install the MySQL server:

sudo apt-get install mysql-server

During the installation you will be asked for a root password several times. Pick a strong one and enter it the first time. Every additional time you are asked for it, you can just hit enter. It keeps the old password if you leave the field blank.

In order to have a proper setup, you need to generate a s0-called ‘Certificate Authority’ certificate first. This certificate is used to create both the server and the client certificates. When the client establishes a connection to the server, the server checks the client certificate against the CA certificate for validity. If it matches, the client is considered trusted and a secure connection is established. I could go into more detail about that, but that is not the goal of this tutorial. Maybe for another day.

Anyway, lets generate the CA certificate and private key. On the server, go to /etc/mysql and type the following command:

openssl genrsa 2048 > ca-key.pem

This generates a new CA private key. Next, generate the certificate using that key:

openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

You will be asked a series of questions. It doesn’t really matter what you put in here. Once done, you will have a CA key and certificate and we can now create the other certificates.

Let’s create a private key for the server and a signing request to go with that:

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem

Fill out all the fields again. Just like before, it doesn’t really matter what you put in there. Be sure to leave ‘A challenge password’ empty. It’s going to be removed in the next step anyway.

Now, export the private key into an RSA private key:

openssl rsa -in server-key.pem -out server-key.pem

And finally, create a certificate using the CA certificate:

openssl x509 -sha1 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

As you can see, we use the ca-cert.pem and ca-key.pem files to actually create a certificate based on the certificate request, which was generated for a certain private key. Like I said before, I’ll dive into the workings of SSL in another tutorial.

Now that we’ve got the CA certificate, the server private key, and the server certificate, let’s configure MySQL to use them. Open up /etc/mysql/my.cnf and put the following lines in the [mysqld] section:

ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

These files are now loaded when MySQL starts and it enables SSL, which in turn makes MySQL accept SSL connections. While we’re here, change the following line:

bind-address = 127.0.0.1

To:

bind-address = *

This ensures MySQL listens on all addresses, not just on localhost. To apply these changes, restart MySQL:

sudo service mysql restart

And the MySQL server should now be ready to accept SSL connections.

The final step is to add a user that is allowed to connect to this server. We are going to require the user to use SSL, so the user cannot connect without SSL. Log in to MySQL

mysql -u root -p

And create the user with the following command:

GRANT ALL PRIVILEGES ON *.* TO ‘iamsecure’@’%’ IDENTIFIED BY ‘dingdingding’ REQUIRE SSL;

Obviously, it is good to pick a better password than I have. You may also want to limit the database a user is able to connect to or from which host a user is allowed to connect. For example, this limits the user to the database ‘collections’ and only allows him to connect from 192.0.2.10:

GRANT ALL PRIVILEGES ON ‘collections’.* TO ‘iamsecure’@’192.0.2.10’ IDENTIFIED BY ‘dingdingding’ REQUIRE SSL;

Now flush the privilege cache to ensure the user you just added can connect:

FLUSH PRIVILEGES;

That’s it! We’re now ready to set up the client.

Setting up the client

We still need to be on the MySQL server in order to set up the client, though. A private key and certificate for the client need to be generated on the server, as that is where the CA certificate and private key reside. Just like before, we start with a private key and a certificate signing request:

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem

Next, we export the private key to an RSA private key:

openssl rsa -in client-key.pem -out client-key.pem

And finally, we create a certificate using the CA private key and certificate:

openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Now we need to copy the following three files to the client:

• ca-cert.pem
• client-key.pem
• client-cert.pem

The easiest way is to use ‘scp’ to do this, but you may also open two terminals and copy-paste the contents of the files across.

When that has been done, we need to install the MySQL client on the client server:

sudo apt-get install mysql-client

Next, open up /etc/mysql/my.cnf and add the following lines under the [client] section:

ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Which is all there should be to it. When you now use the ‘mysql’ command to make a connection to a server, it will automatically try to establish and SSL connection and use these files to do so.

To see if all we did works, try and connect to your server:

mysql -h [SERVER HOSTNAME/IP] -u iamsecure -p

It should ask you for the password, which is ‘dingdingding’ in my example but I hope you’ve picked a better one. If the connection is successful, you should be connected to MySQL. That’s all there’s to it!

Final notes

While understanding the concepts on SSL may be hard at first, they’re not really relevant for using it. Setting up SSL for MySQL is actually a quite simple and straight-forward process. In times where privacy is a more and more important topic, securing your connections between your servers is a great idea.

Up next time: Linux Basics: what do you on your first ever VPS

• Author
• Recent Posts

mpkossen

Maarten Kossen was the administrator of LowEndBox from 2013 to 2015, and brought many ideas and improvements to the website during his leadership. Today he is member of our community and LowEndTalk.

Latest posts by mpkossen (see all)

• How to Rapidly Install Java, OpenJDK & Oracle JDK on your VPS - December 14, 2015
• It’s been a great ride - December 14, 2015
• Cheap Windows VPS – $21/quarter 1GB KVM-based Windows VPS in 11 worldwide locations - November 30, 2015