MySQL’s TLS/SSL encryption isn’t just about scrambling data in transit; it’s fundamentally about verifying identity, making the encryption part almost a happy accident of strong authentication.
Let’s see it in action. Imagine a mysql client trying to connect to a mysqld server.
Server (mysqld) Configuration:
[mysqld]
ssl_ca = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
Client (mysql) Command:
mysql -h your_mysql_host -u your_user --ssl-ca=/etc/mysql/ssl/ca.pem --ssl-cert=/etc/mysql/ssl/client-cert.pem --ssl-key=/etc/mysql/ssl/client-key.pem your_database
When the client connects, it’s not just asking for encrypted communication. It’s presenting its client-cert.pem and asking the server to verify it against the ca.pem. The server does the same with its server-cert.pem. If the certificates are valid and signed by the same CA, the connection proceeds. The TLS handshake establishes a secure channel, and then the actual MySQL protocol traffic flows over it.
The core problem this solves is man-in-the-middle attacks and eavesdropping. Without TLS, anyone sniffing network traffic could see your SQL queries and sensitive data in plaintext. More subtly, without proper client/server authentication via certificates, a malicious actor could impersonate your database server or intercept client connections.
Here’s how it breaks down internally:
- Certificate Authority (CA): You need a CA certificate (
ca.pem). This is the root of trust. You’ll use this to sign both server and client certificates. You can use a self-signed CA for internal networks or a commercial CA for external-facing services. - Server Certificates: The MySQL server needs its own certificate (
server-cert.pem) and private key (server-key.pem). The certificate contains the server’s public key and identity information, signed by the CA. The server uses its private key to prove it owns the certificate during the handshake. - Client Certificates (Optional but Recommended): For stronger authentication, clients can also have their own certificates (
client-cert.pem) and private keys (client-key.pem), also signed by the CA. This allows the server to authenticate the client. - TLS Handshake: When a client connects, the TLS handshake begins.
- The client requests a secure connection.
- The server sends its certificate and public key.
- The client verifies the server’s certificate against its trusted CA. If it matches, the client knows it’s talking to the real server (assuming the CA is secure).
- The client can optionally send its own certificate.
- The server verifies the client’s certificate against its trusted CA. If it matches, the server knows it’s talking to an authorized client.
- Both sides then use the shared secrets established during the handshake to encrypt all subsequent communication.
The mysqld configuration is straightforward:
ssl_ca: Points to the CA certificate file. This is used by the server to verify client certificates.ssl_cert: Points to the server’s public certificate file.ssl_key: Points to the server’s private key file.
On the client side, you use the --ssl-ca, --ssl-cert, and --ssl-key options with the mysql command-line client or equivalent settings in application connection strings.
To generate these certificates, you’d typically use OpenSSL. For a self-signed CA and server/client certs:
# 1. Generate CA key and certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem
# 2. Generate server key and certificate signing request (CSR)
openssl genrsa 2048 > server-key.pem
openssl req -new -key server-key.pem -out server-req.pem
# 3. Sign the server CSR with the CA
openssl x509 -req -days 3650 -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem
# 4. Generate client key and CSR
openssl genrsa 2048 > client-key.pem
openssl req -new -key client-key.pem -out client-req.pem
# 5. Sign the client CSR with the CA
openssl x509 -req -days 3650 -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem
Then, copy ca.pem, server-cert.pem, and server-key.pem to the MySQL server’s SSL directory (e.g., /etc/mysql/ssl/) and configure mysqld as shown above. Copy ca.pem, client-cert.pem, and client-key.pem to the client machine for use.
Crucially, for MySQL to enforce TLS, you need to configure user accounts to require it. This is done using the REQUIRE SSL clause when creating or altering users:
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;
ALTER USER 'another_user'@'localhost' REQUIRE SSL;
Without REQUIRE SSL, even if TLS is configured and the handshake succeeds, the user could still connect without encryption if they don’t explicitly request it.
The most surprising part is how permissive MySQL is by default. You can configure TLS on the server, but if users aren’t explicitly restricted to REQUIRE SSL, they can connect using plain text, and the server might even allow unencrypted connections for users who don’t specify --ssl-mode=REQUIRED or similar. This means setting up the certificates is only half the battle; securing your users is the other, often overlooked, half.
Once you have TLS configured and users restricted, the next step is often managing certificate rotation and ensuring clients automatically reconnect securely when certificates expire.