MySQL, revered as one of the world's most popular open-source relational database systems, takes the responsibility of safeguarding sensitive data seriously. It accomplishes this by offering Secure Socket Layer (SSL) support, a powerful encryption mechanism that ensures data remains confidential and secure during transmission.

This blog post embarks on a comprehensive journey into the intricate world of MySQL SSL connections, certificates, server configurations, and the various means by which you can fortify your MySQL connections. We'll unravel the mystery behind SSL/TLS encryption in MySQL and equip you with the knowledge and tools to make your MySQL connections as secure as possible.

SSL Certificates in MySQL

By default, MySQL stores these certificates and keys in its data directory, but understanding their roles and significance is essential. Let's explore these key certificate files and their functions:

• CA Key and CA Certificate (ca-key.pem and ca.pem): These files house the Certificate Authority (CA) key and certificate, respectively. The CA certificate plays a pivotal role in verifying the authenticity of other certificates within the system, serving as the root certificate in the certificate chain.
• Server Key and Server Certificate (server-key.pem and server-cert.pem): In this set, we encounter the private key and certificate for the MySQL server. The private key's role is to decrypt incoming data encrypted with the public key, while the certificate serves to verify the server's identity to connecting clients.
• Client Key and Client Certificate (client-key.pem and client-cert.pem): Similar to server keys and certificates, these files contain the private key and certificate for MySQL clients. They come into play for encryption and authentication when clients establish connections with the MySQL server.
• Private Key and Public Key (private_key.pem and public_key.pem): These files hold the private and public keys, facilitating MySQL's secure connections feature. This feature empowers encrypted connections between the MySQL server and clients, with the private key encrypting data sent to the client and the public key securing data sent to the server.

SSL Configuration in MySQL 8.0

MySQL clients offer the convenience of automatically attempting SSL connections when interacting with the server via TCP/IP connections, provided that the server supports SSL. To verify if SSL is enabled on your MySQL server, you can utilize the following command:

1. have_ssl: This variable serves as an indicator of SSL support availability within the MySQL server binary. When it's set to YES, it signifies that MySQL has been compiled with SSL support, allowing for the use of SSL connections.
2. have_openssl: This specific variable focuses on OpenSSL, a renowned open-source SSL/TLS library often employed for enhancing network communication security. When have_openssl is set to YES, it serves as confirmation that OpenSSL support is present within MySQL. This OpenSSL support is typically a prerequisite for enabling SSL functionality.

However, it's important to note that SSL is not automatically used when connecting via a Unix socket, even if it's enabled on the server because the communication between the client and server occurs within the boundaries of the local machine.

Creating Secure Remote Users

Ensuring secure connections to your MySQL server involves the creation of secure remote users. Below, you'll find an example illustrating the process:

In this example, we've created a user named 'webuser1' who possesses the capability to connect from any host ('%'). What sets this user apart is the REQUIRE SSL clause, which mandates SSL encryption for the connection, thereby enhancing its security.

Secure remote Connection

After successfully connecting to the MySQL server via a remote connection, you can verify if SSL is in use by executing the following command:

If SSL encryption is indeed being utilized for the connection, this command will display the specific SSL cipher that is currently in use, providing confirmation of the secure connection.

Enforcing SSL

While MySQL may accept both SSL and unencrypted connections by default, you can enforce SSL by setting the require_secure_transport option to ON. This ensures that all connections must be made either with SSL or via a local Unix socket.

SSL Modes

The --ssl-mode option determines the level of security and verification used during the connection process. There are several modes to choose from:

DISABLED

In this mode, no SSL encryption is used for the connection. It's suitable for situations where security is not a concern.

PREFERRED

This mode allows the client to attempt an SSL connection, but it will fall back to an unencrypted connection if SSL is not supported. It provides flexibility while still prioritizing security.

REQUIRED

SSL becomes mandatory in this mode. If the server doesn't support SSL or the client can't establish an SSL connection, the connection will fail. It ensures that all communication is encrypted.

VERIFY_CA

In this mode, SSL is required, and the server's certificate must be valid. It adds an extra layer of security by verifying the server's certificate against a trusted Certificate Authority (CA).

VERIFY_IDENTITY

This mode combines certificate validation (VERIFY_CA) with hostname verification. It ensures not only that the server's certificate is valid but also that the server's hostname matches the one provided. This stringent mode guarantees both authenticity and secure communication.

Testing SSL Modes

1. --ssl-mode=DISABLED

If the MySQL server requires SSL (as you've configured webuser1 to require SSL), this connection attempt will fail because the client is explicitly disabling SSL.  

2. --ssl-mode=PREFERRED

If the MySQL server supports SSL and the client is capable of SSL, this connection will use SSL encryption. Otherwise, it will fall back to an unencrypted connection if SSL isn't available.

3. --ssl-mode=REQUIRED

The --ssl-mode=REQUIRED option means that SSL encryption is mandatory for this connection. If the server doesn't support SSL or the client can't establish an SSL connection, this command will fail.

4. --ssl-mode=VERIFY_CA

This mode provides a higher level of security by ensuring that the server's certificate is signed by a trusted Certificate Authority (CA). If the server's certificate is not trusted or is missing, the connection will fail.

The various --ssl-mode options in MySQL provide you with fine-grained control over the level of SSL/TLS encryption and security required for your client connections. Depending on your specific security needs and the server's SSL configuration, you can select the appropriate --ssl-mode option to enforce the desired level of security for your MySQL connections.

In the dynamic landscape of database management, security is paramount. By adopting SSL/TLS encryption and implementing robust security measures, you can ensure the integrity and confidentiality of your data, establishing a strong foundation for your MySQL operations.

Stay connected with Mydbops blogs for more technical insights and discoveries in the MySQL ecosystem.