MariaDB TLS in Production: Rotating Certificates Without a Maintenance Window

The first warning came from a status variable nobody had graphed until that year: Ssl_server_not_after, sampled into the monitoring pipeline on a whim, fired an alert twenty-one days before the primary's certificate expired. The primary served about 400 concurrent connections at peak, half of them from application pools, a quarter from replicas, and the rest from a zoo of cron jobs, BI tools, and one legacy reporting service nobody wanted to touch. The rotation itself turned out to be the easy part — MariaDB has been able to reload TLS material without a restart since 10.4, and the whole server-side cutover took ninety seconds per box. The part that actually bit us was one application account created two years earlier with REQUIRE SUBJECT pinning the exact distinguished name of the old client certificate. New cert, new subject, account locked out, and a dashboard went red at 09:40 on rotation day. This is the procedure we run now, in the order that keeps both the downtime and the surprise account lockouts at zero.

TLS on a database is one of those topics where the setup article is easy to find and the lifecycle article is not. Setup takes an afternoon. The lifecycle — expiry monitoring, CA rollover, per-account requirements, replication channels — is where production actually lives, so that is what follows.

What does the TLS posture look like before you touch anything?

Before rotating, get an honest map of the current state, because the rotation plan is only as good as the inventory. The server side is three files and a handful of variables; the client side is every account's SSL requirement and every client trust store:

-- server-side TLS state
SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('have_ssl', 'ssl_ca', 'ssl_cert', 'ssl_key',
   'tls_version', 'require_secure_transport');

-- when does the current cert expire? (sample this, alert on it)
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Ssl_server_not_before', 'Ssl_server_not_after');

-- which accounts REQUIRE what?
SELECT User, Host, Ssl_type, Ssl_cipher, X509_issuer, X509_subject
FROM mysql.user
WHERE Ssl_type <> ''
ORDER BY User;

-- are sessions actually encrypted, or just allowed to be?
SHOW STATUS LIKE 'Ssl_cipher';  -- per session: empty means plaintext

Three things to pin down from that inventory. First, have_ssl says the server can do TLS; it does not say anyone is using it. require_secure_transport, available since MariaDB 10.5, is the variable that closes the gap — with it set, TCP connections that cannot or will not negotiate TLS are refused outright, which converts "we support encryption" into "we enforce encryption." Check it before rotation day, because a rotation is the worst time to discover half your clients were quietly plaintext all along. Second, the mysql.user inventory tells you which accounts have cryptographic opinions: Ssl_type values of ANY, X509, or SPECIFIED escalate from "must use TLS" to "must present a certificate from this issuer" to "must present this exact certificate," and the SPECIFIED rows are where rotation-day surprises live. Third, tls_version deserves a look while you are here — TLSv1.2 as the floor is the sane default, and raising the floor is a rotation-adjacent change that breaks old drivers just as surely as a new CA does.

How does the online rotation actually work?

The mechanism is one statement: ALTER INSTANCE RELOAD TLS. It re-reads ssl_ca, ssl_cert, and ssl_key from their configured paths and applies them to new connections without restarting the server and without dropping existing sessions. Existing connections keep the session they have — TLS is negotiated at connect time — so nothing in flight is disturbed, and connection pools pick up the new material naturally as they recycle connections. That property is what makes the rotation order safe:

Step one, distribute the new CA to every client trust store before the server changes anything. The reliable pattern is a CA bundle containing both the old and the new CA, rolled out to applications, replicas, cron hosts, and the BI box, so that during the cutover both old-issued and new-issued server certificates validate. Step two, install the new server certificate and key at the configured paths on each server and run ALTER INSTANCE RELOAD TLS — ninety seconds a box, verified immediately with Ssl_server_not_after and with openssl s_client from a client host, because the server's opinion of its own certificate is not the same as a client's validation of it. Step three, after everything has stabilized and old-CA-issued certs are no longer presented anywhere, remove the old CA from the bundles. Most rotation incidents are ordering incidents: the server moved first, a client trust store only knew the old CA, and the outage was self-inflicted. The bundle-first order makes the cutover itself a non-event.

-- after new files are in place at ssl_cert / ssl_key / ssl_ca paths:
ALTER INSTANCE RELOAD TLS;

-- confirm the server picked up the new material
SHOW GLOBAL STATUS LIKE 'Ssl_server_not_after';

-- from a client host (shell, not SQL):
--   openssl s_client -connect db-primary:3306 -starttls mysql \
--     -CAfile /etc/ssl/clients/ca-bundle.pem | openssl x509 -noout -dates

What do per-account requirements do to your rotation plan?

Per-account SSL requirements are where a rotation stops being a file-copy exercise. The ladder, in ascending strictness: REQUIRE SSL (any TLS session will do), REQUIRE X509 (the client must present a certificate signed by a trusted CA), and REQUIRE ISSUER / REQUIRE SUBJECT / REQUIRE CIPHER (the certificate must match a specific issuer, a specific subject distinguished name, or the session a specific cipher). Our 09:40 incident was a REQUIRE SUBJECT account: the new client certificate had a reorganized distinguished name — the org had renamed a department, and the CN changed with it — and the account's pinned subject no longer matched anything the client could present. The fix was an ALTER USER to re-pin the new subject, but the lesson was that subject pinning couples your accounts to your certificate authority's naming habits, and naming habits change:

-- the ladder of strictness, per account
ALTER USER 'etl_svc'@'%' REQUIRE SSL;
ALTER USER 'report_legacy'@'10.%' REQUIRE X509;
ALTER USER 'payroll_feed'@'10.4.2.%'
  REQUIRE ISSUER '/CN=MonPG Internal CA 2026'
         SUBJECT '/CN=payroll-feed-client';

-- rotation-safe re-pin: do this as PART of the rotation runbook
ALTER USER 'report_legacy'@'10.%'
  REQUIRE SUBJECT '/CN=reporting-client-2026';

My rule after the incident: prefer REQUIRE X509 with a tight CA over REQUIRE SUBJECT wherever the security model allows it. Pinning the issuer means rotation of client certificates is routine as long as they come from the approved CA; pinning the subject means every reissue is a coordinated account change, and the coordination step is exactly what gets forgotten two years after the account was created. If a compliance requirement genuinely demands subject pinning, put the ALTER USER re-pin step in the rotation runbook in writing, next to the command that issues the new cert, because nobody will reconstruct it under pressure.

How does replication fit into the rotation?

Replication channels are TLS clients too, and they are the clients most likely to be forgotten because they live in CHANGE MASTER statements instead of application config files. Each channel has its own MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CERT, and MASTER_SSL_KEY settings, and a server cert rotation that breaks a replica's validation shows up as an IO thread that will not reconnect after its next network blip — sometimes days after the rotation, which makes the cause delightfully non-obvious. The procedure mirrors the client side: update the replica's CA bundle first, verify the channel validates against the new server certificate, then rotate the source. If the replication user itself has a REQUIRE clause, it goes on the same inventory as the application accounts from the first section. And if the replica's own server certificate is in the rotation scope — because replicas serve TLS to their own clients — the same ALTER INSTANCE RELOAD TLS applies per box; there is no cluster-wide broadcast, each server reloads its own material.

Two smaller traps worth naming. verify_server_cert on the replica makes the IO thread validate the source's certificate against the configured CA, which is what you want — but it also means a sloppy rotation surfaces as replication failure rather than a certificate warning, so watch the IO thread state during the window. And on Galera clusters the replication channels for cluster traffic are a separate TLS configuration from client connections, with their own socket.ssl_* provider options; rotating one and forgetting the other splits the cluster at the next restart, which is a much worse Tuesday than a locked-out reporting account. The cluster-side failure modes deserve the same respect as the flow-control ones I wrote about in the Galera flow control piece.

What breaks quietly, and how do you monitor the aftermath?

The quiet failures after a rotation are drift and downgrade. Drift: one cron host or one BI workstation whose trust store never got the bundle, failing intermittently and retrying for weeks because its error handling is a log file nobody reads — the detection is the server-side aborted-connects counters and the client error logs, watched for a fortnight after rotation. Downgrade: a client that silently falls back to plaintext when validation fails, which is why require_secure_transport matters — with enforcement on, a broken client fails loudly at connect time instead of quietly unencrypted. Expiry monitoring closes the loop: Ssl_server_not_after sampled daily per server, alerting at thirty days, is the entire reason our second rotation started with a calendar entry instead of an outage. The status variable costs nothing to collect and is the difference between a runbook and an emergency.

Where MonPG fits

The signals worth trending here are the ones from this article's inventory: days until certificate expiry per server from Ssl_server_not_after, the fraction of sessions actually using TLS, aborted-connect rates during and after rotation windows, and replication IO-thread state as a TLS-health canary. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and certificate lifecycle is on the list of signals it is being built around: expiry countdowns and encryption coverage surfaced per server, not discovered by a client outage. Until that ships, the status queries above plus a calendar alert are your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.