26

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

4
  • 2
    Check out this awesome thread.. Perhaps it could help you.
    – SirCharlo
    Sep 28, 2012 at 15:01
  • Thanks for the link SirCharlo, but the issue addressed in that thread does not seem to apply to my situation. Oct 1, 2012 at 13:46
  • 1
    What @SirCharlo mentions is where you want to look. Specifically the Apparmor part and post #10. Keys I've generated on Debian Squeeze or an older Ubuntu work fine - regenerating in 12.04 makes it fail again. See the MySQL error logs as well.
    – gertvdijk
    Feb 5, 2013 at 10:51
  • 2
    I had this same question and spent hours on it, but @user262116's answer solved it. I'd encourage you to accept that answer if it helped you!
    – elixenide
    Apr 24, 2015 at 17:00

7 Answers 7

34

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
7
  • 1
    This post saved the day for me! Fantastic explanation and solution. Apr 28, 2014 at 2:30
  • I got 'ERROR 2026 (HY000): SSL connection error: ASN: before date in the future' Jun 5, 2014 at 11:40
  • Unfortunately, I still got the ERROR 2026 (HY000): SSL connection error: protocol version mismatch errors (using OpenSSL 1.0.1f). I switched to OpenSSL 1.0.1e and it worked with the above instructions.
    – Jarrett
    Mar 12, 2015 at 22:38
  • 2
    This answer is amazing - would award a massive bounty if I could. Great explanation of the problem.
    – elixenide
    Apr 24, 2015 at 16:59
  • Another voice to add to the horde. Have been trawling the Internet trying to find a solution to why the MySQL documented set up just wouldn't work - the bottom part of this post completely saved my day. Oct 13, 2015 at 15:27
4

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.

2
  • I had a similar problem in my Ubunbtu 12.04 with mysql 5.5.34, where the pem files all had readable by all and still gave me the same problem. But this answer combined with changing the owner did the trick. Nov 16, 2013 at 13:38
  • Don't manually add "RSA" - this will only suppress the error, but SSL won't work (you will get another error, "SSL connection error: protocol version mismatch"). Convert from PKCS#8 to PKCS#1 format instead using openssl rsa
    – rustyx
    Aug 7, 2016 at 8:47
3

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.

2
  • This was the problem I had as well
    – Jonathan
    Jun 7, 2016 at 20:55
  • is that a typo? do you mean "moving pem files to" instead of "moving pem files in"? sorry for being so pedant but I got a bit confused
    – knocte
    Jun 14, 2016 at 13:05
1

Make sure that the user running the mysqld process has read access to the keys and certificate files. If you launch MySQL using the account "mysql", you would:

/etc/mysql$ chown mysql:mysql *.pem
/etc/mysql$ ls -l *.pem
-rwxrwx--- 1 mysql mysql 1631 2013-09-16 14:27 ca-cert.pem
-rwxrwx--- 1 mysql mysql 1281 2013-09-16 14:27 server-cert.pem
-rwxrwx--- 1 mysql mysql 1679 2013-09-16 14:27 server-key.pem

Otherwise, you might get the following in your error log:

SSL error: Unable to get certificate from '/etc/mysql/server-cert.pem'
130916 13:32:25 [Warning] Failed to setup SSL
130916 13:32:25 [Warning] SSL error: Unable to get certificate
2
  • Great. But how could this be done? Take a time and explain further, giving more information :)
    – Lucio
    Sep 17, 2013 at 22:29
  • This combined with adding RSA to the server-key.pem file did the trick. Nov 16, 2013 at 13:39
1

On Ubuntu 16.04, I ran mysql_ssl_rsa_setup, could see the files in show variables as in the question, but have_ssl and have_openssl continued to be DISABLED.

The solution was to chown mysql.mysql /var/lib/mysql/*.pem. Alternatively, I assume if you run mysql_ssl_rsa_setup as the mysql user, it will create the files with the correct permissions.

3
  • The question is four years and two months old... It's unlikely the OP will respond to your answer. Dec 13, 2016 at 1:16
  • 1
    I posted this simply as a service for future readers because this is one of the top google search results for this problem and nothing I read helped solve my problem. The value of this answer referring to the state of the world in Ubuntu 12.04 and even 14.04 is losing relevance. Dec 13, 2016 at 4:10
  • @WinEunuuchs2Unix Maybe not the OP, but here I am, almost two years later looking for exactly this answer. So, thank you Craig!
    – Oldskool
    Sep 28, 2018 at 14:55
0

The private key file should look like (PKCS#1 format):

-----BEGIN RSA PRIVATE KEY-----
. . .
-----END RSA PRIVATE KEY-----

If your private key begins instead with:

-----BEGIN PRIVATE KEY-----

(PKCS#8 format), then you should convert it like this:

openssl rsa -in server-key.pem -out server-key.pem

Don't manually add the "missing" "RSA" tag by hand, because the format is different.

-1

The SSL startup options should be near the top of your my.cnf file or they might be ignored. I had problems running mysql 5.6 on RHEL 6.4 where the SSL variables were being ignored, I had them at the end of the my.cnf file. I moved them at the top of the file (just below [mysqld]) then I restarted the server and everything was fine.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .