How to Set Up a MySQL SSL Connection (Encrypted Database Traffic)

Overview

MySQL connections are unencrypted by default. If your application connects to a remote MySQL server — even on a private network — credentials and query data travel as plain text. Anyone with access to the network path can read that traffic. Setting up a MySQL SSL encrypted connection fixes this by wrapping all database traffic in TLS.

You’ll need this if your app and database are on separate servers, if you’re connecting over the public internet, or if a compliance requirement (PCI-DSS, HIPAA, SOC 2) mandates encrypted data in transit. It’s also worth doing on any VPS SSD Hosting setup where you’re running MySQL exposed on a non-loopback interface.

This guide covers MySQL 8.0+ and MariaDB 10.6+ on Ubuntu 22.04/24.04 and RHEL/AlmaLinux 8/9. The steps for Debian and CentOS derivatives are nearly identical — I’ll flag version-specific differences where they matter.

Prerequisites

• Root or sudo access to the MySQL server
• MySQL 8.0+ or MariaDB 10.6+ installed
• OpenSSL installed (openssl version should return a result)
• A MySQL user account with a known password — you’ll be modifying it
• The MySQL data directory path — typically /var/lib/mysql
• Basic comfort editing files with nano or vi

Step 1 — Verify Whether SSL Is Already Active

Before generating anything, check what MySQL is already doing. MySQL 8.0 actually auto-generates self-signed SSL certificates on first install. They may already be sitting in /var/lib/mysql.

Log in to MySQL and run:

SHOW VARIABLES LIKE '%ssl%';

Look at the output:

• have_ssl = YES — SSL support is compiled in and active
• have_ssl = DISABLED — MySQL found the config but SSL is turned off
• ssl_ca, ssl_cert, ssl_key — these should point to files that actually exist

Also check whether your current connection is using SSL:

s

If the output shows SSL: Not in use, encryption is off for this session even if the server supports it.

Step 2 — Generate SSL Certificates

You need three things: a Certificate Authority (CA) cert, a server cert signed by that CA, and a client cert signed by the same CA. MySQL ships a helper tool that does all of this in one command.

sudo mysql_ssl_rsa_setup --datadir=/var/lib/mysql

This generates the following files in /var/lib/mysql:

• ca.pem — the CA certificate (client needs a copy of this)
• ca-key.pem — CA private key (keep this off the server if possible)
• server-cert.pem — server certificate
• server-key.pem — server private key
• client-cert.pem — client certificate
• client-key.pem — client private key

📝 Note: If you’re running MySQL 8.0+ and the certs already exist from auto-setup, you can skip generation and go straight to Step 3. Just verify the existing certs haven’t expired: openssl x509 -in /var/lib/mysql/server-cert.pem -noout -dates

⚠ Warning: The auto-generated certs expire after 10 years but the ca-key.pem is readable on disk. For production systems handling sensitive data, generate proper certs from your own PKI or use a tool like certbot with a real CA. Self-signed is fine for encryption — it’s not fine for verifying server identity unless you distribute your CA cert to clients.

Step 3 — Configure MySQL to Use the Certificates

Open your MySQL config file. On Ubuntu/Debian it’s usually /etc/mysql/mysql.conf.d/mysqld.cnf. On RHEL/AlmaLinux it’s /etc/my.cnf or /etc/my.cnf.d/mysql-server.cnf.

Under the [mysqld] section, add or confirm these lines:

[mysqld]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
require_secure_transport=ON

The require_secure_transport=ON line is the one most tutorials skip. Without it, SSL is available but not enforced — users can still connect without it. Set this once you’ve confirmed SSL works end-to-end.

Restart MySQL:

sudo systemctl restart mysql
# or on RHEL/AlmaLinux:
sudo systemctl restart mysqld

Log back in and rerun SHOW VARIABLES LIKE '%ssl%'; to confirm have_ssl = YES and the cert paths are correct.

Step 4 — Require SSL for a Specific MySQL User

You can enforce SSL per user rather than globally. This is useful when you have internal tools connecting over localhost (which doesn’t need SSL) alongside an app connecting remotely.

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

Or, if you want to require a specific client certificate (mutual TLS):

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

REQUIRE SSL just means the connection must be encrypted. REQUIRE X509 means the client must also present a valid certificate signed by your CA. For most setups, REQUIRE SSL is enough.

Step 5 — Connect with SSL from the Client

Copy ca.pem, client-cert.pem, and client-key.pem from the server to your client machine. Then connect:

mysql -u appuser -p 
  --host=your.db.server.ip 
  --ssl-ca=/path/to/ca.pem 
  --ssl-cert=/path/to/client-cert.pem 
  --ssl-key=/path/to/client-key.pem

Once connected, verify SSL is active in this session:

s

You should see something like SSL: Cipher in use is TLS_AES_256_GCM_SHA384.

Step 6 — Update Your Application Connection String

Here’s how to pass SSL parameters in a PHP PDO connection — a common setup on shared hosting and VPS environments:

$pdo = new PDO(
    'mysql:host=your.db.server.ip;dbname=yourdb',
    'appuser',
    'your_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',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    ]
);

📝 Note: If you’re using a self-signed CA cert, set PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false only if you understand the risk — this disables hostname verification. I’d recommend using it as true and distributing your CA cert properly instead.

For Node.js using the mysql2 package:

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

const connection = mysql.createConnection({
  host: 'your.db.server.ip',
  user: 'appuser',
  password: 'your_password',
  database: 'yourdb',
  ssl: {
    ca: fs.readFileSync('/path/to/ca.pem'),
    cert: fs.readFileSync('/path/to/client-cert.pem'),
    key: fs.readFileSync('/path/to/client-key.pem'),
  }
});

Common Issues & Troubleshooting

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

This is annoyingly vague. It almost always means the CA cert on the client doesn’t match the CA that signed the server cert. Double-check you copied ca.pem from the same server generating the connection. If certs were regenerated on the server, you need to re-copy them to the client.

ERROR 1045 (28000): Access denied — user requires SSL but connection is not using it

The user has REQUIRE SSL set but the client connected without SSL flags. Either add --ssl-ca to your connection command, or temporarily remove the SSL requirement with ALTER USER 'appuser'@'%' REQUIRE NONE; while you debug.

MySQL won’t start after editing my.cnf

Usually a file permission issue. MySQL expects the key and cert files to be owned by the mysql user and not world-readable. Run:

sudo chown mysql:mysql /var/lib/mysql/server-key.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/ca.pem
sudo chmod 600 /var/lib/mysql/server-key.pem

Then check the MySQL error log: sudo journalctl -u mysql -n 50

s shows “SSL: Not in use” even though SSL is configured

The server supports SSL but this specific connection isn’t using it. Either the client isn’t passing --ssl-ca, or MySQL’s require_secure_transport is off so it silently fell back to unencrypted. Add require_secure_transport=ON to [mysqld] to force it — that way no connection can succeed without SSL, which makes misconfiguration obvious fast.

Certificate expired

Auto-generated certs are valid for 10 years from creation, but certs from internal PKIs might expire sooner. Check with:

openssl x509 -in /var/lib/mysql/server-cert.pem -noout -dates

If expired, rerun mysql_ssl_rsa_setup, restart MySQL, and re-copy the updated ca.pem and client certs to any machines that connect remotely.

Root Authentication Note (Ubuntu/Debian)

On Ubuntu 22.04 and 24.04, the MySQL root account uses socket authentication by default. If you’re running mysql_secure_installation as part of your setup and it fails or loops, here’s why: it’s trying to authenticate with a password but root is using the auth_socket plugin, so it ignores the password entirely.

Fix it before proceeding:

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'ReplaceWithAStrongPassword';
FLUSH PRIVILEGES;
EXIT;

sudo mysql_secure_installation

⚠ Warning: Replace ReplaceWithAStrongPassword with an actual strong password. Never leave a literal example password in a production server. On MariaDB 10.6+, the plugin name is also mysql_native_password, but verify with SELECT user, plugin FROM mysql.user WHERE user='root'; first.