Enable and require TLS for MySQL

The fix/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3

Or per-account, which is the safer way to roll it out:

ALTER USER 'app'@'10.0.1.%' REQUIRE SSL;

Why it matters

Without TLS, every query, every result row and every credential exchange crosses the network in the clear. On MySQL that includes the authentication handshake — and caching_sha2_password in particular will not send credentials over an unencrypted connection unless an RSA key exchange happens first, which is why migrating auth before fixing TLS produces confusing failures.

MySQL already generates certificates for you

This is the part that surprises people, in a good way.

MySQL 8 creates a self-signed CA and server certificate at initialisation and enables TLS automatically. You do not need to obtain a certificate to get encryption working. The files land in the data directory (ca.pem, server-cert.pem, server-key.pem) and the server uses them without any configuration.

So on a modern MySQL, TLS is almost certainly already available. The problem is that available is not required.

Those auto-generated certificates are self-signed, which matters for what the client can verify — see below. They’re fine for encryption; they cannot prove which server you reached.

Offering TLS is not requiring it

Same trap as Postgres and Redis, and worth stating plainly because MySQL’s automatic setup makes it easier to believe you’re done.

By default the server accepts both encrypted and unencrypted connections. A client that asks for TLS gets it; a client that doesn’t, connects in plaintext and is accepted silently. Since most modern clients default to preferring TLS, this usually looks fine — right up until the one legacy client that doesn’t.

require_secure_transport = ON refuses any unencrypted TCP connection outright. Unix socket connections are still allowed, since they never touch a network.

The client half: sslmode

A server requiring TLS stops eavesdropping. Only the client can verify which server it reached:

Client mode	Encrypts	Verifies CA	Verifies hostname
PREFERRED (default)	if available	no	no
REQUIRED	yes	no	no
VERIFY_CA	yes	yes	no
VERIFY_IDENTITY	yes	yes	yes

mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ca.pem -h db.internal -u app -p

REQUIRED stops passive interception and does nothing against an active machine-in-the-middle. VERIFY_IDENTITY is the one that means what people think REQUIRED means — and it needs a certificate whose name matches the host, which the auto-generated self-signed one will not.

Verify it worked

Confirm TLS is on and what’s enforced:

SHOW VARIABLES WHERE Variable_name IN ('have_ssl','require_secure_transport','tls_version','ssl_cert');

Then check which live connections are actually encrypted — this is the query that settles it, because the config cannot tell you:

SELECT t.processlist_user AS user, t.processlist_host AS host,
       t.connection_type,
       sbt.variable_value AS cipher
FROM performance_schema.threads t
LEFT JOIN performance_schema.status_by_thread sbt
  ON sbt.thread_id = t.thread_id AND sbt.variable_name = 'Ssl_cipher'
WHERE t.processlist_user IS NOT NULL;

An empty cipher on a TCP connection is a live plaintext session. That’s the finding.

Quick check for your own session:

\s

The SSL: line reports the cipher in use, or “Not in use”.

Prove a plaintext attempt is refused:

mysql --ssl-mode=DISABLED -h your.db -u app -p -e 'SELECT 1'

Expect ERROR 3159: Connections using insecure transport are prohibited.

Before you apply this

require_secure_transport = ON disconnects every client not using TLS, at once, and MySQL’s error for it is clear but arrives at connect time — so a pool that reconnects will fail within seconds.

Find out who is on plaintext before you flip it, using the performance_schema query above. Any row with an empty cipher breaks.

Roll it out per account rather than globally — this is MySQL’s genuine advantage over the alternatives here:

ALTER USER 'app'@'10.0.1.%' REQUIRE SSL;

That forces TLS for one account only. Migrate them one at a time, confirm each, and set require_secure_transport globally at the end when nothing is left. A global flip on a server you don’t fully know is an outage waiting for a convenient moment.

Two more things:

• REQUIRE SSL on an account also applies to socket connections in some versions, which can lock out local admin tooling. Test with mysql -u app -p (socket) as well as -h 127.0.0.1 (TCP).
• The auto-generated certificate expires. MySQL’s self-signed CA and server cert have finite lifetimes, and an expired one refuses connections from verifying clients. ALTER INSTANCE RELOAD TLS reloads them without a restart once regenerated — worth knowing before you need it at 3am.

Related

• Authentication plugins — get TLS working first.
• Bind to a private interface — if nothing crosses a wire, this matters less.