I decided to make the move to MySQL over SSL when I moved to dedicated MySQL database servers rather than all-in-one web/database servers for the production websites and databases that I host with Digital Ocean. I wanted an extra layer of security since MySQL traffic would no longer be contained within localhost.

Long story short, none of the tutorials I could find on this seemingly simple topic appeared to work. I mentioned the idea to my friend Josh Haber who also thought it was a good idea to strengthen security between web and database servers, but he too initially had problems getting everything to work. After a lot of digging through obscure posts online, he found that part of our problem was AppArmor, Ubuntu’s Mandatory Access Control system. To paraphrase Ubuntu’s wiki article, AppArmor’s purpose is “to confine programs to a limited set of resources”. You see, most tutorials suggested to store certificates in /etc/mysql/, however we were trying to store them in /etc/mysql/ssl/, a much cleaner solution in my opinion.

Without further delay, here are step-by-step instructions for configuring MySQL to use SSL on Ubuntu 14.04 LTS:

mkdir /etc/mysql/ssl && cd /etc/mysql/ssl

Generate a CA key and certificate with SHA1 digest

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

Create server key and certificate with SHA1 digest, sign it and convert

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -sha1 -req -in server-req.pem -days 3650  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl rsa -in server-key.pem -out server-key.pem

Create client key and certificate with SHA1 digest, sign it and convert

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem

Set permissions

chown mysql:mysql /etc/mysql/ssl/* && chmod ug=wrx /etc/mysql/ssl/*

Configure MySQL

nano /etc/mysql/my.cnf
Add the following lines under the [client] section
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
Add the following lines under the [mysqld] section
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Configure AppArmor

nano /etc/apparmor.d/user.sbin.mysqld

Change  /etc/mysql/*.pem r to /etc/mysql/ssl/*.pem r

service mysql restart

Once you’ve completed these steps, you should be able to run show variables like “%ssl%”; from your MySQL instance and get the following:

mysql> show variables like "%ssl%";
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /etc/mysql/ssl/ca-cert.pem     |
| ssl_capath    |                                |
| ssl_cert      | /etc/mysql/ssl/server-cert.pem |
| ssl_cipher    |                                |
| ssl_key       | /etc/mysql/ssl/server-key.pem  |
+---------------+--------------------------------+

You can then configure your database users to require SSL.

Bonus Tip

If you’re configuring MySQL over SSL for WordPress, all you’ll need to do is add the following line in your wp-config.php file:

define('MYSQL_CLIENT_FLAGS', MYSQL_CLIENT_SSL);

As I mentioned above, I’ve recently moved all production servers to Digital Ocean. Use this link to signup and get a $10 credit.

Category:
Technology
Tags:
, , , , ,