In last weeks OurSQL postcast (episode 65)  Sheeri,  Sarah and Jerry talked about making MySQL safe with SSL.  Encryption always seems to be such a confusing subject. I think every database should be using SSL by default.  So, I was wondering just how easily SSL could be setup.

Most existing examples I found  setup SSL authentication and encryption.  If you are handling  PCI DSS or HIPAA data you must encrypt the data on the wire, but passwords are all you need to authenticate the application to the data source.

 # mkdir /etc/mysql/certs
 # cd /etc/mysql/certs

This looks complicated, it’s not.  JUST PRESS ENTER when openssl ask you a question.  This will not make you any less secure.  Your data will be encrypted.   Note these keys will expire in three years (1095 days).

 # openssl genrsa 2048 > ca-key.pem
 # openssl req -new -x509 -nodes -days 1095 -key ca-key.pem -out ca-cert.pem
 # openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key.pem -out server-req.pem
 # openssl rsa -in server-key.pem -out server-key.pem
 # openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem \
 -set_serial 01 -out server-cert.pem

With the keys generated you need to tell MySQL to use them. Add these lines to your my.cnf.

 # vi /etc/my.cnf

 ssl
 ssl-cipher=DHE-RSA-AES256-SHA
 ssl-ca=/etc/mysql/certs/ca-cert.pem
 ssl-cert=/etc/mysql/certs/server-cert.pem
 ssl-key=/etc/mysql/certs/server-key.pem

Now restart mysql.

 # service mysql restart

You should see SSL is enabled and mysql sees the keys.

 mysql> show variables like '%ssl%';
 +---------------+----------------------------------+
 | Variable_name | Value                            |
 +---------------+----------------------------------+
 | have_openssl  | YES  |
 | have_ssl      | YES  |
 | ssl_ca        | /etc/mysql/certs/ca-cert.pem     |
 | ssl_capath    |                                  |
 | ssl_cert      | /etc/mysql/certs/server-cert.pem |
 | ssl_cipher    | DHE-RSA-AES256-SHA               |
 | ssl_key       | /etc/mysql/certs/server-key.pem  |
 +---------------+----------------------------------+
 7 rows in set (0.00 sec)

Duplicate these keys and the configuration segment to all your servers.

Client

If the client system is not a server, copy of the MySQL SSL keys to it was well.  Then, you need to tell the client to use SSL.  Edit your user’s .my.cnf file and give it the keys too.

 # vi ~/.my.cnf

[client]
 ssl
 ssl-cipher=DHE-RSA-AES256-SHA
 ssl-ca=/etc/mysql/certs/ca-cert.pem

Thats it.  It should be working.  Look for ‘Cipher in use’.

 # mysql -e "\s"

mysql Ver 14.14 Distrib 5.1.59, for unknown-linux-gnu (x86_64) using readline 5.1

 Connection id: 7
 Current database:
 Current user: root@localhost
 SSL: Cipher in use is DHE-RSA-AES256-SHA
 ......
 Threads: 1 Questions: 22 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 2.0

Replication

On the master, you need to tell the replication user to require SSL connections. Replace the rep_user with your replication user’s ID.

 mysql> GRANT USAGE ON *.* TO 'rep_user'@'%'  REQUIRE SSL;
 mysql> flush privileges;

and on the slave tell it to connect to the master with SSL.  Then make sure you are still connecting. Change the IP and user name, show here,  to your settings.

 mysql> stop slave;
 mysql> CHANGE MASTER TO master_host='192.168.1.12', master_user='rep_user', \
MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem' ;
 mysql> start slave;
 mysql> show slave status;

Applications

Your application could be written in lots of different languages and I can’t go over each of them but here are some links to setup up SSL connection to MySQL in several of the most popular.  If you know where there are some better examples, please leave me a comment.

Python

Perl

JDBC

Tweet