Simplified SSL/TLS Setup for MySQL Community

Transport Layer Security (TLS, also often referred to as SSL) is an important component of a secure MySQL deployment, but the complexities of properly generating the necessary key material and configuring the server dissuaded many users from completing this task.  MySQL Server 5.7 simplifies this task for both Enterprise and Community users.  Previous blog posts have detailed the changes supporting Enterprise builds; this blog post will focus on parallel improvements made to MySQL Community builds.

Introducing mysql_ssl_rsa_setup

To enable TLS with MySQL Server, we first need to generate the required key material.  Key material must be unique and secret – it’s not something that MySQL can generate and pre-populate in packages.  If another organization has the server TLS keys for your organization, they can decrypt network traffic for your organization.  That means key material needs to be dynamically created on the deployment host.  In OpenSSL-linked MySQL Server binaries, this can be done directly by the mysqld server process at startup.  The yaSSL library used by GPL-licensed MySQL Community builds lacks the ability to generate the necessary key material, so another means to produce TLS certificates and keys was required.  This is the new mysql_ssl_rsa_setup utility.

For GPL compatibility, mysql_ssl_rsa_setup is also not linked to OpenSSL libraries – it uses OpenSSL by making calls to OpenSSL via the command-line.  For this to succeed, OpenSSL must be found in PATH.  For most Linux distributions, this requirement is satisfied by default.  On Windows, this requires OpenSSL be explicitly installed and configured.  Here is an example of the error resulting from calling mysql_ssl_rsa_setup on Windows without OpenSSL configured in PATH:

Adding OpenSSL to the PATH solves this problem:

The output above shows the key material being generated – here’s a listing of the resulting files:

We now have public certificate and private key pairs for a certificate authority (CA), the server and clients. Additionally, we have a RSA private/public key pair, which provides the means to securely exchange client password during authentication with the sha256_password plugin, when TLS for the duration of the connection is not required. This also requires OpenSSL-linked client and server binaries, meaning RSA key material is unused for GPL-licensed MySQL Community builds from Oracle.

Generated key material

Let’s take a closer look at the generated certificates, starting with the CA cert:

The issuer and the subject are identical: MySQL_Server_5.7.10_Auto_Generated_CA_Certificate. This is a self-signed CA certificate, and the corresponding key (ca-key.pem) is used to sign the server and client key certificates.  The certificate start date is the current date/time, and is valid for 3650 days (10 years, not accounting for leap days).  The sha256WithRSAEncryption signature algorithm shows that SHA-2 was used (not the less-secure SHA-1), and the public key field shows that 2048-bit encryption is used.

Be careful with the CA private key (ca-key.pem) – anybody with access to it can use it to generate additional client or server certificates that will be accepted as legitimate when CA verification is enabled. Compromised CA keys undermine the entire chain of trust on which TLS is based. Consider securely deleting the CA private key in situations where no further client or server certificates should be signed with this CA.

The public certificate (ca.pem) can be distributed as needed. It may be used client-side to validate the server identity, and the server can use it to validate client identity.

Here’s the server certificate:

The server key uses a canonical name in the subject (MySQL_Server_5.7.10_Auto_Generated_Server_Certificate), and is signed by the CA certificate examined earlier. It has the same duration and generation strength characteristics, but the Serial Number for generated server certificates is 2 (CA is always 1, client certificates are always 3).

The client certificate is similar to the server certificate, except it has a different subject:

Like all private keys, the client private key (client-key.pem) needs to be kept secret – but it also needs to be distributed to the clients who will use it to identify themselves during connections. There’s no reason to keep it on the server unless TLS connections are required for clients connecting from the same host.

Key Material Naming

The key material generated by mysql_ssl_rsa_setup uses a naming convention consistent with MySQL Server (mysqld). These filenames are the new default values for TLS configuration options for MySQL Server, allowing MySQL Server to discover the generated key material automatically, and not require additional configuration. After running mysql_ssl_rsa_setup to generate key material to the data directory, starting MySQL Server 5.7 with that data directory will automatically enable TLS.

The standard naming convention also allows mysql_ssl_rsa_setup to avoid overwriting any existing TLS material produced by earlier executions, or by OpenSSL-linked MySQL Server binaries. Running mysql_ssl_rsa_setup a second time against the same data directory results in no output, and adding a --verbose option shows that key generation is skipped because existing key material was found:

The checks for existing TLS and RSA key material are independent – if any of ca.pem or server-cert.pem are present, TLS key material generation is skipped, while the existance of either private_key.pem or public_key.pem prevent generation of new RSA key material. Note that existance of client-cert.pem alone does not prevent generation of new TLS key material, which will overwrite the client public key.

This also means that users wanting to replace existing key material will need to manually delete (or move) the existing key material before invoking mysql_ssl_rsa_setup to generate new keys and certificates.

Installation Scripting

Users of Oracle-produced .RPM and .DEB packages will find key generation has been further simplified.  Because OpenSSL is likely to be already installed on most Linux machines, post-installation scripts for these packages call mysql_ssl_rsa_setup during deployment.  As a result, users who deploy MySQL Server 5.7 using these packages will typically find key material automatically generated without any additional steps.


Introduction of mysql_ssl_rsa_setup supports users of GPL-licensed MySQL Community secure deployments by simplifying generation of TLS key material.  By integrating this utility in post-installation scripts for Linux packages, many users will find key material is produced automatically, allowing MySQL Server 5.7 to support TLS without any extra effort or configuration.

4 thoughts on “Simplified SSL/TLS Setup for MySQL Community

  1. Great. Generated the certs. Start mysql with the –ssl option. SSL IS STILL DISABLED! I have a my.cnf file that looks like this:
    bind-address = *

    Should I use backslashes? All the docs I’ve seen use forward slashes, but may be assuming linux. I’m not even sure the my.cnf file is being read!

    1. I had the same issue, been fighting this since the latest windows 10 update which suddenly would NOT use the ssl certs on our web server but other windows platforms used them fine.
      Pretty sure the issue is that MySQL community server 5.7.13 will create the certs if they are not found but will not use them unless the mysql server is compiled with openssl instead of yassl, and for the life of me I can not get it to compile without several hundred errors even after getting all the requirements and following all the dev instructions. Rather infuritating, frankly.

      1. I always wondered why so many mySQL people would only use an Apple server for mySQL which has no problem with encryption as SSL is built in. Also remember that there is a new version of openSSL which patches the heartbleed bug, so you may be running into the newer encryption.

Leave a Reply