Zero-Downtime MySQL Certificate Rotation

Introduction

If you're reading this blog, you probably have had the need to change the MySQL server SSL/TLS certificates without a maintenance window where you'd have to disconnect your production applications.

Tungsten Clustering 7.2.0 and 8.0.1 introduce a brand-new feature that makes the certificate upgrades almost invisible to the client applications. Thanks to a combination of connectivity and tpm cert tool improvements, rotating certificates becomes an easy and smooth process.

Procedure

There are three main steps: generate, apply, and clean-up.

If you bring your own new certificates, you can skip the certificate creation step but make sure they are in (or are converted into) the expected format.

To generate new certificates, we use the tpm cert tool which hides the complexity of the keytool commands.

We will then load the new certificates using the `tpm cert` command, first into the Tungsten Clustering keystores and truststores, then rotate the database server certificates.

Finally, we will finish the rotation by removing the old certificates from the Tungsten key/trust-stores.

Detailed Steps

Generate

[Skip the command below if you bring your own certificates, but make sure you have all the p12 and pem files listed below]

Let's open a command prompt and start by generating the new MySQL certificates:

mkdir /tmp/certs_generated
tpm cert gen mysqlcerts,p12 --certsdir /tmp/certs_generated --mysqldir /tmp/certs_generated -x -y

This single command will create the following files:

ca-key.pem
ca.pem
client-cert.p12
client-cert.pem
client-key.pem
client-req.pem
private_key.pem
public_key.pem
server-cert.pem
server-key.pem
server-req.pem

Apply

The next step is to add these new certificates to the existing, currently used tungsten keystores and truststores. We will assign them a meaningful alias name "mysqlnew":

tpm cert add /tmp/certs_generated/client-cert.p12 keystore --destalias mysqlnew -x -y --running --force
tpm cert add /tmp/certs_generated/ca.pem truststore --destalias mysqlnew -x -y --running --force
tpm cert add /tmp/certs_generated/client-cert.p12 connector_keystore --destalias mysqlnew -x -y --running --force
tpm cert add /tmp/certs_generated/ca.pem connector_truststore --destalias mysqlnew -x -y --running --force

Now, the new certificates are available to all Tungsten components.

Thanks to the brand-new 8.0.1/7.2.0 feature (code named CT-2511), Tungsten can now try both certificates when connecting to the database servers:

• it first attempts to connect using the original (old) alias
• if that first connection fails, it immediately tries the next available alias

This means the Tungsten software stack can connect to any MySQL server using either of the two certificates.

Since Tungsten can now use both old and new certificates, you can safely update the certificates on the MySQL servers — one after another — without any downtime.

The following command needs to be executed on each node of the cluster, with the Primary updated last. The example below shows the procedure for one host named db2.

Step 1 - Enter Maintenance Mode

Let's indicate to Tungsten that we're performing maintenance, so no failover occurs when restarting the MySQL server:

cctrl> set policy maintenance
$ replicator db2 offline

Step 2 — Copy the New Certificates

Copy the generated certificates to the directory where MySQL reads them (adjust the directory path according to your local configuration):

$ mv /tmp/certs_generated/*.pem /etc/mysql/certs && sudo chmod -R g+r /etc/mysql/certs

Step 3 — Adjust SELinux Context (if applicable)

When you copy new certificate files into /etc/mysql/certs, they typically inherit the default context (e.g., etc_t), which MySQL is not allowed to read. Here is how to fix it:

$ sudo semanage fcontext -a --type mysqld_etc_t '/etc/mysql/certs(/.*)?'
$ sudo restorecon -Rv /etc/mysql/certs

Step 4 — Restart MySQL

Restart the MySQL server to load the new certificates:

$ sudo systemctl restart mysqld

Step 5 — Bring the Replicator and Data Source Online

cctrl> replicator db2 online
cctrl> datasource db2 welcome
cctrl> datasource db2 online
cctrl> cluster heartbeat

Repeat

Repeat the above procedure on all Replicas. When you come to the Primary, first make it a replica:

cctrl> switch

and repeat the steps on the former Primary.

Finally, set the policy back to automatic:

cctrl> set policy automatic

You may, or may not, choose to switch back to the original Primary afterwards.

Certificate Rotation Complete

Congratulations, you've rotated the certificates on all nodes of your cluster, and client applications experienced no downtime!

Clean-up

It's now time to tidy things up. Even though the cluster is fully functional, you'll want to remove the old certificates so that they’re no longer used (as the driver still tries the old ones first).

Step 1 — Update Connectors to Use the New Certificates

Edit the security.properties file to change the default connector alias:

sed -i 's/^connector.security.keystore.alias.client.to.connector=.*/connector.security.keystore.alias.client.to.connector=mysqlnew/' /opt/continuent/tungsten/cluster-home/conf/security.properties

With Tungsten Connector magic, a simple reconfigure is enough — no running application will be disconnected.

The configuration will be applied only to new incoming connections:

$ connector reconfigure

Repeat this step on all connector nodes

Step 2 — Remove Old Aliases from Keystores

Run the following commands (adjust hostnames as needed):

for host in db1 db2 db3 # name your cluster and connector hosts here
do
  for store in keystore truststore connector_keystore connector_truststore
  do
    ssh $host tpm cert removalias --running $store mysql -x -y --force
  done
done

Repeat the above on all datasource nodes and you're set!

Wrap-Up

Rotating MySQL certificates is not something you generally can do without downtime.

However, thanks to Tungsten Clustering and its new feature, you now have a seamless recipe for certificate rotation.

Smooth Sailing!