MySQL SSL/TLS: Don’t Let Your Data Go “Naked” in Transit (mTLS Configuration Guide)

MySQL without SSL? You’re playing with fire!

By default, MySQL transmits data as plain text. If your application and database are on the same server via a socket, it’s fine. However, when running microservices or connecting over the Internet between Cloud regions, this is a massive vulnerability. With just 5 minutes and a tool like Wireshark, a hacker can easily capture every SQL command, from admin passwords to customer credit card info.

I once handled an incident where a system scaled up to 50 nodes. Due to the oversight of not enabling encryption on the internal network, a routine security audit revealed that the entire payment payload was completely exposed. That was a painful lesson that made me prioritize SSL/TLS implementation from the very first step of database initialization.

Three Security Levels You Need to Know

Depending on your project requirements, you can choose one of the following three levels:

• Unencrypted: Fastest speed but highest risk. Should only be used for local connections via localhost.
• Standard SSL (One-way TLS): The server presents a certificate for the client to verify. Data is encrypted, preventing eavesdropping, but the server doesn’t actually verify the client’s identity.
• Client Certificate Authentication (Two-way TLS/mTLS): The most “hardcore” level. Both the server and the client must present valid certificates signed by the same CA. Even if a hacker has the password, without the private key file on their machine, they’re stuck on the outside looking in.

Quick comparison of methods:

Why mTLS is the Choice for Large Systems?

In reality, passwords are easily leaked through configuration files or environment variables. By implementing Client Certificates, you add a physical layer of security. Even if credentials are compromised, an attacker still needs the key file stored securely on the application server. This is the most effective way to isolate the database, allowing only specific nodes to have access.

Step-by-Step Implementation

Step 1: Set Up an Internal Certificate Authority (CA)

For internal connections, I recommend creating your own CA for better control instead of using Let’s Encrypt. Use OpenSSL to generate this key set:

# Create CA (Self-signed Key and Certificate)
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem -subj "/CN=MySQL_Internal_CA"

# Create Key and Cert for Server
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem -subj "/CN=mysql-server"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create Key and Cert for Client
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem -subj "/CN=mysql-client-app-1"
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

You will end up with a set of 5 files. Keep ca-key.pem very secure (ideally offline).

Step 2: MySQL Server Configuration

Copy ca.pem, server-cert.pem, and server-key.pem to the /etc/mysql/ssl/ directory. Don’t forget to set permissions with chown mysql:mysql. Then, update your my.cnf configuration file:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# Block all unencrypted connections
require_secure_transport = ON

Restart the service to apply changes: sudo systemctl restart mysql.

Step 3: Create a User Requiring X509 Authentication

This is the crucial step. We will create a user that MySQL forces to verify a certificate before allowing entry:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'super_hard_password_123';
GRANT ALL PRIVILEGES ON prod_db.* TO 'app_user'@'%' REQUIRE X509;
FLUSH PRIVILEGES;

The REQUIRE X509 keyword ensures that even if a hacker knows the password, they cannot log in without the certificate you issued.

Step 4: Connecting from the Client

Quickly test via CLI to verify the configuration:

mysql -u app_user -p -h db.example.com \
    --ssl-ca=ca.pem \
    --ssl-cert=client-cert.pem \
    --ssl-key=client-key.pem

Example connecting with Python:

import mysql.connector

conn = mysql.connector.connect(
    user='app_user',
    password='super_hard_password_123',
    host='10.0.0.5',
    database='prod_db',
    ssl_ca='certs/ca.pem',
    ssl_cert='certs/client-cert.pem',
    ssl_key='certs/client-key.pem'
)

cursor = conn.cursor()
cursor.execute("SHOW STATUS LIKE 'Ssl_cipher'")
print(f"Connected using cipher: {cursor.fetchone()[1]}")
conn.close()

Crucial Operational Notes

After several years of maintaining SSL-enabled systems, I’ve gathered three important takeaways:

1. Performance (CPU Overhead): SSL handshakes consume significant resources. In benchmark tests, throughput can drop from 1000 req/s to 600 req/s if new connections are created constantly. Use a Connection Pool (like ProxySQL) to keep SSL connections open.
2. Don’t Let Certs Expire: The worst feeling is a system crash at 2 AM just because a certificate expired. Set up monitoring or use a script to alert you 30 days in advance.
3. CA Chain Errors: If you encounter self signed certificate in certificate chain, 90% of the time it’s because the ca.pem file on the client machine doesn’t match the CA used to sign the server’s cert. Double-check their checksums.

Implementing SSL/TLS might seem tedious at first. However, it helps you sleep better knowing your data is no longer “exposed” to the curious eyes of hackers.