Simplified MySQL SSL connections

November 22, 2011

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

 

 

 

 

posted in Commentary by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "Simplified MySQL SSL connections"

  1. Jonathan Levin wrote:

    Not to add to the confusion, but I found that REQUIRE SSL allowed you to use any certificate file and when I user REQUIRE X509, it required you to use the actual certificate and key file. Maybe it was just me..

  2. peter_laursen wrote:

    A comment to ” ..you need to tell the client to use SSL. Edit your user’s .my.cnf file and give it the keys too .. /.my.cnf .. [client].

    Not all clients will use the [client] section of the options file. For instance clients connecting from remote hosts cannot. So in such case the client would/should have its own interface to specify SSH-parameters.

  3. Simplified MySQL SSL connections « DbRunas – Noticias y Recursos sobre Bases de Datos wrote:

    […] http://www.mysqlfanboy.com/?p=712 November 23, 2011   //   MySQL   //   No Comments   //   […]

  4. Sheeri wrote:

    This is exactly why I love doing podcasts — people take what we do and give us feedback, or run with it and go further.

    It warms my heart to know I helped inspire you! I think I’m a fangirl of MySQL Fanboy!

  5. Do I need client certs for mysql ssl replication? | Question and Answer wrote:

    […] first one creates both client and server certs, while the second one only creates server […]

  6. Mark Grennan wrote:

    To keep you data secure you only need Server certs. Client certs are to validate your clients. If you trust who is connecting to your servers (ID and Passwords are enough) you don’t need client certs.

  7. MySQL PAM and Active Directory authentication wrote:

    […] very well designed a secure environment to access your database wherever be the location, a VPN or SSL is always a requirement to go forward to the next step in this article as passwords are sent in […]

  8. Linksammlung | Eigener Server in 24h wrote:

    […] http://www.mysqlfanboy.com/2011/11/simplified-mysql-ssl-connections/ […]

Leave Your Comment

You must be logged in to post a comment.

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.