Encrypted Connections (TLS) and Data at Rest

By default, data between a client and MySQL travels over the network as readable bytes. Anyone who can capture the traffic (on a shared network, a compromised router or between cloud zones) can read queries and results. TLS (the successor of SSL) encrypts the connection and lets the client check the server’s identity.

Is my connection encrypted?

1

SHOW STATUS LIKE 'Ssl_cipher';

1
2
3
4
5

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+

A non-empty Ssl_cipher means this connection uses TLS. An empty value means it is not encrypted (a local socket connection is also unencrypted, which is fine on one machine). In mysql, \s or status shows a line SSL: Cipher in use is ....

MySQL 8 creates a self-signed certificate automatically at startup (auto_generate_certs), so TLS is available out of the box. A self-signed certificate protects against eavesdropping, but not against a man-in-the-middle, because the client cannot verify who it is talking to.

1

SELECT @@tls_version AS tls_versions, @@require_secure_transport AS require_tls, @@ssl_ca IS NOT NULL AS has_ca_file;

1
2
3
4
5
6

+-----------------+-------------+-------------+
| tls_versions    | require_tls | has_ca_file |
+-----------------+-------------+-------------+
| TLSv1.2,TLSv1.3 |           0 |           1 |
+-----------------+-------------+-------------+
1 row in set

Server settings

Make require_secure_transport = ON on any server that is reachable over a network other than a trusted private link.

Require TLS per account

1
2
3

CREATE USER 'shop_app'@'10.0.0.%' IDENTIFIED BY 'Ap9!pass#Long#Random#1' REQUIRE SSL;
CREATE USER 'partner'@'%' IDENTIFIED BY 'Pa7!ner#Long#Random#2' REQUIRE X509;
SELECT user, host, ssl_type FROM mysql.user WHERE user IN ('shop_app', 'partner') ORDER BY user;

1
2
3
4
5
6
7

+----------+----------+----------+
| user     | host     | ssl_type |
+----------+----------+----------+
| partner  | %        | X509     |
| shop_app | 10.0.0.% | ANY      |
+----------+----------+----------+
2 rows in set

A client without TLS then gets: ERROR 1045 (28000): Access denied for user ... (using password: YES). Use the mysql --ssl-mode=REQUIRED option from the client.

Client side: ssl-mode

1

mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/mysql-ca.pem -h db.example.com -u shop_app -p

The default PREFERRED is dangerous across networks: an attacker who strips TLS support from the exchange makes the client fall back to plain text. Use REQUIRED at least, and VERIFY_IDENTITY when you can.

PHP

1
2
3
4
5
6
7

<?php
$pdo = new PDO('mysql:host=db.example.com;dbname=shop;charset=utf8mb4', $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/mysql-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
]);
echo $pdo->query("SHOW STATUS LIKE 'Ssl_cipher'")->fetch(PDO::FETCH_ASSOC)['Value'];

Other drivers have the same settings (sslmode, ssl-ca and so on).

Certificates

For a private database server, create your own certificate authority and sign the server certificate, and distribute the CA certificate to clients. MySQL’s mysql_ssl_rsa_setup tool creates a CA and keys for testing. In production, use certificates from your organisation’s CA, or from your cloud provider (managed databases give you a CA bundle to download).

Remember to rotate certificates before they expire (SHOW STATUS LIKE 'Ssl_server_not_after') and to run ALTER INSTANCE RELOAD TLS; to load new files without a restart.

1

SHOW STATUS LIKE 'Ssl_server_not_after';

1
2
3
4
5

+----------------------+--------------------------+
| Variable_name        | Value                    |
+----------------------+--------------------------+
| Ssl_server_not_after | Oct  3 09:12:01 2036 GMT |
+----------------------+--------------------------+

(An auto-generated certificate on the test server. The date will differ.)

Encryption at rest

TLS protects data in motion. Data at rest (the files on disk and in backups) is protected by InnoDB tablespace encryption, which needs a keyring component to store the master key:

1

CREATE TABLE secrets (id INT PRIMARY KEY, note VARCHAR(50)) ENCRYPTION = 'Y';

If no keyring is configured, MySQL refuses:

1

ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.

(The test servers have no keyring, so this is the real response.) With a keyring set up (the file-based keyring for tests, or a key management service such as HashiCorp Vault or a cloud KMS in production), ENCRYPTION='Y' encrypts the table’s .ibd file. You can make it the default for a database or schema with DEFAULT ENCRYPTION = 'Y'.

Neither replaces good privileges and parameterised queries. Encryption at rest is often required by regulation (payment card, health data).

Application-level encryption

For especially sensitive columns (national IDs, card details), encrypt in the application with a vetted library, so that even the database administrator and a stolen backup see only ciphertext. Keep the keys outside the database (Hash and Encryption Functions).

Checklist

• [ ] TLS 1.2 or higher only.
• [ ] require_secure_transport = ON where traffic crosses a network.
• [ ] Clients use VERIFY_IDENTITY (or at least REQUIRED).
• [ ] Certificates come from a CA you control, and are renewed on time.
• [ ] Backups are encrypted, and the keys are stored separately.
• [ ] Tablespace encryption on for regulated data.