How to configure SSL/TLS for MySQL or MariaDB server

Using SSL/TLS for your MySQL or MariaDB server ensures that data transferred between the server and clients is encrypted and secure. It protects sensitive information, such as passwords and other confidential data, from potential eavesdroppers.

While the default setup of MySQL and MariaDB servers may not have SSL/TLS enabled, it's highly recommended for production environments, especially when database connections are made over public or untrusted networks. With SSL/TLS, you not only ensure data confidentiality but also gain assurance of data integrity and server authentication.

Configuring SSL/TLS for MySQL or MariaDB involves creating and setting up the required certificates and keys, and then making appropriate configurations in the server's settings file.

1. Make sure you have OpenSSL installed on your system.

   apt-get install openssl

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

   mkdir /etc/mysql/ssl

3. Generate server's private key and certificate.

   $ openssl req -newkey rsa:2048 -days 365 -nodes -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-req.pem

4. Generate a certificate signing request from the private key.

   $ 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

5. Create client key and certificate.

   $ openssl req -newkey rsa:2048 -days 365 -nodes -keyout /etc/mysql/ssl/client-key.pem -out /etc/mysql/ssl/client-req.pem
   $ 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

   This is for clients that will connect to the server, and they'll need these files to authenticate themselves to the server.

6. Ensure all generated keys and certificates are in the MySQL or MariaDB server's data directory.

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

7. 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 configuration file may differ based on the server's operating system or specific installation.

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

   $ sudo systemctl restart mysql # For both MySQL and MariaDB on most distributions

   Related: How to restart MySQL/MariaDB service

9. Verify SSL/TLS is enabled and working.

   $ mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"

   You should see the value 'YES' if SSL/TLS has been correctly configured.

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

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

    It's essential to ensure secure connections by default, especially when handling sensitive data or when the database server is exposed to public networks.

This setup provides an essential layer of security to your database server. Always remember to periodically renew the SSL/TLS certificates before they expire to ensure uninterrupted secure communication.