MySQL

Securing MySQL Connections with SSL/TLS: A Complete Configuration Guide

MySQL transmits data in plaintext by default, exposing passwords and query results to network interception. Here's how to configure SSL/TLS for MySQL — from certificate generation to enforcing encrypted connections.

JusDB Team
September 23, 2023
9 min read
152 views
Optimization
Monitoring
MySQL

During a PCI-DSS audit, your security team flags an unencrypted MySQL connection transmitting cardholder data across the network in plaintext. Or a HIPAA assessment reveals that your application's database traffic is visible to anyone with access to the network segment. These are not hypothetical scenarios — MySQL transmits all data unencrypted by default, meaning passwords, query results, and sensitive records are exposed to interception by anyone capable of capturing network traffic. Configuring SSL/TLS for MySQL eliminates this exposure by encrypting the communication channel between clients and the server, satisfying encryption-in-transit requirements across PCI-DSS, HIPAA, SOC 2, and GDPR frameworks.

This guide walks through the complete SSL/TLS configuration process for MySQL — from generating certificates to enforcing encrypted connections at the server and user level, validating encryption is active, and rotating certificates without downtime.

TL;DR: MySQL supports SSL/TLS but does not enforce it by default. To secure connections: (1) generate a CA, server certificate, and client certificate using mysql_ssl_rsa_setup or OpenSSL; (2) configure ssl-ca, ssl-cert, and ssl-key in my.cnf; (3) set require_secure_transport=ON to enforce encryption server-wide; (4) use REQUIRE SSL or REQUIRE X509 on individual user accounts; (5) verify with SHOW STATUS LIKE 'Ssl%'.

Understanding MySQL SSL/TLS

SSL/TLS for MySQL encrypts the TCP connection between the MySQL client and server. Once configured, the TLS handshake occurs before any authentication credentials or query data are transmitted, meaning the entire session — including the username, password, all SQL statements, and all result sets — is encrypted.

What Gets Encrypted

TLS encryption covers all application-layer data in transit: authentication credentials, SQL queries, query results including row data, binary log replication traffic (when configured), and any stored procedure or prepared statement output. It does not encrypt data at rest on disk — for that, you need MySQL's Transparent Data Encryption (TDE) or filesystem-level encryption.

Certificate Roles in MySQL SSL

MySQL SSL uses a three-tier certificate structure:

  • CA certificate (ca.pem): The Certificate Authority that signs both server and client certificates. Both sides trust this CA.
  • Server certificate (server-cert.pem) and key (server-key.pem): Installed on the MySQL server. The client uses the CA to verify the server's identity.
  • Client certificate (client-cert.pem) and key (client-key.pem): Optionally presented by the client. Required when using REQUIRE X509 for mutual TLS (mTLS).

TLS Version Support

MySQL 8.0+ supports TLSv1.2 and TLSv1.3 (TLSv1 and TLSv1.1 are disabled by default as of MySQL 8.0.26). For compliance with current PCI-DSS requirements (which prohibit TLS 1.0 and 1.1), MySQL 8.0 is the correct baseline. You can restrict the acceptable TLS versions with the tls_version system variable.

Generating SSL Certificates for MySQL

Option 1: mysql_ssl_rsa_setup (Quickest Path)

MySQL ships with mysql_ssl_rsa_setup, which generates a self-signed CA, server certificate, and client certificate in one command. This is appropriate for internal deployments where you control both the server and all clients.

text 
# Generate certificates in the MySQL data directory
sudo mysql_ssl_rsa_setup --datadir=/var/lib/mysql --uid=mysql

# Verify the generated files
ls -la /var/lib/mysql/*.pem

This creates: ca.pem, ca-key.pem, server-cert.pem, server-key.pem, client-cert.pem, client-key.pem.

Option 2: OpenSSL (Full Control)

For production environments requiring specific key sizes, expiry periods, or Subject Alternative Names, generate certificates with OpenSSL directly:

text 
# 1. Generate CA private key and self-signed certificate (10-year validity)
openssl genrsa 4096 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 \
  -key ca-key.pem \
  -out ca.pem \
  -subj "/CN=MySQL_CA/O=YourOrg/C=US"

# 2. Generate server private key and certificate signing request (CSR)
openssl req -newkey rsa:4096 -days 3650 -nodes \
  -keyout server-key.pem \
  -out server-req.pem \
  -subj "/CN=mysql.internal.yourorg.com/O=YourOrg/C=US"

# 3. Sign the server certificate with the CA
openssl x509 -req -days 3650 \
  -in server-req.pem \
  -CA ca.pem \
  -CAkey ca-key.pem \
  -CAcreateserial \
  -out server-cert.pem

# 4. Generate client private key and CSR
openssl req -newkey rsa:4096 -days 3650 -nodes \
  -keyout client-key.pem \
  -out client-req.pem \
  -subj "/CN=mysql-client/O=YourOrg/C=US"

# 5. Sign the client certificate with the CA
openssl x509 -req -days 3650 \
  -in client-req.pem \
  -CA ca.pem \
  -CAkey ca-key.pem \
  -CAcreateserial \
  -out client-cert.pem

# 6. Verify the server certificate chain
openssl verify -CAfile ca.pem server-cert.pem
openssl verify -CAfile ca.pem client-cert.pem

Setting File Permissions

MySQL is strict about certificate file permissions. The server will refuse to start if key files are world-readable.

text 
# Move certificates to MySQL data directory
sudo cp ca.pem server-cert.pem server-key.pem /var/lib/mysql/
sudo cp ca.pem client-cert.pem client-key.pem /var/lib/mysql/

# Set ownership to mysql user
sudo chown mysql:mysql /var/lib/mysql/ca.pem \
  /var/lib/mysql/server-cert.pem \
  /var/lib/mysql/server-key.pem

# Private keys must not be world-readable
sudo chmod 600 /var/lib/mysql/server-key.pem
sudo chmod 644 /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem

Configuring MySQL Server for SSL

Add the SSL configuration to your my.cnf (typically at /etc/mysql/my.cnf or /etc/my.cnf) under the [mysqld] section:

text 
[mysqld]
# SSL/TLS Certificate Configuration
ssl-ca   = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key  = /var/lib/mysql/server-key.pem

# Restrict to TLS 1.2 and 1.3 only (PCI-DSS compliant)
tls_version = TLSv1.2,TLSv1.3

# Enforce encrypted connections server-wide (see next section)
require_secure_transport = ON

After editing my.cnf, restart MySQL:

text 
sudo systemctl restart mysql

# Verify SSL is active at the server level
mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'ssl_%';"

Expected output for have_ssl should be YES. If it shows DISABLED, check the MySQL error log (/var/log/mysql/error.log) for certificate loading failures.

Common Misconfiguration: SSL Enabled But Not Enforced
A frequent mistake is configuring SSL certificates on the server without setting require_secure_transport=ON. In this state, MySQL accepts SSL connections but also continues to accept unencrypted connections — any client that does not specify SSL options will connect in plaintext without warning. This configuration passes a superficial check ("SSL is configured") while providing no actual protection against network interception. Always pair SSL configuration with enforcement.

Enabling require_secure_transport

require_secure_transport is the critical enforcement mechanism. Without it, SSL configuration is optional and clients will silently fall back to unencrypted connections. Set this variable to ON to reject all non-SSL connection attempts at the server level, regardless of user-level settings.

You can enable this at runtime without a restart, then persist it in my.cnf:

text 
-- Enable immediately (runtime, no restart required)
SET GLOBAL require_secure_transport = ON;

-- Verify it is active
SHOW VARIABLES LIKE 'require_secure_transport';

Persist it in my.cnf so it survives a restart:

text 
[mysqld]
require_secure_transport = ON

Once enabled, any connection attempt without SSL will receive:

text 
ERROR 3159 (HY000): Connections using insecure transport are prohibited
while --require_secure_transport=ON.

Before enabling this in production, audit all application connection strings and MySQL client scripts to ensure they are specifying SSL options. Enabling require_secure_transport without updating application clients will result in connection failures.

Requiring SSL at the User Level

In addition to server-wide enforcement, you can require SSL or mutual TLS for specific MySQL accounts. This provides defense-in-depth: even if require_secure_transport is temporarily disabled, user-level SSL requirements remain in effect.

REQUIRE SSL

The client must connect over TLS, but does not need to present a client certificate:

text 
-- Require SSL for a new user
CREATE USER 'appuser'@'10.0.1.%'
  IDENTIFIED BY 'strong_password_here'
  REQUIRE SSL;

-- Add SSL requirement to an existing user
ALTER USER 'appuser'@'10.0.1.%' REQUIRE SSL;

-- Apply the change
FLUSH PRIVILEGES;

REQUIRE X509 (Mutual TLS)

The client must present a valid certificate signed by the trusted CA. This is the strongest option — both the server and client authenticate each other:

text 
-- Require mutual TLS (client must present a valid certificate)
CREATE USER 'dba_user'@'%'
  IDENTIFIED BY 'strong_password_here'
  REQUIRE X509;

-- Or require a certificate from a specific issuer and subject
CREATE USER 'service_account'@'%'
  IDENTIFIED BY 'strong_password_here'
  REQUIRE ISSUER '/CN=MySQL_CA/O=YourOrg/C=US'
  AND SUBJECT '/CN=mysql-client/O=YourOrg/C=US';

Verify User SSL Requirements

text 
-- Check SSL requirements for all users
SELECT user, host, ssl_type, ssl_cipher, x509_issuer, x509_subject
FROM mysql.user
WHERE ssl_type != '';

Configuring MySQL Clients for SSL

MySQL CLI

text 
# Connect specifying CA only (server authentication)
mysql -u appuser -p \
  --ssl-ca=/path/to/ca.pem \
  -h db.internal.yourorg.com

# Connect with mutual TLS (client certificate required for REQUIRE X509 users)
mysql -u dba_user -p \
  --ssl-ca=/path/to/ca.pem \
  --ssl-cert=/path/to/client-cert.pem \
  --ssl-key=/path/to/client-key.pem \
  -h db.internal.yourorg.com

# Verify SSL mode is active immediately after connecting
\s

Persistent Client Configuration (~/.my.cnf)

text 
[client]
ssl-ca   = /home/appuser/.mysql/ca.pem
ssl-cert = /home/appuser/.mysql/client-cert.pem
ssl-key  = /home/appuser/.mysql/client-key.pem

PHP (PDO)

text 
$pdo = new PDO(
    'mysql:host=db.internal.yourorg.com;dbname=myapp',
    'appuser',
    'password',
    [
        PDO::MYSQL_ATTR_SSL_CA   => '/path/to/ca.pem',
        PDO::MYSQL_ATTR_SSL_CERT => '/path/to/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_KEY  => '/path/to/client-key.pem',
        // Verify server certificate (do not disable in production)
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ]
);

Python (mysql-connector-python)

text 
import mysql.connector

conn = mysql.connector.connect(
    host='db.internal.yourorg.com',
    user='appuser',
    password='password',
    database='myapp',
    ssl_ca='/path/to/ca.pem',
    ssl_cert='/path/to/client-cert.pem',
    ssl_key='/path/to/client-key.pem',
    ssl_verify_cert=True,
    ssl_verify_identity=True,
)

Node.js (mysql2)

text 
const mysql = require('mysql2');
const fs = require('fs');

const pool = mysql.createPool({
  host: 'db.internal.yourorg.com',
  user: 'appuser',
  password: 'password',
  database: 'myapp',
  ssl: {
    ca: fs.readFileSync('/path/to/ca.pem'),
    cert: fs.readFileSync('/path/to/client-cert.pem'),
    key: fs.readFileSync('/path/to/client-key.pem'),
    rejectUnauthorized: true,
  },
});

JDBC (Java)

text 
String url = "jdbc:mysql://db.internal.yourorg.com:3306/myapp"
           + "?useSSL=true"
           + "&requireSSL=true"
           + "&verifyServerCertificate=true"
           + "&trustCertificateKeyStoreUrl=file:/path/to/truststore.jks"
           + "&trustCertificateKeyStorePassword=truststore_password"
           + "&clientCertificateKeyStoreUrl=file:/path/to/keystore.jks"
           + "&clientCertificateKeyStorePassword=keystore_password";

Verifying SSL Connections

Check Current Session Cipher

text 
-- In the mysql CLI, \s shows connection status including SSL cipher
\s

-- Or query the session SSL status directly
SELECT
  @@ssl_cipher AS current_cipher,
  @@tls_version AS negotiated_tls_version;

Server-Wide SSL Status Variables

text 
-- Show all SSL status counters
SHOW STATUS LIKE 'Ssl%';

-- Key variables to check:
-- Ssl_cipher          : active cipher for current connection
-- Ssl_cipher_list     : ciphers offered by server
-- Ssl_version         : TLS version for current connection
-- Ssl_accepts         : total SSL connection attempts accepted
-- Ssl_connects        : total SSL connections established (as client)
-- Ssl_finished_accepts: total successful SSL handshakes

Identify Unencrypted Active Connections

text 
-- List all active connections and their SSL status
SELECT
  id,
  user,
  host,
  db,
  command,
  time,
  IF(ssl_cipher IS NULL OR ssl_cipher = '', 'PLAINTEXT', ssl_cipher) AS connection_type
FROM information_schema.processlist
ORDER BY connection_type;

Audit Historical SSL Usage

text 
-- Check if any users have connected without SSL (requires Performance Schema)
SELECT
  user,
  host,
  ssl_cipher,
  ssl_version
FROM performance_schema.session_account_connect_attrs
WHERE attr_name = 'ssl_cipher';

Certificate Rotation Without Downtime

SSL certificates expire. Planning rotation in advance prevents outage when certificates lapse. MySQL 8.0 supports online certificate rotation without restarting the server.

Rotation Process

text 
# Step 1: Generate new certificates (same CA or new CA)
# Follow the OpenSSL steps from the certificate generation section above
# Place new certs alongside existing ones temporarily:
# /var/lib/mysql/server-cert-new.pem
# /var/lib/mysql/server-key-new.pem

# Step 2: Update my.cnf to point to new certificate files
# [mysqld]
# ssl-cert = /var/lib/mysql/server-cert-new.pem
# ssl-key  = /var/lib/mysql/server-key-new.pem

# Step 3: Reload SSL certificates at runtime (MySQL 8.0+, no restart required)
# Run as root user in mysql CLI:
text 
-- Reload SSL configuration without restarting (MySQL 8.0.16+)
ALTER INSTANCE RELOAD TLS;

-- Verify the new certificate is active
SHOW STATUS LIKE 'Ssl_server_not_after';
SHOW STATUS LIKE 'Ssl_server_not_before';
text 
# Step 4: Distribute updated client certificates and CA to all application hosts
# before the old certificates expire. Update application configurations and
# test connections with the new certificates:
mysql -u appuser -p \
  --ssl-ca=/path/to/new-ca.pem \
  --ssl-cert=/path/to/new-client-cert.pem \
  --ssl-key=/path/to/new-client-key.pem \
  -h db.internal.yourorg.com \
  -e "SELECT 'connection OK', @@ssl_cipher;"

# Step 5: After confirming all clients are using new certificates,
# archive and remove old certificate files.
Certificate Expiry Monitoring: Set up monitoring alerts for certificate expiry well in advance. Query SHOW STATUS LIKE 'Ssl_server_not_after' or use a cron job running openssl x509 -enddate -noout -in /var/lib/mysql/server-cert.pem to check the expiry date. A 60-day advance warning gives sufficient time for planned rotation across all client applications without emergency pressure.
Key Takeaways
  • MySQL transmits data in plaintext by default — SSL/TLS must be explicitly configured and enforced to protect credentials, queries, and query results from network interception.
  • Use mysql_ssl_rsa_setup for quick certificate generation or OpenSSL for production-grade certificates with specific expiry periods and key sizes.
  • Configure ssl-ca, ssl-cert, and ssl-key in the [mysqld] section of my.cnf to enable SSL on the server.
  • Set require_secure_transport=ON to reject all unencrypted connections at the server level — without this, SSL is optional and clients silently fall back to plaintext.
  • Use REQUIRE SSL on user accounts for TLS-only connections, or REQUIRE X509 for mutual TLS requiring a client certificate.
  • Verify active encryption with \s in the MySQL CLI or SHOW STATUS LIKE 'Ssl%' — check the cipher and TLS version for each connection.
  • MySQL 8.0 supports ALTER INSTANCE RELOAD TLS for zero-downtime certificate rotation — plan rotation at least 60 days before certificate expiry.
  • Restrict acceptable TLS versions to TLSv1.2 and TLSv1.3 via the tls_version variable to satisfy PCI-DSS and current security standards.

Working with JusDB on MySQL Security

JusDB secures MySQL deployments for engineering teams with compliance requirements — SSL/TLS configuration, certificate management, user privilege auditing, and audit logging. Our DBAs handle the full MySQL security hardening lifecycle.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

Share this article

Need Expert Help?

Need expert help? Our specialists can assist with these related services:

MySQL Performance Tuning

5-15× query speedup via Performance Schema, EXPLAIN ANALYZE, InnoDB buffer pool sizing, slow-query analysis

Learn more

MySQL Consulting

Schema design, InnoDB Cluster, ProxySQL routing, 8.0 → 8.4 upgrades — 1000+ instances managed

Learn more

MySQL Migration

MySQL 5.7 → 8.0/8.4 upgrades, Oracle/MSSQL/PostgreSQL → MySQL, zero-downtime AWS RDS/Aurora cutover

Learn more

MySQL High Availability

InnoDB Cluster, Group Replication, Orchestrator failover, MHA, 99.99% uptime

Learn more

MySQL on Kubernetes

Oracle MySQL Operator, Percona Operator, StatefulSet design for InnoDB Cluster on K8s

Learn more

MySQL Support

24/7 production support: replication breaks, deadlocks, buffer-pool sizing, 15-min Sev-1 SLA

Learn more
Explore all our database services

Related Articles

MySQL Explained (2026): InnoDB, 8.4 LTS, Replication & Production Patterns

May 13, 2026

MySQL wait_timeout, net_read_timeout & All Timeout Variables (2026 Production Guide)

May 9, 2026

MySQL binlog Retention, Rotation & Purge: Production Guide (2026)

May 9, 2026