Enable MySQL TLS and Require Encrypted Connections

By default MySQL accepts plain TCP connections, so passwords and query results travel between your application and the database in clear text. This guide shows you how to enable MySQL TLS on a Linux VPS, generate or locate the certificate files, and then require encrypted connections so no client can connect unencrypted.

The work is short: confirm the server has certificates, tell mysqld where they are, restart, and turn on the setting that rejects plain connections.

Before you start

You need:

• A Linux VPS running MySQL 8.0/8.4 or MariaDB 10.x/11.x, with sudo or root access.
• The ability to restart the database service. This drops active connections for a few seconds, so plan a maintenance window.
• A client that supports TLS. The mysql command-line client does, and so do MySQL Workbench, DBeaver and HeidiSQL.
• Your server's hostname or IP, for example 203.0.113.10 or db.example.com.

Check whether TLS is already available

MySQL 8.0 and later generate self-signed certificates in the data directory during initialisation, so have_ssl is often already YES. Check what the server currently has:

sudo mysql -e "SHOW VARIABLES LIKE '%ssl%';"

You should see rows for ssl_ca, ssl_cert, ssl_key and have_ssl. If the paths are populated and have_ssl is YES, the server can already speak TLS — skip to the configuration section.

Now check whether your current session is encrypted:

SHOW STATUS LIKE 'Ssl_cipher';

An empty Ssl_cipher means the connection is plain text. That is the normal state until you either connect with TLS explicitly or turn on require_secure_transport.

Generate TLS certificates

Use the MySQL-generated certificates

On MySQL 8.0, the files ca.pem, server-cert.pem and server-key.pem live in the data directory, usually /var/lib/mysql. If they are missing, you can create a fresh set with mysql_ssl_rsa_setup:

sudo mysql_ssl_rsa_setup --datadir=/var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql/*.pem

Create your own CA and server certificate

For MariaDB, or when you want certificates signed by a CA you control, use OpenSSL. Create a working directory first:

sudo mkdir -p /etc/mysql/certs
cd /etc/mysql/certs

Build the CA key and self-signed CA certificate:

sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 \
  -key ca-key.pem -out ca.pem -subj "/CN=MySQL-CA"

Create the server key and a certificate signed by that CA:

sudo openssl req -newkey rsa:2048 -days 3650 -nodes \
  -keyout server-key.pem -out server-req.pem -subj "/CN=db.example.com"
sudo openssl rsa -in server-key.pem -out server-key.pem
sudo openssl x509 -req -in server-req.pem -days 3650 \
  -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Set ownership and permissions so only the MySQL user can read the private keys:

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

Point MySQL at the certificates

Open the server configuration file. If you are not sure where it lives, see Where is my.cnf? MySQL and MariaDB config files explained. On Ubuntu and Debian the usual path is /etc/mysql/mysql.conf.d/mysqld.cnf.

Add the certificate paths and a TLS version floor under the [mysqld] section:

[mysqld]
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
tls_version=TLSv1.2,TLSv1.3

Restart the service:

sudo systemctl restart mysql

Use mariadb in place of mysql on MariaDB systems. Then confirm the server picked the files up:

sudo mysql -e "SHOW VARIABLES LIKE 'have_ssl';"

Require encrypted connections

Having TLS available is not the same as requiring it. A client can still connect in plain text unless you say otherwise.

On MySQL, add one line to the same [mysqld] section:

require_secure_transport=ON

Restart again, then test. Connections over the local Unix socket are still allowed, so local administration keeps working.

MariaDB does not support require_secure_transport. Enforce TLS per user instead:

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

Check which accounts are locked to TLS:

SELECT user, host, ssl_type FROM mysql.user;

Any account showing ANY under ssl_type is required to use TLS. X509 means a valid client certificate is also required.

Check it works

Connect from a remote host with TLS forced:

mysql -u deploy -p -h 203.0.113.10 --ssl-mode=REQUIRED

Once inside, run:

SHOW STATUS LIKE 'Ssl_cipher';
SHOW STATUS LIKE 'Ssl_version';

Both should return values such as TLS_AES_256_GCM_SHA384 and TLSv1.3. The \s shortcut in the client also prints the SSL cipher for the current session.

To prove the requirement is real, try a plain connection:

mysql -u deploy -p -h 203.0.113.10 --ssl-mode=DISABLED

You should get an error such as ERROR 3159 (HY000): Connections using insecure transport are prohibited. That is the behaviour you want.

Troubleshooting

• ERROR 2026 (HY000): SSL connection error — the server certificate or key path is wrong, or the key is unreadable. Check SHOW VARIABLES LIKE '%ssl%' for the paths in use, then confirm chown mysql:mysql and chmod 600 on the key files.
• ERROR 1045: Access denied right after adding REQUIRE SSL — the account is now TLS-only but the client connected in plain text. Reconnect with --ssl-mode=REQUIRED (MySQL) or --ssl (MariaDB), or revert the account with ALTER USER 'deploy'@'%' REQUIRE NONE;.
• Unknown variable 'require_secure_transport' — you are on MariaDB, which has no such setting. Remove the line and enforce TLS per user with REQUIRE SSL.
• Certificate verify failed — the client does not trust the CA. Pass the CA file with --ssl-ca=/etc/mysql/certs/ca.pem and use --ssl-mode=VERIFY_CA instead of REQUIRED.
• Applications fail to connect after the change — the app's database driver is not configured for TLS. Most drivers accept a ssl or sslmode parameter; set it to require TLS and point it at the CA certificate.

Next steps

• Lock down accounts and remote access in Install and secure MariaDB or MySQL, and allow remote access safely.
• Connect a GUI client over an encrypted tunnel with Connect MySQL Workbench, HeidiSQL or DBeaver to your VPS over SSH.
• If you have not installed the server yet, start with How to install MySQL 8.4 on Ubuntu and Debian.