Getting started with MySQL over SSL
Posted on 22-06-2014 05:00
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.04r42;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:
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:
This generates a new CA private key. Next, generate the certificate using that key:
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:
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:
And finally, create a certificate using the CA certificate:
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:
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:
This ensures MySQL listens on all addresses, not just on localhost. To apply these changes, restart MySQL:
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
And create the user with the following command:
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:
Now flush the privilege cache to ensure the user you just added can connect:
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:
Next, we export the private key to an RSA private key:
And finally, we create a certificate using the CA private key and certificate:
Now we need to copy the following three files to the client:
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:
Next, open up /etc/mysql/my.cnf and add the following lines under the [client] section:
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:
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!
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
Warning about ParkingCrew.com! Case: ParkingCrew.com acquires NameDrive.com but earnings are not transferred despite assurances and promises. Inquiries about this are ignored! It's just a con compagny. Don't do business with them!
|Jump to Forum
|Subject||Discussion Forum||Last Post|
|MySqlTuner MySql tuning script||MySQL optimalisation Tools||: 1||17-10-2016|
|Getting started with MySQL Master-Slave replication||Linux tutorials, Tips & Tricks||: 1||04-05-2014|
|Getting started with SSH||Linux tutorials, Tips & Tricks||: 1||23-02-2014|
|Tutorial: Getting started with OpenVZ!||Linux tutorials, Tips & Tricks||: 1||04-11-2013|
|Getting started with OpenVPN (client)||Linux tutorials, Tips & Tricks||: 1||11-09-2013|