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 and Debian. Ensuring your database connections are secure is crucial, and How to Configure SSL/TLS for MySQL on Ubuntu and Debian provides a solid foundation.

Prerequisites

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

A running Ubuntu or Debian server.
MySQL server installed and running.
Root or sudo privileges on the server.

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 and Debian.

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.

Alternative Solutions for Configuring SSL/TLS

While the above steps provide a solid approach to configuring SSL/TLS for MySQL, here are two alternative solutions:

1. Using MySQL Shell’s `util.generate_ssl()` Function

MySQL Shell provides a convenient function, util.generate_ssl(), to automatically generate SSL/TLS certificates. This method simplifies the process and reduces the risk of errors.

Explanation:

The util.generate_ssl() function handles the creation of the private key, CSR, and self-signed certificate. It also automatically configures the necessary MySQL configuration file settings. This is a more streamlined approach compared to manually generating the certificates using OpenSSL.

Steps:

Connect to MySQL Shell:
```
mysqlsh --uri root@localhost
```
Enter your password when prompted.
Execute the util.generate_ssl() function:
```
util.generate_ssl()
```
This function will guide you through the process and create the required files and configuration settings. It may ask for some information similar to the openssl req command (country, state, etc.).
Restart the MySQL server:
```
sudo service mysql restart
```
Verification
Follow steps 1-4 in the original article to verify.

Advantages:

Simplified process.
Reduced risk of errors.
Automated configuration.

Disadvantages:

Requires MySQL Shell.
Less control over certificate generation parameters.

2. Using a Certificate Authority (CA) Signed Certificate

While the initial solution uses self-signed certificates, a more secure and trusted approach involves using certificates signed by a Certificate Authority (CA). This eliminates the need for clients to explicitly trust the server’s self-signed certificate.

Explanation:

A CA is a trusted third-party that verifies the identity of the server and issues a signed certificate. When a client connects to the server, it verifies the CA’s signature on the certificate, establishing trust without requiring manual configuration.

Steps:

Generate a private key and CSR:

openssl genrsa -out server-key.pem 2048
openssl req -new -key server-key.pem -out server-csr.pem

Submit the CSR to a CA:

Choose a reputable CA (e.g., Let’s Encrypt, DigiCert, Comodo) and follow their instructions for submitting the CSR. This typically involves verifying your domain ownership. Let’s Encrypt is a popular, free option. For Let’s Encrypt, you would likely use certbot.
```
sudo apt install certbot python3-certbot-apache #If using Apache
sudo certbot --apache
```
This would generate the certificates for you.
Download the signed certificate and CA bundle:

The CA will provide you with the signed certificate (e.g., server-cert.pem) and a CA bundle (e.g., ca-chain.pem), which contains the intermediate certificates needed to verify the certificate chain.

Configure MySQL to use the signed certificate and CA bundle:

sudo mv server-key.pem /etc/mysql/mysql-server-key.pem
sudo mv server-cert.pem /etc/mysql/mysql-server-cert.pem
sudo mv ca-chain.pem /etc/mysql/mysql-ca.pem

Edit /etc/mysql/mysql.conf.d/mysqld.cnf and add/modify the following lines:

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

Restart the MySQL server:
```
sudo service mysql restart
```

Advantages:

Enhanced security and trust.
No need for clients to manually trust the server’s certificate.
Automatic certificate renewal (with some CAs like Let’s Encrypt).

Disadvantages:

More complex setup process.
Requires dealing with a CA.
Potential cost associated with commercial CAs (Let’s Encrypt is free).

Configuring How to Configure SSL/TLS for MySQL on Ubuntu and Debian is a critical step in securing your database infrastructure. Choosing the right method depends on your specific requirements and security considerations.

Conclusion

Configuring SSL/TLS for MySQL on Ubuntu and Debian 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. How to Configure SSL/TLS for MySQL on Ubuntu and Debian has detailed the standard method and presented alternative methods for robust security.