Hosting Websites on Bare Minimum VPS/Dedicated Servers

Getting started with MySQL over SSL

Tags: Date/Time: June 22, 2014 @ 1:00 am, by Maarten Kossen

lowendtutorial

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

14 Comments

  1. David:

    Awesome tutorial. Thanks Maarten.

    June 22, 2014 @ 10:40 am | Reply
    • Maarten Kossen:

      Thank you, sir!

      June 23, 2014 @ 5:26 am | Reply
  2. David:

    thanks from yet another David. :-)
    Indeed a very useful tutorial.

    I am using MySQL over SSL since a few years. Compared to classical VPN tunnels it was more robust and less error-prone and you have one layer less to troubleshoot. The security is comparable – if done right. The approach by using an own private CA as described by Maarten is the best you can do in my opinion. Just take care to keep the CA (private key) safe, i.e. not on a public server.

    I also added the following parameter to my MySQL config:
    ssl-cipher = DHE-RSA-AES256-SHA
    For the paranoid guys this ensures AES256 and SHA1 together with DHE (forwards secrecy).
    The exact ciphers you can actually use depend on your MySQL and OpenSSL version. This works for me on Debian Wheezy so it will probably work on a much newer Ubuntu 14.04 as well.

    When granting the privileges to a user (and you use proper client/server ssl certificates) you can also use “REQUIRE X509” instead of “REQUIRE SSL”. Then the server does not only rely on the password but also requires the connecting client to present a valid certificate issued from a trusted CA. This means that finally only your own mysql server (and not everyone else on the internet) can connect – and maybe brute-force the password. Just another approach you can do instead of restricting the user access to certain IP addresses (which is fine as well).

    Last but not least, another amazing thing you can do with MySQL over SSL without VPN: Replication.
    This way you can easily create MySQL HA setups securly over the internet without having to establish a VPN. Even Multi-Master setups with more than 2 servers in ring architectures are possible (though they have some limitations).
    Of course this goes beyond the scope of this great tutorial, but just to give you an idea of what you can do if you want to do more. :-) Thanks Maarten.

    June 22, 2014 @ 1:32 pm | Reply
    • Maarten Kossen:

      Thank you for this very useful elaboration!

      I was uncertain whether I had to go into SSL and replication, but I decided not to in order to limit the scope of this tutorial. It may be material for a future tutorial, though :-)

      June 23, 2014 @ 5:28 am | Reply
  3. Steve:

    Thanks for the tutorial! Can you make a similar article on Google Cloud SQL, how to set it up for a CMS maybe, and with some cost calculations in different instances? It would be great to read on this topic.

    June 23, 2014 @ 4:46 am | Reply
  4. Chris:

    Just wanted to pop by and say I for one would be very interested in a tutorial going over SSL certificates and security. Things I’m never sure about include: how secure is a self-signed certificate? How do you make a self-signed one? Good companies to provide a proper certificate? Are the “free” ones actually any good (CACert etc.)?

    Thanks.

    June 23, 2014 @ 9:58 pm | Reply
    • Maarten Kossen:

      Thank you for your suggestion. It’s definitely on my list as, while writing this tutorial, I noticed I had a hard time not elaborating on it.

      June 24, 2014 @ 6:15 am | Reply
  5. raza19:

    Awesome, followed the article, implemented, tested and applied :) thanks !

    But I hope you will continue mysql, I am really looking forward to something in the lines of clustered mysql. Would be fantastic if you did.

    June 26, 2014 @ 3:22 pm | Reply
  6. Peter mao:

    Why not stunnel?

    June 29, 2014 @ 3:42 am | Reply
  7. Awesome, thank you so much sir. Tested and implemented without problem.

    May 22, 2015 @ 3:46 pm | Reply
  8. BB:

    @David:
    As I understand your writing about “REQUIRE X509” “REQUIRE SSL”.
    You imply that using “REQUIRE SSL” with a non-trusted CA (own made certificate) will make the server vulnerable for brute force.
    How’s that? I thought an attacker would not be able to logon to the server without using the right certificate and therefore not be able to use brute force (you cannot not brute-force a certificate, right)
    Or have I misunderstood something?

    April 15, 2016 @ 9:59 am | Reply
  9. Kun:

    Excuse me, how can you guys said ‘Awesome’?? The last part of this post is obviously wrong. You need to indicate where are the client-key.pem and client-cert.pem along with ca-cert.pem on client side. And also some important details missing and I suggest you practice a little bit to figure out what is the correct way. But still except for the last part, the other information is right. Thanks for it.

    Regards,
    Kun

    June 16, 2016 @ 2:37 am | Reply
  10. Ben:

    Thanks for this – very useful.

    I have scripted a number of these commands using a Docker OpenSSL container and the standard MariaDB container:
    https://github.com/bendalby82/mariassl

    I was not able to verify usage of my.cnf on either server or client side, so the scripts show command line options.

    Best wishes Ben

    December 21, 2016 @ 5:14 pm | Reply
  11. Adria S:

    Is there a type-o in your guide? It says to copy the following files to the client machine,

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

    But then it says to configure my.cnf with:

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

    February 8, 2017 @ 8:09 pm | Reply

Leave a Reply

Some notes on commenting on LowEndBox:

  • Do not use LowEndBox for support issues. Go to your hosting provider and issue a ticket there. Coming here saying "my VPS is down, what do I do?!" will only have your comments removed.
  • Akismet is used for spam detection. Quoting webhostingtalk.com URL seems to get binned consistently here, but I do peek into the spam box frequently to publish those comments.
  • Use <pre>...</pre> to quote the output from your terminal/console, or consider using a pastebin service.

Your email address will not be published. Required fields are marked *