Securing MySQL Connections: SSL/TLS, Password Plugins, and Authentication

1. Introduction

When building certain environments you often need to provide SSL/TLS related files such as ssl-ca , ssl-cert , and ssl-key . This article uses MySQL as an example to illustrate how to create a secure connection and to explain the underlying security concepts.

2. Security Issues When Connecting to MySQL

Impersonation of both ends : client and server can be spoofed.

Password leakage : plaintext transmission, brute‑force attacks, collision attacks.

Communication data leakage : data transmitted in clear text.

Communication data tampering .

2.1 Solution to Impersonation

Use digital signatures. The client presents a certificate and private key; the server verifies the client’s certificate with a CA certificate. The client hashes the message, signs the hash with its private key, and sends both the message and the signature. The server verifies the signature with the client’s public key.

Signatures ensure message integrity and authenticate the sender.

2.2 Solution to Password Leakage

mysql_native_password (deprecated)

Deprecated from MySQL 8.0.24, disabled in 8.4, removed in 9.0.

Stores a double‑SHA1 hash in mysql.user .

Uses a challenge‑response mechanism, but SHA1 is considered weak and unsalted hashes can be exposed.

sha256_password (not recommended)

Hashes salted passwords with SHA‑256 multiple times and stores them in mysql.user .

Even identical passwords produce different stored values because of the salt.

caching_sha2_password (recommended)

Similar to sha256_password but performs 5000 SHA‑256 rounds; the result is stored in mysql.user .

The number of rounds is controlled by caching_sha2_password_digest_rounds .

Caches password hashes to accelerate authentication. If a cached hash is found, it is compared directly; otherwise the client encrypts the password with the server’s RSA public key, the server decrypts and hashes it 5000 times for verification.

Cache can be cleared by changing the user’s password, renaming the user, or executing FLUSH PRIVILEGES .

Relevant variables: caching_sha2_password_private_key_path , caching_sha2_password_public_key_path , caching_sha2_password_auto_generate_rsa_keys , caching_sha2_password_digest_rounds .

If SSL/TLS is used, RSA keys are unnecessary because the connection is already encrypted.

2.3 Solution to Communication Data Leakage

Use SSL/TLS secure connections. The client generates a random pre‑master secret, encrypts it with the server’s public key, and sends it to the server. Both sides derive the master secret and use it with an agreed cipher suite to encrypt all subsequent traffic.

3. MySQL Operations

3.1 Preventing Password Leakage

This section focuses on the caching_sha2_password plugin. When a non‑SSL connection is used, RSA keys are employed for password exchange.

-- Verify the account does not require SSL
select user,host,plugin,ssl_type,ssl_cipher,x509_issuer,x509_subject
from mysql.user
where user='mgr_user';

If ssl_type is empty, the user does not force SSL.

3.2 Creating a Secure MySQL Connection

MySQL 5.7+ includes SSL support. To check if the server supports SSL:

-- For versions before 8.0.26
show variables where variable_name in ('have_openssl','have_ssl');

-- For 8.0.26 and later
select * from performance_schema.tls_channel_status where PROPERTY='Enabled';

To see whether a user is forced to use SSL, examine the ssl_type column in mysql.user (empty = not forced, 'SSL' = forced, 'X509' = forced with client verification).

Connect with SSL:

mysql -uys -pxxxx -h10.186.65.6 -P8038 --ssl-mode=REQUIRED

3.3 Scenarios Where MySQL Does Not Support SSL

Configuration file contains skip_ssl , disabling SSL.

Missing or invalid ssl_ca , ssl_cert , or ssl_key files cause SSL initialization to fail.

3.4 Verifying Client Identity

Specify client certificate and private key:

mysql -uys -pxxxx -h10.186.65.6 -P8038 \
  --ssl-cert=~/client_ssl/client-cert.pem \
  --ssl-key=~/client_ssl/client-key.pem

3.5 Enforcing SSL for Users

-- Force SSL
alter user xxx require SSL;

-- Force SSL with client certificate verification
alter user xxx require X509;

-- No SSL requirement
alter user xxx require none;

3.6 Replacing Expired Certificates

Replace the certificate files and reload TLS configuration:

alter instance reload tls;

4. Summary

Digital signatures verify both parties and prevent tampering. RSA key pairs or SSL/TLS links ensure secure password transmission, and SSL can also be used to create a secure connection.