Debugging MySQL SSL problems

This is not necessarily going to be a comprehensive post, but I learned somethings about MySQL SSL today that I thought would be worth sharing.

I was setting up a PRM install for a customer and one of the requirements was SSL replication.  In this particular case, I had setup PRM first, and then was working to get the other requirements configured.  I knew from experience that it was best to ensure SSL was working properly from the command line first before trying to get replication to use it via PRM’s automation that does the CHANGE MASTER for you.  Eliminate the variables.

The customer provided me with the CA cert, the private key, and the server key, and this was already working on an existing environment with the same MySQL version.  I had already added the relevant config options in the ‘mysqld’ section of the my.cnf:

Now, maybe the rest of the world finds it easy to understand the difference between the server and client keys and certs, and if you need all these options on the client side to connect with SSL to mysql, but I’ve always found it confusing.  From what I can tell, the client-side key and cert is really only necessary if you need the server to authenticate the client.  If you just need raw encryption without that validation, it’s enough to just give the client the CA cert:

However in my case, I immediately got the dreaded:

As is normally the case when one encounters this error, the following steps were taken (in no particular order, or many times in some cases):

  • Checking the mysqld error log (clean as a whistle!)
  • Trying to find an option for the mysql cli that will output the actual SSL error (nary to be found!)
  • Running: SHOW VARIABLES LIKE ‘%ssl%’ (mysql was adamant that had openssl and ssl, and it had my SSL files correctly)
  • Checking grants
  • Double-checking the my.cnf for misspellings
  • Restarting mysql
  • Checking file perms of the ssl files
  • Confirming that SSL worked fine on the old environment
  • Contemplating a nice career in baking

I went through a variety of theories before I found the right one.  They included:

  • Something was just wrong with the SSL files
  • This env was a major OS version newer than the old, but we were using the same MySQL packages built for the old OS, so maybe an openssl compatibility error
  • This just wasn’t my day
  • Maybe it was the chroot requirement

“Aha!”, you say.  chroot, you fool, of course that’s the problem! And indeed it was, but I’m getting ahead of myself.  How did I make such a revelation?  Well, the openssl cli lets to setup a simple SSL client and server, which turns out to be a great way to verify your SSL environment and keys/certs are working properly.  The basic test goes like this:

Start up a basic HTTP-ish server:

Now run a client against that server:

If all is working, then your client should output a copy of the server certificate and generally not give any errors.  If there are errors, it’s helpful to look at what the server outputs.

In my case, this worked fine.  However, once I started suspecting the chroot environment, I was able to quickly start a basic MySQL server outside the chroot and confirm SSL worked fine there.  MySQL worked fine outside the chroot, and so did the basic openssl server, so how do I test the chroot environment?  By running the openssl s_server in the chroot, of course:

I can run the client outside the root because I’m connecting over TCP, though there might be some cases where it’s helpful to also run that in the root.  However, in my case the openssl s_server quickly and efficiently told me everything that was wrong.  This included (as the Ops geniuses out there have already been screaming):
  • a big pile of libraries missing from the chroot
  • the absence of /etc/pki/tls/openssl.cnf (though I’m not convinced that’s necessary)
  • that I needed some more “randomness”, which quickly led me to needing to setup /dev/random and /dev/urandom in the chroot.

Go figure!  After realizing the big gap in my script to build the chroot directories, I finally got what I had been looking for:

After this, it was trivial to get replication fully working with SSL.  Now that I’ve been through it once, it should be easy to debug in the future.

Share this post

Comments (8)

  • Stefan Lasiewski Reply

    Why is it necessary to set up an HTTP-ish server? Why can you not simply use openssl s_client -connect to connect directly to the MySQL server itself?

    July 16, 2013 at 3:20 pm
  • Jay Janssen Reply

    @Stefan: Of course you can, but I started with the bare basics: 1 client, 1 (known) server with the key/cert/ca for troubleshooting. As I stated in the blog: “it’s helpful to look at what the server outputs” — in this case MySQL was not generating any errors. Once I proved that the openssl server had no issues, I knew the problem was in MySQL.

    July 17, 2013 at 8:21 am
  • Stefan Lasiewski Reply

    Thanks for the input. The reason I ask is that I am unable to use OpenSSL to connect to a MySQL server which is configured for SSL. The error SSL23_GET_SERVER_HELLO:unknown protocol almost looks as if my openssl client is speaking ssl to a plaintext port, hence the ‘unknown protocol’ error:

    [root@webserver ~]# openssl s_client -connect 0>/dev/null
    15706:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:588:

    In reading , it almost looks like MySQL starts off with a plaintext connection, and then the actual SSL is initiated afterwards.

    I asked a question on ServerFault, but no luck so far:

    July 17, 2013 at 1:27 pm
  • Jay Janssen Reply

    Now that I think about it, I think you’re right.

    MySQL (unlike Apache or other webservers handling http and https) listens on 1 port: 3306, which is for both encrypted and unencrypted connections. The server can’t speak exclusively ssl on 3306 without confusing the non-SSL clients. The establishment of SSL takes place after the initial connection and handshake is made, but (I assume) before password authentication.

    July 17, 2013 at 1:36 pm
  • Gheeryan Reply

    Doesn’t not work …

    August 4, 2014 at 5:30 am
  • Shaun McPeck Reply

    Another thing to add to the list of stuff to check… The smoking gun for me was making sure I had the “ssl-cipher” setting properly set in my.cnf

    October 4, 2015 at 10:46 am
  • Ales Bobek Reply

    For me was problem in generating certificates, make sure that server/client cert/key Common Name differs each other, see Important clause

    February 25, 2016 at 8:42 am
  • Rusty Reply

    Thanks! It works for me. However, server cert validation (mysql –ssl-verify-server-cert) is failing with “ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure”. Any idea why?

    August 7, 2016 at 11:00 am

Leave a Reply