How to configure SSL/TLS for secured MySQL or MariaDB connections

Using SSL/TLS on MySQL or MariaDB servers ensures encrypted communication between the server and clients. This encryption prevents data from being intercepted during transmission. It also secures sensitive information like passwords and personal data from unauthorized access.

By default, MySQL and MariaDB do not have SSL/TLS enabled. For production environments, especially those involving public or untrusted networks, activating SSL/TLS is necessary. It ensures data integrity and guarantees server authentication, creating a secure connection between clients and the server.

Configuring SSL/TLS requires creating certificates and private keys, adjusting the server settings to use these certificates, and verifying that SSL/TLS is working. Renew certificates regularly to maintain uninterrupted secure communication.

1. Install OpenSSL on your system.

   $ sudo apt install openssl

2. Create a new directory to store the certificates and keys.

   $ sudo mkdir /etc/mysql/ssl

3. Generate the server's private key and certificate signing request (CSR).

   $ sudo openssl req -newkey rsa:2048 -days 365 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem
   
   Generating a RSA private key
   .....................................................................+++++
   ..............................................................+++++
   writing new private key to '/etc/mysql/ssl/server-key.pem'

4. Create the server certificate using the CSR.

   $ sudo openssl x509 -signkey /etc/mysql/ssl/server-key.pem -in /etc/mysql/ssl/server-req.pem -req -days 365 -out /etc/mysql/ssl/server-cert.pem
   
   Signature ok
   subject=/C=US/ST=California/L=SanFrancisco/O=YourCompany/CN=yourdomain.com
   Getting Private key

5. Generate the client key.

   $ sudo openssl req -newkey rsa:2048 -days 365 -nodes -keyout /etc/mysql/ssl/client-key.pem -out /etc/mysql/ssl/client-req.pem
   
   Generating a RSA private key
   ..........+++++
   ................................+++++
   writing new private key to '/etc/mysql/ssl/client-key.pem'

6. Create the client certificate.

   $ sudo openssl x509 -signkey /etc/mysql/ssl/client-key.pem -in /etc/mysql/ssl/client-req.pem -req -days 365 -out /etc/mysql/ssl/client-cert.pem

   These client keys and certificates will be required for clients that need to authenticate themselves to the server.

7. Set the correct ownership for the generated files to mysql user and group.

   $ sudo chown mysql:mysql /etc/mysql/ssl/*.pem

8. Set restrictive permissions on the key and certificate files.

   $ sudo chmod 600 /etc/mysql/ssl/*.pem

9. Copy all generated keys and certificates to the MySQL or MariaDB server’s data directory.

   $ sudo cp /etc/mysql/ssl/*.pem /var/lib/mysql/

10. Modify the my.cnf or my.ini configuration file to point to the SSL/TLS certificates.

    [mysqld]
    ssl-ca=/etc/mysql/ssl/ca-cert.pem
    ssl-cert=/etc/mysql/ssl/server-cert.pem
    ssl-key=/etc/mysql/ssl/server-key.pem

    The exact location of the my.cnf or my.ini file may vary depending on the operating system.

11. Restart the MySQL or MariaDB service to apply the changes.

    $ sudo systemctl restart mysql

12. Verify SSL/TLS is enabled and working.

    $ mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"
    
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | have_ssl      | YES   |
    +---------------+-------+

13. Ensure that client connections use SSL/TLS by default.

    $ mysql -u root -p -e "SET GLOBAL require_secure_transport = ON;"

    It is important to enforce secure connections, especially when the server is exposed to public networks.