Q:

Enabling SSL in MySQL

I'm running Ubuntu Server 12.04, and I want to enable SSL connections to MySQL.
I've generated the following keys/certs files with OpenSSL:

ca-cert.pem
server-cert.pem
server-key.pem

I stored these at /etc/mysql, then added added the following lines to /etc/mysql/my.cnf:
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Next, I restarted the server with sudo service restart mysql.
However, this doesn't seem to enable SSL. Within a mysql session:
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    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+

Any ideas what I'm missing? Thanks

A:

Ubuntu 12.04 comes with a OpenSSL 1.0.1, which has somewhat different defaults than the older OpenSSL 0.9.8 version.
Among other things, if you're using openssl req -newkey rsa:2048 to generate an RSA key, you'll end up with a key in a format called PKCS #8.  Represented in the PEM format, these keys have the more generic -----BEGIN PRIVATE KEY----- header, which doesn't tell you what kind (RSA, DSA, EC) key it is.
Previously, with OpenSSL 0.9.8, keys were always in a format called PKCS #1, which represented as PEM, had the header -----BEGIN RSA PRIVATE KEY-----.
Because of this you cannot simply change the header and footer from:
-----BEGIN PRIVATE KEY-----

to
-----BEGIN RSA PRIVATE KEY-----`

It's not the same thing and it won't work.  Instead you need to convert the key to the old format using openssl rsa.  Like this:
openssl rsa -in key_in_pkcs1_or_pkcs8.pem -out key_in_pkcs1.pem

MySQL (v5.5.35) on Ubuntu 12.04 is using an SSL implementation called yaSSL (v2.2.2).  It expect keys to be in the PKCS #1 format and doesn't support the PKCS #8 format used by OpenSSL 1.0 and newer.  If you simply change the header and footer, as suggested by other posts in this thread, MySQL/yaSSL won't complain, but you'll be unable to connect and instead end up with an error like this:
ERROR 2026 (HY000): SSL connection error: protocol version mismatch

Ubuntu 14.04 comes with OpenSSL 1.0.1f and new settings.  Among other things, it will generate certificates with SHA256 digests instead of SHA1, which was used in earlier versions.  Incidentially, the yaSSL version bundled with MySQL doesn't support this either.
If you're generating certificates for use with MySQL, remember to make sure the RSA keys are converted to the traditional PKCS #1 PEM format and that certificates are using SHA1 digests.
Here's an example of how to generate your own CA, a server certificate and a client certificate.
# 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 certficate with SHA1 digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -sha1 -req -in server-req.pem -days 730  -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 SHA digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem

A:

This helped me:
The header and footer of the file server-key.pem looked like that:
-----BEGIN PRIVATE KEY-----
...
...
-----END PRIVATE KEY-----

But it requires something like that:
-----BEGIN RSA PRIVATE KEY-----
...
...
-----END RSA PRIVATE KEY-----

Note the BEGIN RSA PRIVATE KEY
In order to see the log:
sudo vim /var/log/mysql/error.log

Hope this helps.

A:

I had the same troubles on 12.04 but it was in fact apparmor that caused the problems.
I found a solution at the Ubuntu Forums, moving .pem files in /etc/mysql resolved it.
You can also change the apparmor configuration in /etc/apparmor.d/usr.sbin.mysqld.

