Secure MySQL 8.4 LTS with TLS/SSL Certificates on Ubuntu 24.04 / Rocky Linux 10

MySQL 8.4 LTS supports TLS/SSL encryption for all client-server communication, protecting data in transit from eavesdropping and man-in-the-middle attacks. By default, MySQL allows unencrypted local connections, but any production deployment accepting remote connections must enforce TLS encryption with properly signed certificates.

This guide walks through generating a custom Certificate Authority (CA), server certificates, and client certificates with OpenSSL, then configuring MySQL 8.4 LTS to require TLS on both Ubuntu 24.04 and Rocky Linux 10. We also cover per-user TLS enforcement, client-side configuration, connection verification, and certificate rotation procedures.

Prerequisites

• A server running Ubuntu 24.04 LTS or Rocky Linux 10 with root or sudo access
• MySQL 8.4 LTS installed and running – see our guides for installing MySQL on Ubuntu or installing MySQL on RHEL/Rocky Linux
• OpenSSL installed (included by default on both distributions)
• A client machine for testing TLS connections
• Port 3306/TCP open between server and client

Step 1: Check Current MySQL TLS Status

Before generating certificates, check whether MySQL already has TLS configured. MySQL 8.4 auto-generates certificates on first startup, but these self-signed certificates are not suitable for production use where you need to verify server identity.

Log into MySQL and check the TLS status.

$ sudo mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

Expected output on a default installation:

+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_key                      |                 |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+

The auto-generated certificates (ca.pem, server-cert.pem, server-key.pem) live in the MySQL data directory. We will replace these with our own CA-signed certificates for proper trust chain verification.

Also verify the current connection encryption status.

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

If the Ssl_cipher value is empty, the current session is not encrypted.

Step 2: Create a Directory for TLS Certificates

Create a dedicated directory to store all MySQL TLS certificates and keys. Keep this separate from the MySQL data directory for cleaner management.

$ sudo mkdir -p /etc/mysql/ssl
$ sudo chmod 750 /etc/mysql/ssl

On Rocky Linux 10, the MySQL configuration directory is /etc/my.cnf.d/, but we still create the SSL directory under /etc/mysql/ssl for consistency across both distributions.

## Rocky Linux 10
$ sudo mkdir -p /etc/mysql/ssl
$ sudo chmod 750 /etc/mysql/ssl

Step 3: Generate the Certificate Authority (CA)

The CA certificate signs both the server and client certificates. Anyone who trusts this CA will trust any certificate it signs. In production, you may use an existing internal CA – for this guide, we generate a self-signed CA.

Generate a 4096-bit RSA private key for the CA.

$ sudo openssl genrsa 4096 | sudo tee /etc/mysql/ssl/ca-key.pem > /dev/null

Now generate the CA certificate, valid for 3650 days (10 years). Adjust the subject fields to match your organization.

$ sudo openssl req -new -x509 -nodes -days 3650 \
  -key /etc/mysql/ssl/ca-key.pem \
  -out /etc/mysql/ssl/ca-cert.pem \
  -subj "/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=MySQL-CA"

Verify the CA certificate was created successfully.

$ sudo openssl x509 -in /etc/mysql/ssl/ca-cert.pem -noout -subject -dates

You should see output like this:

subject=C=US, ST=California, L=SanFrancisco, O=MyOrg, OU=DBA, CN=MySQL-CA
notBefore=Mar 19 10:00:00 2026 GMT
notAfter=Mar 17 10:00:00 2036 GMT

Step 4: Generate MySQL Server TLS Certificate

The server certificate identifies the MySQL server to connecting clients. The Common Name (CN) should match the server hostname or IP address that clients use to connect.

Generate the server private key.

$ sudo openssl genrsa 4096 | sudo tee /etc/mysql/ssl/server-key.pem > /dev/null

Create a Certificate Signing Request (CSR) for the server. Replace the CN with your actual server hostname or FQDN.

$ sudo openssl req -new \
  -key /etc/mysql/ssl/server-key.pem \
  -out /etc/mysql/ssl/server-req.pem \
  -subj "/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=db-server.example.com"

Sign the server CSR with the CA certificate. Set the validity to 365 days – server certificates should be rotated annually.

$ sudo openssl x509 -req -days 365 \
  -in /etc/mysql/ssl/server-req.pem \
  -CA /etc/mysql/ssl/ca-cert.pem \
  -CAkey /etc/mysql/ssl/ca-key.pem \
  -CAcreateserial \
  -out /etc/mysql/ssl/server-cert.pem

Verify the server certificate against the CA.

$ sudo openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/server-cert.pem

Expected output:

/etc/mysql/ssl/server-cert.pem: OK

Step 5: Generate MySQL Client TLS Certificate

Client certificates allow the MySQL server to verify the identity of connecting clients. This enables mutual TLS (mTLS) authentication, where both sides prove their identity.

Generate the client private key.

$ sudo openssl genrsa 4096 | sudo tee /etc/mysql/ssl/client-key.pem > /dev/null

Create the client CSR. Use a different CN than the server certificate.

$ sudo openssl req -new \
  -key /etc/mysql/ssl/client-key.pem \
  -out /etc/mysql/ssl/client-req.pem \
  -subj "/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=mysql-client"

Sign the client CSR with the same CA.

$ sudo openssl x509 -req -days 365 \
  -in /etc/mysql/ssl/client-req.pem \
  -CA /etc/mysql/ssl/ca-cert.pem \
  -CAkey /etc/mysql/ssl/ca-key.pem \
  -CAcreateserial \
  -out /etc/mysql/ssl/client-cert.pem

Verify the client certificate.

$ sudo openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem

Output confirming the certificate chain is valid:

/etc/mysql/ssl/client-cert.pem: OK

Step 6: Set File Permissions on SSL Certificates

Private keys must be readable only by the MySQL user. Incorrect permissions will prevent MySQL from starting or loading the certificates.

$ sudo chown -R mysql:mysql /etc/mysql/ssl/
$ sudo chmod 600 /etc/mysql/ssl/*-key.pem
$ sudo chmod 644 /etc/mysql/ssl/*-cert.pem /etc/mysql/ssl/ca-cert.pem

Verify the permissions are set correctly.

$ ls -la /etc/mysql/ssl/

Expected output:

total 40
drwxr-x--- 2 mysql mysql 4096 Mar 19 10:05 .
-rw-r--r-- 1 mysql mysql 2033 Mar 19 10:02 ca-cert.pem
-rw------- 1 mysql mysql 3243 Mar 19 10:01 ca-key.pem
-rw-r--r-- 1 mysql mysql 1931 Mar 19 10:04 client-cert.pem
-rw------- 1 mysql mysql 3243 Mar 19 10:04 client-key.pem
-rw-r--r-- 1 mysql mysql 1931 Mar 19 10:03 server-cert.pem
-rw------- 1 mysql mysql 3243 Mar 19 10:02 server-key.pem

On Rocky Linux 10 with SELinux enabled, restore the SELinux context so MySQL can read the files.

## Rocky Linux 10 only
$ sudo restorecon -Rv /etc/mysql/ssl/

Step 7: Configure MySQL Server for TLS Encryption

Edit the MySQL configuration to point to the custom certificates. The configuration file location differs between distributions.

On Ubuntu 24.04, edit the MySQL configuration file.

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the [mysqld] section:

[mysqld]
# TLS/SSL Configuration
ssl_ca = /etc/mysql/ssl/ca-cert.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

# Require TLS for all connections (optional but recommended)
require_secure_transport = ON

# Minimum TLS version - enforce TLSv1.2 or higher
tls_version = TLSv1.2,TLSv1.3

On Rocky Linux 10, the configuration file is different.

$ sudo vim /etc/my.cnf.d/mysql-server.cnf

Add the same TLS settings under the [mysqld] section:

[mysqld]
# TLS/SSL Configuration
ssl_ca = /etc/mysql/ssl/ca-cert.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem

# Require TLS for all connections
require_secure_transport = ON

# Minimum TLS version
tls_version = TLSv1.2,TLSv1.3

The require_secure_transport setting forces all connections to use TLS. If you set this to ON, unencrypted connections will be rejected. Local connections over Unix socket are still allowed because they do not traverse the network.

Step 8: Restart MySQL and Verify TLS is Active

Restart the MySQL service to load the new certificates.

$ sudo systemctl restart mysql    # Ubuntu 24.04
$ sudo systemctl restart mysqld   # Rocky Linux 10

Check that MySQL started successfully.

$ sudo systemctl status mysql    # Ubuntu 24.04
$ sudo systemctl status mysqld   # Rocky Linux 10

If MySQL fails to start, check the error log for certificate-related issues.

$ sudo tail -50 /var/log/mysql/error.log      # Ubuntu 24.04
$ sudo tail -50 /var/log/mysqld.log           # Rocky Linux 10

Common causes of startup failure include incorrect file permissions on key files, mismatched CA and server certificates, or SELinux denials on Rocky Linux.

Log into MySQL and verify the TLS configuration is loaded.

$ sudo mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

The ssl_ca, ssl_cert, and ssl_key values should now point to your custom certificate paths under /etc/mysql/ssl/.

Verify that TLS is active for the current session.

$ sudo mysql -u root -p -e "SHOW STATUS LIKE 'Ssl%';" | head -20

Key values to look for:

+-----------------------------+-------------------------------+
| Variable_name               | Value                         |
+-----------------------------+-------------------------------+
| Ssl_cipher                  | TLS_AES_256_GCM_SHA384        |
| Ssl_cipher_list             | ...                           |
| Ssl_server_not_after        | Mar 19 10:03:00 2027 GMT      |
| Ssl_server_not_before       | Mar 19 10:03:00 2026 GMT      |
| Ssl_version                 | TLSv1.3                       |
+-----------------------------+-------------------------------+

A non-empty Ssl_cipher confirms the connection is encrypted. The Ssl_version shows which TLS protocol version is in use.

Step 9: Require TLS for Specific MySQL Users

Instead of enforcing TLS globally with require_secure_transport, you can require TLS on a per-user basis. This is useful when some local applications connect over Unix socket (no TLS needed) while remote users must encrypt their connections.

Create a new user that must connect over TLS.

$ sudo mysql -u root -p

Run these SQL statements to create the user with TLS requirement:

CREATE USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!' REQUIRE SSL;
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'10.0.1.%';
FLUSH PRIVILEGES;

The REQUIRE SSL clause forces this user to connect with TLS. You can be even more restrictive with REQUIRE X509, which demands a valid client certificate.

CREATE USER 'secure_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!' REQUIRE X509;
GRANT ALL PRIVILEGES ON secure_db.* TO 'secure_user'@'10.0.1.%';
FLUSH PRIVILEGES;

For the strictest control, require a specific certificate issuer and subject.

CREATE USER 'strict_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!'
  REQUIRE SUBJECT '/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=mysql-client'
  AND ISSUER '/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=MySQL-CA';
GRANT ALL PRIVILEGES ON strict_db.* TO 'strict_user'@'10.0.1.%';
FLUSH PRIVILEGES;

To modify an existing user to require TLS, use ALTER USER.

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

Verify the TLS requirements for all users.

SELECT user, host, ssl_type FROM mysql.user WHERE ssl_type != '';

Expected output:

+-------------+-----------+----------+
| user        | host      | ssl_type |
+-------------+-----------+----------+
| app_user    | 10.0.1.%  | ANY      |
| secure_user | 10.0.1.%  | X509     |
| strict_user | 10.0.1.%  | SPECIFIED|
+-------------+-----------+----------+

Step 10: Configure MySQL Client for TLS Connections

On the client machine, you need the CA certificate (and optionally the client certificate and key for X509 authentication). Copy the required files from the server to the client.

$ scp [email protected]:/etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/
$ scp [email protected]:/etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/
$ scp [email protected]:/etc/mysql/ssl/client-key.pem /etc/mysql/ssl/

Set proper permissions on the client side.

$ sudo chmod 600 /etc/mysql/ssl/client-key.pem
$ sudo chmod 644 /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem

Test a TLS connection from the client by specifying the certificates on the command line.

$ mysql -u app_user -p -h 10.0.1.10 \
  --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
  --ssl-cert=/etc/mysql/ssl/client-cert.pem \
  --ssl-key=/etc/mysql/ssl/client-key.pem

To avoid typing certificate paths every time, configure them in the MySQL client configuration file. On Ubuntu 24.04, edit /etc/mysql/mysql.conf.d/mysql.cnf. On Rocky Linux 10, edit /etc/my.cnf.d/client.cnf. You can also use ~/.my.cnf for per-user settings.

[client]
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/client-cert.pem
ssl-key = /etc/mysql/ssl/client-key.pem
ssl-mode = VERIFY_IDENTITY

The ssl-mode options control how strictly the client validates the server certificate:

ssl-mode	Description
DISABLED	No TLS encryption
PREFERRED	Use TLS if available, fall back to unencrypted (default)
REQUIRED	Require TLS but do not verify server certificate
VERIFY_CA	Require TLS and verify the server certificate against the CA
VERIFY_IDENTITY	Require TLS, verify CA, and check server hostname matches the certificate CN

For production, always use VERIFY_CA or VERIFY_IDENTITY. The REQUIRED mode encrypts traffic but does not protect against man-in-the-middle attacks.

Step 11: Verify Encrypted MySQL Connections

After connecting with TLS, run these queries to confirm the connection is encrypted.

$ mysql -u app_user -p -h 10.0.1.10 \
  --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
  --ssl-cert=/etc/mysql/ssl/client-cert.pem \
  --ssl-key=/etc/mysql/ssl/client-key.pem \
  -e "\s"

Look for the SSL line in the output:

SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384

For more detail, query the session SSL status variables.

SHOW SESSION STATUS LIKE 'Ssl%';

Key fields to verify:

+-----------------------------+-------------------------------+
| Variable_name               | Value                         |
+-----------------------------+-------------------------------+
| Ssl_cipher                  | TLS_AES_256_GCM_SHA384        |
| Ssl_version                 | TLSv1.3                       |
| Ssl_verify_mode             | 5                             |
| Ssl_server_not_after        | Mar 19 10:03:00 2027 GMT      |
+-----------------------------+-------------------------------+

You can also check all active connections and their encryption status from the server side. If you are running MySQL monitoring with Prometheus, track TLS connection metrics over time.

SELECT user, host, connection_type, ssl_version, ssl_cipher
FROM performance_schema.threads t
JOIN performance_schema.session_connect_attrs s USING(processlist_id)
WHERE t.type = 'FOREGROUND' LIMIT 10;

Alternatively, use the simpler processlist query.

SELECT * FROM performance_schema.session_status
WHERE variable_name IN ('Ssl_cipher', 'Ssl_version');

Step 12: Open Firewall Port for Remote MySQL Connections

If connecting from remote clients, open port 3306/TCP in the firewall. On Ubuntu 24.04 with UFW:

$ sudo ufw allow from 10.0.1.0/24 to any port 3306 proto tcp
$ sudo ufw reload
$ sudo ufw status | grep 3306

On Rocky Linux 10 with firewalld:

$ sudo firewall-cmd --permanent --add-service=mysql
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-services | grep mysql

For tighter security, restrict MySQL access to specific source IPs instead of opening it to all networks.

$ sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.1.0/24" port protocol="tcp" port="3306" accept'
$ sudo firewall-cmd --reload

Step 13: Certificate Rotation Without Downtime

TLS certificates expire and must be rotated before expiration. MySQL 8.4 supports reloading certificates without restarting the server, which means zero downtime during rotation.

First, check the current certificate expiry dates.

$ sudo openssl x509 -in /etc/mysql/ssl/server-cert.pem -noout -enddate

Output shows when the certificate expires:

notAfter=Mar 19 10:03:00 2027 GMT

Generate new server certificates using the same CA (follow Step 4 again). Place the new certificates in a staging directory first.

$ sudo mkdir -p /etc/mysql/ssl/new
$ sudo openssl genrsa 4096 | sudo tee /etc/mysql/ssl/new/server-key.pem > /dev/null
$ sudo openssl req -new \
  -key /etc/mysql/ssl/new/server-key.pem \
  -out /etc/mysql/ssl/new/server-req.pem \
  -subj "/C=US/ST=California/L=SanFrancisco/O=MyOrg/OU=DBA/CN=db-server.example.com"
$ sudo openssl x509 -req -days 365 \
  -in /etc/mysql/ssl/new/server-req.pem \
  -CA /etc/mysql/ssl/ca-cert.pem \
  -CAkey /etc/mysql/ssl/ca-key.pem \
  -CAcreateserial \
  -out /etc/mysql/ssl/new/server-cert.pem

Verify the new certificate before deploying it.

$ sudo openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/new/server-cert.pem

Swap the certificates in place.

$ sudo cp /etc/mysql/ssl/server-cert.pem /etc/mysql/ssl/server-cert.pem.bak
$ sudo cp /etc/mysql/ssl/server-key.pem /etc/mysql/ssl/server-key.pem.bak
$ sudo cp /etc/mysql/ssl/new/server-cert.pem /etc/mysql/ssl/server-cert.pem
$ sudo cp /etc/mysql/ssl/new/server-key.pem /etc/mysql/ssl/server-key.pem
$ sudo chown mysql:mysql /etc/mysql/ssl/server-cert.pem /etc/mysql/ssl/server-key.pem
$ sudo chmod 600 /etc/mysql/ssl/server-key.pem

Tell MySQL to reload the certificates without restarting the service. This is the key command for zero-downtime rotation.

$ sudo mysql -u root -p -e "ALTER INSTANCE RELOAD TLS;"

Verify the new certificate is loaded.

$ sudo mysql -u root -p -e "SHOW STATUS LIKE 'Ssl_server_not_after';"

The expiry date should reflect the new certificate. Existing connections continue using the old certificate until they reconnect. New connections use the updated certificate immediately.

Clean up the staging directory after successful rotation.

$ sudo rm -rf /etc/mysql/ssl/new

Step 14: Automate Certificate Expiry Monitoring

Set up a simple cron job to alert you before certificates expire. This script checks the server certificate and sends a warning 30 days before expiry.

Create the monitoring script.

echo '#!/bin/bash
CERT="/etc/mysql/ssl/server-cert.pem"
DAYS_WARNING=30
EXPIRY=$(openssl x509 -in "$CERT" -noout -enddate | cut -d= -f2)
EXPIRY_EPOCH=$(date -d "$EXPIRY" +%s)
NOW_EPOCH=$(date +%s)
DAYS_LEFT=$(( (EXPIRY_EPOCH - NOW_EPOCH) / 86400 ))

if [ "$DAYS_LEFT" -lt "$DAYS_WARNING" ]; then
    echo "WARNING: MySQL TLS certificate expires in $DAYS_LEFT days ($EXPIRY)" | \
    mail -s "MySQL TLS Certificate Expiry Warning" [email protected]
fi' | sudo tee /usr/local/bin/check-mysql-cert.sh > /dev/null
sudo chmod +x /usr/local/bin/check-mysql-cert.sh

Add it to the root crontab to run daily.

$ echo "0 8 * * * /usr/local/bin/check-mysql-cert.sh" | sudo tee -a /var/spool/cron/crontabs/root > /dev/null

On Rocky Linux 10, the cron path is different.

$ echo "0 8 * * * /usr/local/bin/check-mysql-cert.sh" | sudo tee -a /var/spool/cron/root > /dev/null

Troubleshooting MySQL TLS Issues

Here are common problems you may encounter when setting up MySQL TLS and their solutions.

ERROR 2026 (HY000): SSL connection error: error:0A000086 – This usually means the CA certificate on the client does not match the one that signed the server certificate. Verify with:

$ openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/server-cert.pem

MySQL fails to start after adding TLS config – Check the error log for specifics. The most common cause is wrong file permissions on the private key.

$ sudo ls -la /etc/mysql/ssl/server-key.pem
$ sudo namei -l /etc/mysql/ssl/

SELinux denials on Rocky Linux 10 – Check for AVC denials and fix the context.

$ sudo ausearch -m AVC -ts recent | grep mysql
$ sudo restorecon -Rv /etc/mysql/ssl/

Client connects but shows no encryption – The client defaults to ssl-mode=PREFERRED, which may silently fall back. Force TLS by setting --ssl-mode=REQUIRED on the command line or in the client config.

Certificate chain issues – If using intermediate CAs, concatenate the intermediate and root CA certificates into a single file for the ssl_ca parameter.

Conclusion

MySQL 8.4 LTS is now secured with custom TLS certificates on Ubuntu 24.04 and Rocky Linux 10. All client-server traffic is encrypted, and per-user TLS requirements give fine-grained control over who must present valid certificates. The ALTER INSTANCE RELOAD TLS command makes certificate rotation a zero-downtime operation.

For production hardening, combine TLS with strong authentication plugins, regular certificate rotation, automated expiry monitoring, and database backups to S3 or another off-site location. If you are running MySQL replication, enable TLS on the replication channel as well to encrypt data between primary and replica servers.

Related Guides

• How To Install MySQL 8.0 on Ubuntu 24.04/22.04/20.04
• How To Install MySQL 8.4/8.0 on Fedora 43/42/41/40
• Configure Prometheus MySQL Exporter on Ubuntu / CentOS
• How To Generate Let’s Encrypt SSL Certificates on Linux
• How To Install MySQL 8.0 on Debian 12/11/10