How to Configure MySQL SSL With Public CertificatesGetting MySQL working with self-signed SSL certificates is pretty simple. Having it working with a certificate signed by a trusted authority is also very simple, we just need to set the correct path and privileges to the file. The problem comes when we need to make MySQL validate the certificate signature against the authority public key.

I’ve searched on the internet but wasn’t able to find much information about it. There are a good number of posts on how to set up your own certificate authority and self-sign your certificates, but not much about how to use one signed by a public trusted authority.

I used a certificate signed by a Let’s Encrypt on my tests but the concepts and steps shared here should work for any public trusted authority. I also generated one certificate to be used by MySQL server and another one to be used by the client. It is possible to use the same certificate on the server and the client, but it defeats the security purpose of using certificates.

Let’s Encrypt gives us a package with four files, which are:

  • The <host> public certificate, signed by the CA
  • The <host> private key
  • The CA public certificate
  • A bundle with the signed <host> public certificate and the CA public certificate

The first step is to get MySQL using the certificates. For simplicity I’ve just removed the old certificates from the server <data_dir> (I was using /var/lib/mysql), renamed the certificates that I received from Let’s Encrypt, moved into /var/lib/mysql, and restarted the MySQL service. All good, the server was able to start without issues.

I tried to connect using MySQL client but without using the certificate given by Let’s Encrypt – and that worked flawlessly. The first problem happened when I tried to validate the server certificate on the client:

I wasn’t expecting to get this error because I was using the exactly same CA certificate used on the server, but the validation failed. I thought that it was an issue on my MySQL server and I tested many configuration changes using the CA certificate (chain1.pem) and using the bundle (fullchain1.pem) for example, but none of them worked. I decided then to debug the issue. At first on MySQL server, but I found the issue wasn’t on the server, at least not this very one. I then went to debug the MySQL client and ended up in the function ssl_handshake_loop[1] which calls the function SSL_connect[2] from the OpenSSL library. At this point, I realized the error was coming from the OpenSSL library!

I recompiled MySQL and the OpenSSL library with debug symbols to be able to use client the “–debug“[3] option on my MySQL client to get some debug messages directly from the client and make it easier to debug. Following the execution path through the MySQL client and OpenSSL library, I found that the OpenSSL was trying to validate the whole CA chain of trust but the CA chain key that Let’s Encrypt gave me didn’t have the full chain of trust. As they explain in their website here[5] under normal circumstances, certificates issued by Let’s Encrypt will come from “R3”, an RSA intermediate which is an intermediate CA. At this point, I only had the certificate of the intermediate CA and OpenSSL was refusing to validate the server certificate without having the whole chain. This was the issue!

The solution was pretty simple. Let’s Encrypt is a publicly trusted certificate authority and all browsers and other tools have an up-to-date file with the chain of publicly trusted CA’s. I downloaded the certificate chain from the curl website[4] and used at the client side to make MySQL client connect to the server:

Now that I understood what was the problem I also used this same CA’s chain certificate on the MySQL server side because if we want to use a certificate on client-side it needs to be validated by the server against the CA chain of trust. The error would be similar without using the proper CA’s certificate, for example:

But if we check the error log we can see:

I then used the same CA file:

But it gave me a “certificate verify failed” error. Note that this is not the CA chain validation error anymore but the OpenSSL on the server wasn’t able to validate the certificate:

It was clear that for whatever reason the chain didn’t have CA certificate that Let’s Encrypt used to sign. I then just added it to the chain:

As I said, it works using the same certificate on both MySQL server and client. I tested it to make sure it works, and it did work on my setup, but I would not recommend it because of security issues and because some versions of the SSL libraries do not work well with the same certificates used on both server and client. I would suggest having different certificates for MySQL server and MySQL client. All servers can use the same certificate, it wouldn’t be a big issue. Also, all MySQL clients may share the same certificate. However, remember that sharing certificates on both the server and client-side increases the security risk.

A final note here is that some versions of the library may validate the hostname against the certificate common name (CN). It wasn’t the case here, and I was able to use certificates with a CN different from the server’s hostname.

[1] https://github.com/percona/percona-server/blob/5.7/vio/viossl.c#L343
[2] https://github.com/openssl/openssl/blob/OpenSSL_1_0_2-stable/ssl/ssl_lib.c#L1002
[3] https://dev.mysql.com/doc/refman/5.7/en/debugging-client.html
[4] https://curl.haxx.se/docs/caextract.html
[5] https://letsencrypt.org/certificates/

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
mahesh Nanayakkara

This blog helped me solve my issue. U saved my day! Thanks a lot sir.

0
Charly Batista

Hello Mahesh,

I’m glad to hear that this article was of good help. Thank you for your feedback.

0
Aleksey

Thanks for the post! It helped me to find the solution:

On server side:
[mysqld]
ssl_ca=/etc/letsencrypt/live/myhos/fullchain.pem
ssl_cert=/etc/letsencrypt/live/myhos/cert.pem
ssl_key=/etc/letsencrypt/live/myhos/privkey.pem

On client side:
# Download Let’s Encrypt Root CA:
wget https://letsencrypt.org/certs/isrgrootx1.pem

# Verify server CA:
mysql -u user -p -h myhost –ssl-mode=VERIFY_CA –ssl-ca=isrgrootx1.pem

# Or one can use system-level ca-bundle to verify Let’s Encrypt certificate:
mysql -u user -p -h myhost –ssl-mode=VERIFY_CA –ssl-ca=/etc/ssl/certs/ca-bundle.crt

0
Charly Batista

Hello Aleksey,

Thank you for letting me know the blog helped you. The feedback are what help us find and fix possible errors, mistakes, help us to learn a bit more, and also encourages us to continue writing when we see what we write help others. Thanks once again!

0