MySQL and MariaDB TLS Configuration: Requiring Encrypted Client Connections

By /

MySQL and MariaDB both generate self-signed TLS certificates automatically during installation, but they do not require clients to use them. The default setup allows plaintext connections to coexist with TLS connections — clients that do not ask for TLS get none. This article shows how to verify that TLS is working, replace the auto-generated certificates with ones from a real CA, enforce TLS for specific users, and configure MySQL replication over encrypted connections.

Check the current TLS status

Before making any changes, check what is already enabled:

SHOW VARIABLES LIKE '%ssl%';

Expected output on a default install:

+-------------------+---------------------------------------------+
| Variable_name     | Value                                       |
+-------------------+---------------------------------------------+
| have_openssl      | YES                                         |
| have_ssl          | YES                                         |
| ssl_ca            | /var/lib/mysql/ca.pem                       |
| ssl_cert          | /var/lib/mysql/server-cert.pem              |
| ssl_key           | /var/lib/mysql/server-key.pem               |
+-------------------+---------------------------------------------+

have_ssl = YES means MySQL is willing to use TLS, but it does not mean TLS is required. To see which clients are actually using TLS:

SELECT user, host, ssl_type, ssl_cipher
FROM mysql.user
WHERE user NOT IN ('mysql.sys', 'mysql.infoschema', 'mysql.session');

A blank ssl_type means that user can connect without TLS.

Replace the auto-generated certificates with CA-signed certificates

The self-signed certificates MySQL generates are adequate for encryption but cannot be verified by clients without distributing the MySQL-generated CA. Using certificates from your own internal CA (or Let’s Encrypt) is cleaner.

Generate a CA-signed server certificate

If you have an internal CA:

# Generate server private key and CSR
openssl genrsa -out /etc/mysql/mysql-server.key 4096
openssl req -new -key /etc/mysql/mysql-server.key \
  -out /etc/mysql/mysql-server.csr \
  -subj "/CN=db.example.com"

# Sign with your CA
openssl x509 -req -in /etc/mysql/mysql-server.csr \
  -CA /etc/pki/ca/ca.crt -CAkey /etc/pki/ca/ca.key \
  -CAcreateserial -out /etc/mysql/mysql-server.crt \
  -days 365 \
  -extfile <(echo "subjectAltName=DNS:db.example.com")

Copy the CA certificate:

cp /etc/pki/ca/ca.crt /etc/mysql/mysql-ca.crt

Set ownership:

chown mysql:mysql /etc/mysql/mysql-server.key /etc/mysql/mysql-server.crt /etc/mysql/mysql-ca.crt
chmod 640 /etc/mysql/mysql-server.key
chmod 644 /etc/mysql/mysql-server.crt /etc/mysql/mysql-ca.crt

Update MySQL configuration

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu) or /etc/my.cnf (RHEL/Rocky):

[mysqld]
# TLS certificate files
ssl-ca   = /etc/mysql/mysql-ca.crt
ssl-cert = /etc/mysql/mysql-server.crt
ssl-key  = /etc/mysql/mysql-server.key

# Minimum TLS version — reject TLS 1.0 and 1.1
tls_version = TLSv1.2,TLSv1.3

# Require TLS for ALL connections (MySQL 8.0.28+)
# require_secure_transport = ON

Restart MySQL:

sudo systemctl restart mysql
sudo systemctl status mysql

Verify the new certificate is in use:

mysql -u root -p -e "SHOW STATUS LIKE 'Ssl_cipher';"

Require TLS for specific users

MySQL 8.x

ALTER USER 'appuser'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

To require a specific CA-validated certificate:

ALTER USER 'appuser'@'%'
  REQUIRE SUBJECT '/CN=appuser.example.com'
  AND ISSUER '/CN=Internal CA';
FLUSH PRIVILEGES;

MariaDB

MariaDB uses the same syntax:

ALTER USER 'appuser'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

After this change, any connection by appuser that does not use TLS will be refused:

ERROR 1045 (28000): Access denied for user 'appuser'@'...' (using password: YES)

Require TLS globally (MySQL 8.0.28+)

To enforce TLS for every connection including root:

[mysqld]
require_secure_transport = ON

Restart MySQL. This setting causes MySQL to reject any connection that cannot negotiate TLS, including connections on the local socket if they do not upgrade. Test that local socket connections still work (they do not use TLS but are considered secure):

mysql -u root -p --socket=/var/run/mysqld/mysqld.sock -e "SELECT 1;"

Connecting clients with TLS

MySQL CLI:

mysql -u appuser -p \
  --host=db.example.com \
  --ssl-ca=/etc/mysql/mysql-ca.crt \
  --ssl-mode=VERIFY_IDENTITY \
  mydb

--ssl-mode=VERIFY_IDENTITY is the strictest mode — it verifies both the CA and the hostname. Options:

ssl-modeEncryptionCertificate verified
DISABLEDNoNo
PREFERREDIf availableNo
REQUIREDYesNo
VERIFY_CAYesCA checked
VERIFY_IDENTITYYesCA + hostname checked

Python (mysql-connector-python):

import mysql.connector

conn = mysql.connector.connect(
    host='db.example.com',
    user='appuser',
    password='secret',
    database='mydb',
    ssl_ca='/etc/mysql/mysql-ca.crt',
    ssl_verify_cert=True,
    ssl_verify_identity=True
)

JDBC (Java):

jdbc:mysql://db.example.com:3306/mydb?useSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file:/path/to/truststore.jks&trustCertificateKeyStorePassword=password

Django:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'ssl': {
                'ca': '/etc/mysql/mysql-ca.crt',
            },
        },
    }
}

Verifying TLS is active on a connection

Inside a MySQL session:

SHOW STATUS LIKE 'Ssl_%';

Key values:

  • Ssl_cipher — non-empty means TLS is active (e.g., TLS_AES_256_GCM_SHA384)
  • Ssl_version — TLS version (TLSv1.3)
  • Ssl_server_not_after — certificate expiry date

Enabling TLS on MySQL replication

On the primary

In my.cnf:

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

Create the replication user with SSL requirement:

CREATE USER 'replicator'@'replica-ip' IDENTIFIED BY 'strong-password' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'replica-ip';
FLUSH PRIVILEGES;

On the replica

Copy the CA certificate from the primary:

scp primary:/etc/mysql/mysql-ca.crt /etc/mysql/mysql-ca.crt
chown mysql:mysql /etc/mysql/mysql-ca.crt

Configure the replication connection:

CHANGE MASTER TO
  MASTER_HOST='primary.example.com',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong-password',
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/mysql-ca.crt',
  MASTER_SSL_VERIFY_SERVER_CERT=1;

START SLAVE;

Verify:

SHOW SLAVE STATUS\G

Look for Master_SSL_Allowed: Yes and Slave_IO_Running: Yes.

Certificate renewal automation

Create a deploy hook for Let’s Encrypt or your internal CA renewal process:

sudo nano /etc/letsencrypt/renewal-hooks/deploy/mysql.sh
#!/bin/bash
DOMAIN=db.example.com
MYSQL_CERTS=/etc/mysql

cp /etc/letsencrypt/live/${DOMAIN}/fullchain.pem ${MYSQL_CERTS}/mysql-server.crt
cp /etc/letsencrypt/live/${DOMAIN}/privkey.pem   ${MYSQL_CERTS}/mysql-server.key
cp /etc/letsencrypt/live/${DOMAIN}/chain.pem     ${MYSQL_CERTS}/mysql-ca.crt

chown mysql:mysql ${MYSQL_CERTS}/mysql-server.crt ${MYSQL_CERTS}/mysql-server.key ${MYSQL_CERTS}/mysql-ca.crt
chmod 640 ${MYSQL_CERTS}/mysql-server.key
chmod 644 ${MYSQL_CERTS}/mysql-server.crt ${MYSQL_CERTS}/mysql-ca.crt

systemctl reload mysql || systemctl restart mysql
sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/mysql.sh

MySQL 8.0 supports runtime certificate reload without restarting:

ALTER INSTANCE RELOAD TLS;

This picks up new certificate files without any connections being dropped.

Troubleshooting

ErrorCauseFix
SSL connection error: error:... wrong versionTLS version mismatch (old client)Lower tls_version temporarily or upgrade client
ERROR 1045: Access denied for SSL userClient not using TLSAdd --ssl-mode=REQUIRED to the client
SSL error: Unable to get local issuer certificateClient does not have CA certProvide --ssl-ca or disable cert verification
Plugin caching_sha2_password could not be loadedMariaDB/old client vs MySQL 8 authUse mysql_native_password or update client
Replication stops after certificate renewalNew cert not picked upRun ALTER INSTANCE RELOAD TLS on primary

Summary

MySQL enables TLS by default during installation but does not enforce it. Replacing auto-generated certificates with CA-signed ones allows clients to properly verify the server. Enforcing TLS per user with REQUIRE SSL or globally with require_secure_transport = ON prevents plaintext connections. Clients should use --ssl-mode=VERIFY_IDENTITY or equivalent to get full protection. For replication, set MASTER_SSL=1 and MASTER_SSL_VERIFY_SERVER_CERT=1 in CHANGE MASTER TO. Certificate renewal can be done without downtime using ALTER INSTANCE RELOAD TLS.

Related Posts

Scroll to Top