How to enable MySQL SSL on Ubuntu

Owned by Former user (Deleted)
Last updated: Apr 11, 2011 by UdaiU (Unlicensed)Version comment
3 min read

Test SSL support

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

Disabled means mysql has ssl support but it's just not enabled (if you have 'NO' instead of 'DISABLED' then you don't have ssl support)

mysql> \s
...
SSL:        Not in use
...

mysql --ssl --help (you will get ERROR)

Generate keys

I am using /etc/mysql for cert storage because /etc/apparmor.d/usr.sbin.mysqld contains /etc/mysql/*.pem r.

sudo su -
cd /etc/mysql
openssl genrsa -out ca-key.pem 2048;
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem;
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem;
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem;
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem -out client-req.pem;
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem;

Add configuration

/etc/mysql/my.cnf

[client]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

[mysqld]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Restart MySQL

sudo service mysql restart

Test SSL support again

mysql -u root -p
mysql> show variables like "%ssl%";

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+

mysql> \s
...
SSL:          Cipher in use is DHE-RSA-AES256-SHA
...

mysql --ssl --help (help working on ssl)

JDBC with MySQL on SSL

Add this to configure Mifos database properties (local.properties) under Mifos Configuration Locations

main.database.params=useUnicode=true&characterEncoding=UTF-8&useSSL=true&requireSSL=true

NOTE: JDK has issue with validating SSL certs, so you may want to add another parameter which will disable validation using verifyServerCertificate=false

To strictly force SSL for a user to connect to mysql use "REQUIRE SSL" with GRANT statement.

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-using-ssl.html

Reference