Understanding TLS/SSL and Encrypted Connections in MySQL

TLS or SSL?

SSL (Secure Socket Layer) was originally created by Netscape and later standardized by the IETF; it operates on top of TCP beneath HTTP, so HTTPS is essentially HTTP + SSL/TCP. TLS (Transport Layer Security) is the newer, more secure version of SSL, standardized as TLS 1.0 and effectively SSL 3.1, and it is the protocol actually used by MySQL.

1. TLS Handshake Process

The TLS handshake is the key exchange phase where the client and server agree on a symmetric session key while authenticating the server’s public key using asymmetric cryptography.

├── ca-key.pem
├── ca.pem
├── client-cert.pem
├── client-key.pem
├── server-cert.pem
└── server-key.pem

Key Algorithms

Symmetric encryption: the same key encrypts and decrypts data, offering high performance for bulk data.

Asymmetric encryption: a public key encrypts data that only the corresponding private key can decrypt, used for securely exchanging the symmetric key and for digital signatures.

Certificate Issuance and Verification

The Certificate Authority (CA) signs a digital certificate that binds a public key to an identity. The issuance steps are:

User generates a key pair and sends the public key plus identity info to the CA.

CA verifies the user’s identity.

CA hashes the certificate data and encrypts the hash with its private key, producing a signature.

Verification on the client side involves:

Client computes the hash (H1) of the received certificate.

Client uses the CA’s public key (from a trusted store) to decrypt the signature, obtaining hash H2.

If H1 equals H2, the certificate is trusted.

2. MySQL SSL Encrypted Connection

Server‑side Configuration

Enable encryption with the --ssl startup option (default in MySQL 8.0) and set relevant system variables:

require_secure_transport : when ON, clients must use encrypted connections.

Specify certificate and key files:

ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem

MySQL 8.0 can auto‑generate these files, or you can create them manually with OpenSSL:

# Create CA certificate and key
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

# Create server certificate and key
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate and key (optional for mutual auth)
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Client‑side Configuration

MySQL client selects the desired SSL mode with --ssl-mode :

PREFFERED (default): try encrypted connection, fall back to plain if unavailable.

REQUIRED : connection must be encrypted; the server’s public key is accepted without CA verification.

DISABLED : no encryption.

VERIFY_CA : encrypted connection with CA certificate verification.

VERIFY_IDENTITY : encrypted connection with CA verification and host‑name validation (not applicable to self‑signed CA generated by MySQL).

Example commands:

# Encrypted connection without verification
mysql -h 172.16.21.4 -P3306 -utest -ptestpass --ssl-mode=REQUIRED -e "select 1"

# Encrypted connection with CA verification (self‑signed CA must be copied locally)
scp ca.pem 172.16.21.5:/tmp/
mysql -h10.186.61.173 -P3308 -uhucq -p'1qaz@WSX' \
  --ssl-ca="/tmp/ca.pem" \
  --ssl-mode=VERIFY_CA \
  -e "select 1"

3. Configuring SSL for JDBC

To disable SSL in JDBC:

jdbc:mysql://localhost:3306/hucq?useSSL=false

If the server uses caching_sha2_password or sha256_password , the client must also set AllowPublicKeyRetrieval=true to obtain the RSA public key when SSL is not used:

jdbc:mysql://localhost:3306/hucq?useSSL=false&AllowPublicKeyRetrieval=true

Enabling SSL in JDBC requires the same CA certificate handling as the MySQL client; the CA file can be supplied via connection properties as described in the official MySQL Connector/J documentation.

References

https://tangyuxian.com/2021/05/19/%E5%90%8E%E7%AB%AF/%E7%AE%97%E6%B3%95/%E7%AE%97%E6%B3%95-TLS-SSL%E6%8F%A1%E6%89%8B%E8%BF%87%E7%A8%8B/

https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html