Configuring TLS-Encrypted MySQL Connections: Server, Client, and Certificates

1. Why TLS matters for MySQL connections in a Magento context

MySQL connections already run opportunistically encrypted by default, but without any enforced check: a client that does not offer TLS, or an attacker who actively downgrades a connection, can still establish the session unencrypted. In a typical Magento setup with a separate database server, several web and cron servers, and possibly a cloud environment spanning multiple availability zones, the network path between application and database is no longer a closed system but crosses switches, load balancers, and sometimes even shared network segments.

Anyone reading along on that path sees not only orders and customer data but potentially the database password itself, if authentication details are ever transmitted over an unencrypted channel. For stores handling payment data, personal data under the GDPR, or PCI-DSS obligations, consistently enforced TLS encryption is therefore not optional polish but a basic safeguard that can be set up with manageable effort and that audits routinely ask about explicitly.

2. Server-side TLS configuration: require_secure_transport and SSL variables

For several years now MySQL has automatically generated self-signed certificates in the data directory on first start, so TLS is technically available right away. That is not enough for an enforced, production-grade configuration, though: actual enforcement happens through the global system variable require_secure_transport, which, since MySQL 8.0.21, prevents clients from connecting over TCP without encryption. Unix socket connections on the same host are unaffected, since they never travel over the network in the first place.

In addition, dedicated certificates should replace the automatically generated default ones as soon as multiple servers are involved, since the automatic generation happens independently per instance and does not fit a company-wide certificate chain. The relevant variables ssl_ca, ssl_cert, and ssl_key are set in the [mysqld] section of the configuration file and point to the dedicated certificate authority, the server certificate, and the private key.

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
ssl_ca   = /etc/mysql/tls/ca.pem
ssl_cert = /etc/mysql/tls/server-cert.pem
ssl_key  = /etc/mysql/tls/server-key.pem

# since MySQL 8.0.21: reject any TCP connection without TLS
require_secure_transport = ON
tls_version = TLSv1.2,TLSv1.3

3. Generating and managing certificates: a dedicated CA instead of ad hoc certificates

For smaller setups, MySQL ships the mysql_ssl_rsa_setup command line tool, which generates a minimal certificate authority along with server and client certificates in a few seconds. For production environments with several database servers and application servers, it pays off instead to build a dedicated, documented PKI structure where a central internal CA signs the certificates for every database instance, so clients only need to trust a single, consistent certificate chain.

It matters that the server certificate carries a Subject Alternative Name covering every relevant hostname and, where applicable, the internal IP address, since modern clients no longer check only the Common Name. The server's private key needs restrictive file permissions (0600, owned by the mysql user) and must never leave the database server, not even inside a backup, without additional encryption of its own.

# Building a minimal own CA with openssl (short version)
openssl genrsa -out ca-key.pem 4096
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem -subj "/CN=Mironsoft Internal DB CA"

# Generate a server certificate and sign it with the CA
openssl genrsa -out server-key.pem 2048
openssl req -new -key server-key.pem -out server-req.pem -subj "/CN=db-primary.internal"
openssl x509 -req -in server-req.pem -days 825 -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem -extfile server-ext.cnf

# server-ext.cnf contains: subjectAltName=DNS:db-primary.internal,DNS:db.internal,IP:10.0.1.10

4. Client-side configuration: REQUIRE SSL and REQUIRE X509 per user

Besides the global enforcement through require_secure_transport, TLS can also be mandated per database user for staggered security requirements. The REQUIRE SSL clause in CREATE USER or ALTER USER forces an encrypted connection without requiring a client certificate. Higher requirements are met by REQUIRE X509, which additionally demands a valid client certificate signed by the known CA and thereby enables genuine mutual authentication.

On the client side, the ssl-mode parameter controls connection behavior, with values ranging from DISABLED through PREFERRED and REQUIRED up to VERIFY_CA and VERIFY_IDENTITY. Only the latter two modes actually check whether the certificate presented by the server originates from the expected CA and, in the case of VERIFY_IDENTITY, whether the connection's hostname matches the certificate, which effectively prevents man-in-the-middle attacks.

-- Application user that must always use TLS
CREATE USER 'magento_app'@'10.0.2.%' IDENTIFIED BY 'a-strong-password' REQUIRE SSL;

-- Migration user with mutual certificate verification
CREATE USER 'ci_migration'@'10.0.3.%' IDENTIFIED BY 'another-password' REQUIRE X509;

-- Retrofit an existing user to require TLS
ALTER USER 'reporting'@'%' REQUIRE SSL;

5. Magento integration: setting the right SSL options in env.php

For Magento itself to use TLS on the database connection, the corresponding PDO options need to be set in the db section of app/etc/env.php. Magento passes the driver_options defined there straight through to the PDO MySQL driver, so the same constants used in any other PHP PDO application apply, such as PDO::MYSQL_ATTR_SSL_CA for the path to the CA file and PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to enable hostname verification equivalent to VERIFY_IDENTITY.

A common mistake in multi-server deployments is updating the CA file on only one application server while others still reference an expired certificate, causing sporadic, hard-to-reproduce connection failures. env.php should therefore be distributed consistently through the deployment process, and the path to the CA file should be identical across every server, ideally driven by central configuration management rather than manual copies.

// app/etc/env.php (excerpt)
'db' => [
    'connection' => [
        'default' => [
            'host' => 'db-primary.internal',
            'dbname' => 'magento',
            'username' => 'magento_app',
            'password' => 'a-strong-password',
            'driver_options' => [
                PDO::MYSQL_ATTR_SSL_CA => '/etc/magento/tls/ca.pem',
                PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
            ],
        ],
    ],
],

6. Certificate rotation: expiry monitoring and rollover without downtime

Certificates have a limited lifetime, and an unnoticed expired server certificate can, in the worst case, take down every application connection at once as soon as clients use VERIFY_CA or VERIFY_IDENTITY. A simple daily check with openssl x509 -enddate -noout against every certificate in use, combined with a warning threshold of about thirty days before expiry, reliably prevents that outage and should feed into an existing monitoring system.

For the actual rollover, the database server no longer needs a restart since MySQL 8.0.16: the command ALTER INSTANCE RELOAD TLS reloads new certificate and key files from the configured paths without interrupting existing connections. In practice that means copying the new certificate to disk, running ALTER INSTANCE RELOAD TLS, and only then removing the old files, so an invalid certificate is never active at any point.

7. Practical pitfalls in multi-store setups with several application servers

As soon as several application servers, or several Magento websites behind a load balancer, access the same database, typical configuration mistakes surface. The most common one is a server certificate whose Subject Alternative Name lists only a single internal hostname, while some application servers connect through a different DNS name or an IP address. Under VERIFY_IDENTITY that results in rejected connections that show up on some servers and not others, which makes troubleshooting considerably harder.

A second typical pitfall is diverging CA bundle versions across application servers after a certificate rotation, for instance because a new server was provisioned during a scaling event using an older deployment snapshot. Central certificate distribution through configuration management tools, or a shared, versioned artifact in the deployment process, prevents this drift and ensures every server uses the same certificate chain at any given time.

# Test the connection from a single application server explicitly
mysql --host=db-primary.internal --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/magento/tls/ca.pem -u magento_app -p -e "SHOW STATUS LIKE 'Ssl_cipher';"

# Compare the CA fingerprint across every application server
md5sum /etc/magento/tls/ca.pem

8. Monitoring and troubleshooting: status variables and common error codes

Whether a specific connection is actually encrypted is shown by the session variable Ssl_cipher, which stays empty for an unencrypted connection and reports the cipher suite in use for a successful TLS connection. For an ongoing overview, a periodic query against performance_schema.status_by_thread or performance_schema.status_by_account shows which accounts actually connect encrypted and which still do not.

The most common client-side error code for TLS problems is errno 2026, which shows up both for a genuine certificate problem and for a simply wrong CA file path, making it fairly uninformative on its own. In that case, a manual connection attempt using the CLI client with ssl-mode explicitly set helps, since the error message it returns is usually far more detailed than the one surfaced through the PDO driver in the application.

-- Check whether the current connection uses TLS
SHOW STATUS LIKE 'Ssl_cipher';

-- Server-wide overview of encrypted vs. unencrypted sessions
SELECT VARIABLE_VALUE AS current_cipher
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Ssl_cipher';

9. Performance impact of TLS and justified exceptions

The computational cost of TLS is essentially limited to the initial handshake, while the actual data transfer causes practically no measurable overhead anymore thanks to modern CPU acceleration for AES. With short-lived connections and no connection pooling, the repeated handshake can still become noticeable, which is one more reason connection pooling on the application side is advisable regardless, as it further reduces the TLS overhead by making the handshake happen less often.

One justified exception to the encryption requirement is a pure Unix socket connection between application and database on the same host, since there is no network path there that could be eavesdropped on. For every connection over TCP, especially between separate servers or across a cloud VPC, consistently enforced TLS encryption remains the standard, one that current compliance frameworks such as PCI-DSS 4.0 explicitly expect.

10. Summary

11. FAQ: TLS-Encrypted MySQL Connections at a Glance