Using SSL with MySQL

If you ever make remote connections to a MySQL database, it’s extremely important to connect over SSL. Otherwise, you run the risk of someone seeing data in transit, modifying data in transit, or, worst of all, stealing your credentials. This could be an utter disaster.

Unfortunately, getting MySQL to use SSL is kind of complicated. Here’s how to do it.

Make Sure SSL is Enabled

First, make sure your installation of MySQL supports SSL. Using PHPMyAdmin, a MySQL command-line prompt, or whatever you prefer, run this command:

You should get a result looking something like this:

If you see “DISABLED” next to “have_openssl” and “have_ssl”, great – that means that your copy of MySQL supports SSL. If you see “NO”, you need to recompile or reinstall MySQL. If you see “YES”, you’re already done!

Create SSL Certificates

Now, you need to create some SSL certificates. Go to wherever you want to store them (I’d suggest something like /home/mysql/certs) and run the following commands:

Enter information as needed at the prompts. This will create self-signed client and server keys for you to use. The last two lines are very important! OpenSSL versions 1.0 and newer create keys in the PKCS #8 format, but MySQL expects them to be in the PKCS #1 format, so you have to run these commands to convert the keys we just created. You can read more about that problem in this Ask Ubuntu question: Enabling SSL in MySQL.

Tell MySQL to Use the Certificates

Edit your my.cnf file (probably at /etc/my.cnf) to include the following entries:

(If you used a directory other than /home/mysql/certs above, use it in the entries here.) If your my.cnf file already has mysqld and/or client sections, just add the appropriate entries to those sections.

Restart MySQL. The command varies by system, but will usually be something like service mysql restart.

Test It

Run this command again:

You should see something like this:

If so, congratulations! You’re done! If not, you might find the post Debugging MySQL SSL Problems on the Percona blog helpful.

Have you had problems with MySQL over SSL? Let me know in the comments! I’ll try to help you out!