KfWiki: Enable SSL for MySQL

Enable SSL for MySQL

Make sure your MySQL has ssl support. To check,

SHOW VARIABLES LIKE '%ssl%';

If have_ssl or have_openssl says NO, you don't have ssl support. If it says DISABLED, you have ssl support. On freebsd, you can recompile MySQL with ssl support via ports. Simply go to the port directory and do make WITH_OPENSSL=YES install clean

From MySQL's doc, one should check ssl support with the command. The following tells you ssl support is absent.

shell> mysqld --ssl --help
060525 14:18:52 [ERROR] mysqld: unknown option '--ssl'

Creating the certificates

Go to /var/db/mysql, generate a CA certificate

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

Next, create a server certificate

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

Next, create a client certificate

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

Now edit your my.cnf and add ssl configirations

my.cnf

[client]

ssl-ca=/var/db/mysql/ca-cert.pem

ssl-cert=/var/db/mysql/client-cert.pem

ssl-key=/var/db/mysql/client-key.pem

[mysqld]

ssl-ca=/var/db/mysql/ca-cert.pem

ssl-cert=/var/db/mysql/server-cert.pem

ssl-key=/var/db/mysql/server-key.pem

You probably want to put the client configurations on your client machine, and vice versa. When a connection is established, you can check your connection by

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

Connection id:          8
Current database:
Current user:           root@192.168.18.198
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.37
Protocol version:       10
Connection:             192.168.18.192 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 53 min 36 sec

Threads: 2  Questions: 8  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6  Queries per second avg: 0.002
--------------

mysql> show status like 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA | 
+---------------+--------------------+
1 row in set (0.01 sec)

There you have an SSL-enabled MySQL connection.

KfWiki : MysqlSSL

Enable SSL for MySQL

Creating the certificates