MySQL TLS — Practical Zero Trust

How to get and renew MySQL TLS certificates

Written December 15, 2021, last updated December 28, 2021

Zero Trust or BeyondProd approaches require authenticated and encrypted communications everywhere. TLS is the cryptographic protocol that powers encryption for all your technologies. For TLS, you need certificates. This practitioner's tutorial provides instructions for automating MySQL TLS certificate renewal and enabling server-side encryption.

Try it

Create a private key and request a certificate

Before you can configure MySQL TLS, you will need a certificate issued by a trusted certificate authority (CA). If you already have a certificate, private key, and CA root certificate from your organization's existing CA, you can skip to the MySQL TLS configuration section below. If you need to generate a certificate, you can:

To request a certificate from your CA using the step CLI, bootstrap your CA with step ca bootstrap and run the following command (sub the server name for the actual name / DNS name of your MySQL server).

step ca certificate --kty=RSA "db.example.net" server-cert.pem server-key.pem

Note that MySQL uses RSA keys, thus the --kty=RSA here. Your certificate and private key will be saved in server-cert.pem and server-key.pem, respectively.

MySQL is one of a few services that require the intermediate CA certificate to be bundled with the root CA certificate, in order to validate certificate chains. Let's create a bundle of your CA intermediate and root certificates:

cat server-cert.pem | awk '/BEGIN/,/END /{ if(/BEGIN/){a++}; if (a==2) {print} }' > ca.pem step ca root >> ca.pem

The certificate bundle will be saved in ca.pem.

Configure MySQL to use the certificate

You can test your certificate by starting up MySQL with TLS enabled.

The simplest way to try MySQL with TLS is to use a Docker container for testing.

mkdir tls mv server-cert.pem server-key.pem ca.pem tls docker run -it --rm \ -e MYSQL_ROOT_PASSWORD=pzttest \ -p 3306:3306 \ --mount type=bind,source="$(pwd)"/tls,target=/run/tls \ --user "$( id -u ):$( id -g )" \ mysql:latest \ --require-secure-transport=ON \ --ssl-cert=/run/tls/server-cert.pem \ --ssl-key=/run/tls/server-key.pem \ --ssl-ca=/run/tls/ca.pem \ --tls-version=TLSv1.2,TLSv1.3
  • Setting require-secure-transport=ON requires TLS for all incoming TCP connections. Unix socket connections on Linux, and shared memory connections on Windows, are also considered secure; by default they will not use TLS.
  • The setting for tls-version restricts TLS connections and disables TLSv1.1. If you're using older MySQL client versions, check those to be sure they are compatible with TLSv1.2+.
  • The server log should show that TLS has been enabled.

Test MySQL TLS configuration

Now run the mysql client and check that SSL is in use:

$ mysql -u root -h db.example.net -p --ssl-ca=tls/ca.pem Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 20 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | Ssl_cipher | TLS_AES_256_GCM_SHA384 | +---------------+------------------------+ 1 row in set (0.02 sec)

Operationalize It

Select a provisioner

Smallstep CAs use provisioners to authenticate certificate requests using passwords, one-time tokens, single sign-on, and a variety of other mechanisms.

  • ACME (RFC8555) is an open standard, used by Let's Encrypt, for authenticating certificate requests. To use ACME on a private network you need to run an ACME server. ACME is harder to setup, but has a large client ecosystem (some software even has built-in support).
  • Other provisioners use the open source step CLI and do not require a local network agent. The instructions below focus on the JWK provisioner, but can be repurposed with small tweaks to operationalize all non-ACME provisioners.
Show me instructions for...

The right provisioner depends on your operational environment.

The JWK provisioner is the most general-purpose provisioner. It supports password and one-time token-based authentication. To add a JWK provisioner called mysql to a hosted Certificate Manager authority (if you haven't already), run:

step ca provisioner add mysql --type JWK --create --x509-default-dur 720h

For instructions on adding provisioners to open source step-ca, or to learn more about other provisioner types, see Configuring step-ca Provisioners.

The ACME protocol requires access to your internal network or DNS in order to satisfy ACME challenges. For hosted Certificate Manager CAs, you'll need to configure an ACME Registration Authority on your network that will act as an ACME agent to Certificate Manager.

Configure MySQL TLS Certificate Automation

We've created a systemd-based certificate renewal timer that works with step. Check out our documentation on Renewal using systemd timers for background on how these timers work.

To install the certificate renewal unit files, run:

cd /etc/systemd/system sudo curl -sL https://files.smallstep.com/cert-renewer@.service \ -o cert-renewer@.service sudo curl -sL https://files.smallstep.com/cert-renewer@.timer \ -o cert-renewer@.timer

The renewal timer will check your certificate files every five minutes and renew them after two-thirds of their lifetime has elapsed.

We've created a systemd renewal timer for renewing certificates with a Smallstep CA (see non-ACME Linux instructions). However, we haven't yet investigated how to modify that timer for ACME use cases. We're working on it, but feel free to contribute this content directly on GitHub. At this point, you will need to manually create the cert-renewer@.service and cert-renewer@.timer template files.

To renew and hot-reload the MySQL server certificate, we will need a MySQL-specific systemd override file that can tell MySQL to refresh its certificates. To install the override, run:

sudo mkdir /etc/systemd/system/cert-renewer@mysql.service.d cat <<EOF | sudo tee /etc/systemd/system/cert-renewer@mysql.service.d/override.conf [Service] ; "Environment=" overrides are applied per environment variable. This line does not ; affect any other variables set in the service template. Environment=CERT_LOCATION=/var/lib/mysql/server-cert.pem \\ KEY_LOCATION=/var/lib/mysql/server-key.pem LoadCredential=reloader@localhost.cnf:/var/lib/mysql/reloader@localhost.cnf ; Empty ExecStartPost (Don't attempt to restart mysql.service) ExecStartPost= ExecStartPost=mysql --defaults-extra-file="\$CREDENTIALS_DIRECTORY/reloader@localhost.cnf" \\ -e "ALTER INSTANCE RELOAD TLS" EOF

You'll need a database user (eg. reloader@localhost) that has the CONNECTION_ADMIN privilege, for reloading the certificate:

mysql> CREATE USER 'reloader'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT CONNECTION_ADMIN on *.* to 'reloader'@'localhost';
Query OK, 0 rows affected (0.07 sec)

Replace password with a secure password. Finally, create the reloader@localhost.cnf credential file for the mysql client, which will look like:

[client] password="password"

When the certificate is renewed, the renewal service will use the Unix domain socket to connect as reloader@localhost. TLS will not be enabled for that connection, because it is already considered secure. That's good because we want our certificate renewer to reach the server even if the server certificate is expired.

To start the renewal timer, run:

sudo systemctl daemon-reload sudo systemctl enable --now cert-renewer@mysql.timer

You'll see that the timer is active, by checking the output of systemctl list-timers.

Distribute your root certificate to end users and systems

Once MySQL server TLS is enforced, you'll need to make sure that remote clients will verify and trust certificates signed by your CA.

The mysql CLI client will not read CA certificates from the system trust store (such as the macOS Keychain). It needs to be explicitly configured to trust your CA (using --ssl-ca=ca.pem), and to verify the server's certificate (using --ssl-mode=CA_VERIFY).

Most teams will use some form of automation to distribute the root certificate bundle to client applications. Depending on your needs and your IT or DevOps team's approach, this may be a configuration management tool (like Ansible or Puppet), a Mobile Device Management (MDM) solution, or something else. Some examples:

  • Use Ansible to add a ca.pem directly to linux VMs so running applications trust the API servers they call
  • Bake ca.pem directly into base Docker images for gRPC so gRPC clients can always reference the trusted CA
  • Store ca.pem in a Kubernetes Secret and inject it into an environment variable for access from application code
  • Store ca.pem in a Kubernetes ConfigMap and mount it to pods for reference on the filesystem

Research notes

In researching MySQL TLS, we did some thorough investigation. Here are our rough notes if you are interested in diving deeper.

  • MySQL leaf certificates need to use RSA keys, even in MySQL 8.0
  • The intermediate & root CAs can use ECDSA keys, however.
  • The mysql client doesn't trust the system trust store.
  • Connecting through the Unix domain socket on Linux, or via shared memory on Windows, is considered secure and does not use TLS unless you explicitly enable it.
  • You can globally require TLS connections via the require_secure_transport configuration option.
  • Or you can configure each user to require TLS or to use X509 authentication. Use the TLS options of CREATE USER to configure this.
  • MySQL Server creates its own certificate files when it starts up, if those files don't exist. At least, this is true in Ubuntu.
  • The CA certificate bundle used by mysql and mysqld needs to be a concatenation of the intermediate and root CA certificates. The root CA alone is not sufficient.
  • There are two TLS connection interfaces in MySQL: A main interface, and an administrative interface. MySQL can be configured with separate certificates and CAs for each interface, if you want. The administrative interface can be used to connect and reload the server's certificate files, using the ALTER INSTANCE RELOAD TLS command.
  • You may want to make a special service account on your MySQL server that is dedicated to reloading certificate files. That user will need the CONNECTION_ADMIN privilege. Then you can create a .cnf containing credentials for the reloader@db.example.com account, and use it after you renew the certificate.

Contribute to this document

The Practical Zero Trust project is a collection of living documents detailing TLS configuration across a broad spread of technologies. We'd love to make this document better. Feel free to contribute any improvements directly on GitHub.