How to configure SSL/TLS for MySQL or MariaDB server

Securing communication between clients and servers is essential for ensuring data privacy and integrity. One common way to achieve this is by configuring SSL/TLS encryption for your database server. MySQL and MariaDB are widely used open-source relational database management systems, and both support SSL/TLS encryption for secure connections. By enabling SSL/TLS, you can protect sensitive data from being intercepted and decrypted by malicious actors during transmission.

Configuring SSL/TLS for MySQL or MariaDB involves generating a set of required certificates, enabling SSL support in the server configuration, and testing the secure connection. While the process is straightforward, it's essential to follow each step carefully to ensure proper security is in place.

In this guide, we will outline the necessary steps to configure SSL/TLS for your MySQL or MariaDB server. By following these instructions, you can be confident that your server's communication is secure and compliant with modern security practices.

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 a Certificate Authority (CA) certificate and private key

   openssl req -newkey rsa:2048 -days 3650 -x509 -keyout ca-key.pem -out ca-cert.pem

   Fill in the required information when prompted.

4. Create a server private key and Certificate Signing Request (CSR)

   openssl req -newkey rsa:2048 -keyout server-key.pem -out server-req.pem

   Fill in the required information when prompted, making sure the Common Name (CN) matches your server's hostname or IP address.

5. Remove the passphrase from the server private key to prevent MySQL or MariaDB from prompting for a passphrase when starting the server.

   openssl rsa -in server-key.pem -out server-key.pem

6. Sign the server CSR with the CA certificate.

   openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

7. Generate a client private key and CSR.

   openssl req -newkey rsa:2048 -keyout client-key.pem -out client-req.pem

   Fill in the required information when prompted.

8. Remove the passphrase from the client private key.

   openssl rsa -in client-key.pem -out client-key.pem

9. Sign the client CSR with the CA certificate.

   openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem

10. Open the server's configuration file, typically located at /etc/mysql/my.cnf or /etc/my.cnf. Add the following lines under the [mysqld] section using your preferred text editor.
11. Locate the [mysqld] section in the configuration file. If it does not exist, create it by adding [mysqld] on a new line.
12. Add the following lines to the [mysqld] section, replacing the paths with the appropriate locations of your SSL/TLS certificate files.

    ssl-ca = /path/to/ca-cert.pem
    ssl-cert = /path/to/server-cert.pem
    ssl-key = /path/to/server-key.pem

    Ensure that the file paths are correct and that the MySQL or MariaDB user has read access to these files.

13. Enable SSL/TLS by adding the following line to the [mysqld] section.

    require_secure_transport = ON

14. Save and close the configuration file.
15. Restart the MySQL or MariaDB server to apply the changes.

    sudo systemctl restart mysql
    sudo systemctl restart mariadb

16. Verify that SSL/TLS is enabled by connecting to the server using the MySQL or MariaDB client with SSL/TLS options. Replace the placeholders with your server's details and certificate paths

    mysql --ssl-ca=/path/to/ca-cert.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem -u username -p -h hostname

17. Execute the following command to check the SSL/TLS status.

    SHOW STATUS LIKE 'Ssl_cipher';