How to Configure SSL/TLS for MySQL on Ubuntu and Debian

Secure Socket Layer (SSL) and Transport Layer Security (TLS) are cryptographic protocols used to establish secure connections between a client and a server. When it comes to database management systems like MySQL, configuring SSL/TLS can help protect sensitive data during transmission. In this article, we will guide you through the process of configuring SSL/TLS for MySQL on Ubuntu.

Prerequisites

Before we begin, ensure that you have the following prerequisites in place:

1. An Ubuntu / Debian server with MySQL installed.
2. Root or sudo access to the server.
3. A registered domain name or a valid IP address.

Step 1: Generate SSL/TLS Certificates

The first step is to generate SSL/TLS certificates for MySQL. Follow these steps:

1- Open a terminal window on your Ubuntu server.

2- Generate a private key by running the following command:

$ openssl genrsa -out server-key.pem 2048 

This command will generate a private key file named server-key.pem.

3- Next, generate a certificate signing request (CSR) by executing the following command:

$ openssl req -new -key server-key.pem -out server-csr.pem 

This command will generate a CSR file named server-csr.pem. You will be prompted to provide information such as the country, state, organization, and common name (domain name or IP address).

4- Now, generate a self-signed SSL/TLS certificate using the CSR by running the following command:

$ openssl x509 -req -in server-csr.pem -signkey server-key.pem -out server-cert.pem 

This command will generate a self-signed certificate named server-cert.pem.

5- Finally, move the generated certificate and key files to the MySQL SSL/TLS directory by running the following commands:

$ sudo mv server-key.pem /etc/mysql/mysql-server-key.pem
$ sudo mv server-cert.pem /etc/mysql/mysql-server-cert.pem 
$ sudo mv server-csr.pem /etc/mysql/mysql-server-csr.pem 

These commands move the files to the /etc/mysql/ directory, which is the default SSL/TLS directory for MySQL on Ubuntu.

Step 2: Configure MySQL to Use SSL/TLS

After generating the SSL/TLS certificates, we need to configure MySQL to use them. Follow these steps:

1- Open the MySQL configuration file using a text editor. For example:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2- Locate the [mysqld] section in the configuration file.

3- Add the following lines to enable SSL/TLS:

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

These lines specify the paths to the SSL/TLS certificate and key files generated in Step 1.

4- Save the changes and exit the text editor.

5- Restart the MySQL service to apply the configuration changes:

$ sudo service mysql restart 

MySQL will now be configured to use SSL/TLS for secure connections.

Step 3: Configuring Secure Connections for Remote Clients

To configure secure connections for remote clients, follow these steps:

1- Open the MySQL configuration file using a text editor. For example:

$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2- Locate the [mysqld] section in the configuration file.

3- Add the following line to enable SSL/TLS for remote clients:

require_secure_transport=ON 

This line ensures that remote clients must use SSL/TLS to connect to the MySQL server.

4- Save the changes and exit the text editor.

5- Restart the MySQL service to apply the configuration changes:

$ sudo service mysql restart 

MySQL will now require remote clients to use SSL/TLS for secure connections.

Step 4: Verify SSL/TLS Configuration

To verify if SSL/TLS is correctly configured for MySQL, follow these steps:

1- Open the MySQL client by running the following command:

$ mysql -u root -p

2- Enter your MySQL root password when prompted.

3- Once logged in to the MySQL shell, execute the following command to check if SSL/TLS is enabled:

sql> SHOW VARIABLES LIKE 'have_ssl'; 

If the output shows have_ssl as YES, SSL/TLS is enabled and functioning properly.

4- To view the SSL/TLS settings, execute the following command:

sql> SHOW VARIABLES LIKE 'ssl%'; 

The output will display various SSL/TLS-related variables, including the paths to the certificate and key files.

5- You can also test SSL/TLS connections by attempting to connect to the MySQL server using SSL/TLS. For example:

$ mysql -u root -p --ssl-ca=/etc/mysql/mysql-server-cert.pem --ssl-cert=/etc/mysql/mysql-server-cert.pem --ssl-key=/etc/mysql/mysql-server-key.pem 

If the connection is successful, SSL/TLS is working correctly.

Conclusion

Configuring SSL/TLS for MySQL on Ubuntu adds an additional layer of security to your database system, ensuring that sensitive data remains protected during transmission. By following the steps outlined in this article, you can generate SSL/TLS certificates, configure MySQL to use them, and enable secure connections for remote clients. Remember to regularly update your SSL/TLS certificates to maintain the highest level of security for your MySQL server.