A Step-by-step Guide to Configure SSL/TLS for MySQL on Linux

21Jun 2023 by Ernest Haberli Jr. No Comments

Easiest Way to Create Certificates Using Web-Based GUI OpenSSL

Cybersecurity is critical for every business with rising attacks and database vulnerabilities. . MySQL is open-sourced relational database management system (RDBMS). It is based on Structured Query Language (SQL) and used for OS like Windows, Linux, and macOS. For example, in Ubuntu Linux-based OS, MySQL is configured to allow local connections, but you need to configure SSL/TLS connections for remote access.

Securing remote access to a MySQL server is crucial because it helps to protect the server from unauthorized access. Unfortunately, a report suggests that over 3.6 million MySQL servers were exposed to cyberattacks.

Are you struggling to get your MySQL server secure? Are you having trouble configuring SSL/TLS on MySQL? Don’t worry – we’ve got you covered! In this article, we’ll provide a step-by-step guide to configure SSL/TLS for MySQL on Linux. We’ll show you exactly how to do it so that you can keep your data safe and secure.

SSL/TLS is an essential security protocol for any server, and the process of setting it up can be intimidating. But with our comprehensive guide, even a novice user will be able to set up SSL/TLS for their MySQL server in no time. We’ll walk you through every step of the way and give you all the tips and tricks that make this task easy and efficient.

Whether you’re new to setting up servers or just need a refresher course, our guide has everything you need. So don’t wait any longer – start securing your MySQL server today with our helpful guide!

A Short Introduction to SSL/TLS

SSL/TLS is a secure protocol that’s used to protect data as it travels between two points. It provides authentication and encryption, ensuring that only authorized parties can access the data. SSL/TLS also helps to ensure that any data sent is not tampered with or modified in transit. This makes it an ideal choice for protecting sensitive information, such as financial and personal details.

There are several steps involved to set up SSL/TLS for MySQL. The user will need to create an SSL certificate, configure the server to use the certificate, and then configure their client applications to use the certificate.

The process of configuring SSL/TLS for MySQL requires some technical knowledge. However, we created this step-by-step guide in such a way that a learner can also complete this process with full confidence. Once if you enable the SSL/TLS settings on your MySQL servers, you will be able to securely connect you applications and databases with a high degree of confidence and secure your data between the client and database on the transit.

Prerequisites Required to Configure SSL/TLS for MySQL

Before one can configure SSL/TLS for MySQL servers, there are a few prerequisites that need to be taken care of.

A Linux Server: You need a Linux server to host MySQL server on that. In this demo, we have used Ubuntu 22.04.
MySQL server: You should have MySQL server installed on your Ubuntu 22.04 LTS system.
Root access or sudo privileges: You need to have root access to the system, or a user account with sudo privileges to perform administrative tasks, such as installing packages, creating directories, and editing configuration files.
OpenSSL: It is require to generate CSR or Self-Signed Certificate. Anyways, It is usually pre-installed on most of the Linux distributions. If not, you can install it using the following command: sudo apt install openssl.
MySQL Client: This is optional, you need this if you want to test the SSL connection from a remote computer.

With these prerequisites in place, you can proceed to configure SSL/TLS for MySQL on your Linux server.

Install MySQL on Ubuntu

We believe MySQL has been installed and running on the server. If not you can quickly run these commands to install and configure the MySQL on your server.

sudo apt update
sudo apt install mysql-server
sudo systemctl enable mysql.service
mysql -V

After the installation, you will need to configure MySQL. This includes setting the root password and securing the installation. To start the configuration process, run the following command:

sudo mysql_secure_installation

On running the above command, you have to answer some key questions like

Do you wish to continue with the password provided?
Remove anonymous users?
Disallow root login remotely?
Remove the test database and access it.
Reload privilege tables now?

Once you answer these questions, your password will be set, and the MySQL server will be secure.

How to Configure SSL/TLS for MySQL on Linux?

Configuring SSL/TLS for MySQL on Linux involves several steps, including generating certificates, configuring the MySQL server and client, and verifying the secure connection. Here is a step-by-step guide to help you know how to set up SSL/TLS for MySQL on Linux through the process. The process involves several steps:

Generate SSL/TLS certificates and keys for the MySQL server and clients.
Configure MySQL server to enable SSL/TLS connections and specify the path to the server certificate and key files.
Create MySQL user accounts that require SSL/TLS connections, and specify the REQUIRE SSL option when creating or altering the accounts.
Restart MySQL server to apply the changes.
Configure MySQL clients to enable SSL/TLS connections and specify the path to the client certificate and key files.
Test the SSL/TLS connections by connecting to the MySQL server using the SSL/TLS protocol.

Time needed: 30 minutes.

How to Configure SSL/TLS for MySQL on Linux?

Verify the status of SSL/TLS on MySQL serverTo verify the status of SSL/TLS on a MySQL server, you can follow these steps:Connect to the MySQL server using the MySQL client.
Type the following command to check if SSL/TLS is enabled on the server:
SHOW VARIABLES LIKE 'have_ssl';If the output is “YES”, it means that SSL/TLS is enabled on the server.To check the SSL/TLS certificate details, type the following command:SHOW VARIABLES LIKE '%ssl%';This will display various SSL/TLS-related variables, including the certificate details.You can also check the SSL/TLS connection status for a particular session by using the following command:SHOW STATUS LIKE 'Ssl_cipher'; OR mysql> sThis will display the SSL/TLS cipher used for the connection.SHOW VARIABLES LIKE '%ssl%';Output +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | +---------------+----------+ 9 rows in set (0.01 sec)
Generate self-signed Certificates or get a Certificate from your CA (Certificate Authority)If you are not aware how to generate a self-signed Certificate or a Certificate from your CA, please refer these blog posts where in we clearly showed a step-by-step procedure to generate a self-signed Certificate using OpenSSL. If you are not sure about what is a self-signed Certificate and Certificate Authority , and CSR, refer these posts. If you son’t want to create the certificate on your machine, try this web OpenSSL tool to generate the certificate.What Is A PKI Certificate? What Are The Different Types Of PKI Certificates?
What Are The Different Types Of Certificate Authority?
What Is SSL/TLS? How SSL, TLS 1.2, And TLS 1.3 Differ From Each Other?
How To Generate A CSR For A Multi-Domain SSL Certificate Using OpenSSL?
The Most Useful OpenSSL Commands to Work With SSL Certificate
How To Create A Certificate Signing Request On A Linux Server?
Step By Step Procedure To Generate A CSR In Mac
Easiest Way to Create Certificates Using Web-Based GUI OpenSSL How to Generate A Self-Signed Certificate Using OpenSSL?To create the key pair, run this command in your terminal:$ openssl genrsa -out private.key 2048
This command will generate an RSA key pair with a length of 2048.Run this command to extract the public key from the key pair generated in step 1.
$ openssl rsa -in private.key -pubout -out public.key
The next step is to generate a Certificate Signing Request (CSR). This will be used by the certificate authority (CA) to create the self-signed certificate. To generate the CSR, run this command in your terminal:You will be prompted to enter a variety of information, such as the common name, organization name, organization unit, country code, email address, optional password, and many more. Enter the valid input it asks to generate the CSR. For example, the country name should be two char country codes. The common name should be the FQDN or IP to which you are going to get the certificate. The CSR is now ready for the CA to generate a self-signed certificate.
$ openssl req -new -key private.key -out certificate.csrWe suggest verifying the CSR generated before you submit it to the Certificate Authority. Run this command to verify the details of your CSR.
$ openssl req -text -in certificate.csr -noout -verifyFinally, generate the self-signed certificate using the private key and CSR. Run this command to generate the self-signed certificate on the terminal:
$ openssl x509 -in certificate.csr -out certificate.crt -req -signkey private.key -days 365MySQL versions 5.7 and above:Instead of creating the CSR or self-signed certificates using OpenSSL, you can use a utility called mysql_ssl_rsa_setup to create Certificates and key pair. Run this command to generate Certificates and key pair.sudo mysql_ssl_rsa_setup --uid=mysql
Store the Certificate files in the MySQL data directory.Move all the certificate files to /var/lib/mysql directory, which is the default data directory of MySQL.ls /var/lib/mysql -name '*.pem' -lsOutput 258930 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/client-cert.pem 258919 4 -rw-r--r-- 1 mysql mysql 451 May 3 16:43 /var/lib/mysql/public_key.pem 258925 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/server-key.pem 258927 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/server-cert.pem 258922 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/ca-key.pem 258928 4 -rw------- 1 mysql mysql 1675 May 3 16:43 /var/lib/mysql/client-key.pem 258924 4 -rw-r--r-- 1 mysql mysql 1107 May 3 16:43 /var/lib/mysql/ca.pem 258918 4 -rw------- 1 mysql mysql 1679 May 3 16:43 /var/lib/mysql/private_key.pem
Enable the SSL/TLS Connections on the MySQL ServerModren MySQL servers will fetch the certificate files from the MySQL data directory during the service start. No need to modify MySQL’s configuration to enable SSL.In such case, ensure the certificate files in the /var/lib/mysql directory and restart the MySQL service using this command:sudo systemctl restart mysqlIf not, you can go for manual configuration approch to enable the SSL/TLS connections on the MySQL server.Open the /etc/my.cnf file with your preferred text editor.
Add these lines underneath [mysqld] section of the my.cnf file:ssl ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=/var/lib/mysql/ca.pem ssl-cert=/var/lib/mysql/server-cert.pem ssl-key=/var/lib/mysql/server-key.pemAdd these lines underneath [client] section of the my.cnf file:[client] ssl-mode=REQUIRED ssl-cert=/var/lib/mysql/client-cert.pem ssl-key=/var/lib/mysql/client-key.pemmy.cnf file should look like this.[mysqld] max_connections=500 log-slow-queriesmax_allowed_packet=268435456
open_files_limit=10000
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0ssl ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=/var/lib/mysql/ca.pem ssl-cert=/var/lib/mysql/server-cert.pem ssl-key=/var/lib/mysql/server-key.pem[client] ssl-mode=REQUIRED ssl-cert=/var/lib/mysql/client-cert.pem ssl-key=/var/lib/mysql/client-key.pemSave your changes to the /etc/my.cnf file and exit your text editor.
Run the /scripts/restartsrv_mysql script to restart MySQL.
Verify the status of SSL/TLS on MySQL server upon the restarting the serviceRepet the Step 1 to verify the SSL/TLS status.SHOW VARIABLES LIKE '%ssl%';

Enforce SSL Connections for Remote Clients

While your MySQL server currently accepts SSL connections from clients, it might still allow unencrypted connections if requested. To increase security, enable the require_secure_transport option, which forces all connections to use SSL or a local Unix socket. Since Unix sockets are only accessible from within the server, remote users will only have the SSL connection option.

Update the MySQL Configuration File

Open the MySQL configuration file using your preferred text editor:

sudo nano /etc/mysql/<my.cnf>

Locate the two !includedir directives and add your configuration below these lines to override any conflicting settings in the additional configuration files. Create a [mysqld] section and set require_secure_transport to ON:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

require_secure_transport = ON

Enable MySQL to Listen for External Connections

By default, MySQL listens for connections from the localhost IP address (127.0.0.1). To allow external connections, configure MySQL to listen on an external IP address by adding the `bind-address` setting and pointing it to 0.0.0.0:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]

require_secure_transport = ON bind-address = 0.0.0.0

Note: You can alternatively set `bind-address` to your MySQL server’s public IP address, but make sure to update your `my.cnf` file if you migrate your database to another machine.

Save and close the file. If you used nano, press CTRL+X, Y, then ENTER.

Restart MySQL and Verify the New Settings

Restart MySQL to apply the new settings:

sudo systemctl restart mysql

To check that MySQL is listening on 0.0.0.0 instead of 127.0.0.1. The output should show MySQL listening on all available interfaces:

sudo netstat -antlp

Output 
Active Internet connections (only servers) 
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name     
tcp        0      0 0.0.0.0:3306      0.0.0.0:*               LISTEN      13317/mysqld         
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1293/sshd            
tcp6       0      0 :::22                   :::*                    LISTEN      1293/sshd

Configure Firewall Rules to Allow External Connections

Next, update your server’s firewall to allow MySQL connections. Add an exception to your `ufw` rules:

sudo ufw allow mysql

Output 
Rule added 
Rule added (v6)

Create a Dedicated Remote User in MySQL

To start, you’ll need to create a dedicated user that can connect to your MySQL server from a remote client machine. This user will be configured with the necessary permissions and security settings to ensure a secure connection. Follow these steps to create a remote user:

mysql -u root -p

At the prompt, create a new remote user using the CREATE USER command. You can name this user as you like, but we’ll use “mysql_user” in this example. Replace “your_mysql_client_IP” with your client machine’s IP address, and “password” with a strong password of your choice. Make sure to include the REQUIRE SSL clause for added security:

CREATE USER 'mysql_user'@'your_mysql_client_IP' IDENTIFIED BY 'password' REQUIRE SSL;

Grant Permissions to the Remote User

Now that you have created a remote user, it’s time to grant them the necessary permissions on the databases or tables they need access to. Here’s an example of how to create a new database and give your remote user access to it:

Create an example database:

CREATE DATABASE example;

Grant your remote user access to this database and all of its tables:

GRANT ALL ON example.* TO 'mysql_user'@'your_mysql_client_IP';

Apply the New Settings and Test the Connection

After granting the appropriate permissions, you need to apply the changes and test the remote connection. Follow these steps to do so:

Flush the privileges to apply the new settings immediately:

FLUSH PRIVILEGES;

Exit MySQL and return to the shell:

exit

At this point, your MySQL server is configured to allow connections from your remote user. To test the connection, you’ll need to install the mysql-client package on the client machine. Follow the instructions for your specific operating system to install the mysql-client package. Once installed, you can test the connection by running:

mysql -u mysql_user -p -h your_mysql_server_IP --ssl-mode=REQUIRED

Replace “your_mysql_server_IP” with the IP address of your MySQL server. If the connection is successful, you’ll be prompted to enter the password you created for the remote user.

Note: In a case, you don’t have MySQL client installed or not sure how to install MySQL client, run this command to install the MySQL client on your Linux server.

sudo apt update
sudo apt install mysql-client

Conclusion

In conclusion, setting up SSL/TLS for MySQL on Linux is a straightforward process. It’s important to understand the purpose of using this encryption protocol, as well as the difference between the certificate and key pair. Once you’ve checked if it’s already enabled and configured properly, you can then look into other protocols that can be used to secure your connections.

The most important part of setting up SSL/TLS for MySQL is making sure that there are no issues with the configuration. This can be done by running tests and checking logs regularly to make sure everything is running smoothly. If any errors occur, they should be addressed quickly in order to maintain a secure connection and keep data safe from malicious actors.

Overall, setting up and configuring SSL/TLS for MySQL on Linux is an easy process if all steps are followed correctly. I hope this guide has given you the necessary information required to configure SSL/TLS for MySQ; on Linux. Thanks for reading this tutorial post. Visit our social media page on Facebook, LinkedIn, Twitter, Telegram, Tumblr, Medium & Instagram, and subscribe to receive updates like this.

FAQ

What is SSL/TLS and why is it important for MySQL security?

SSL (Secure Sockets Layer) and its successor, TLS (Transport Layer Security), are cryptographic protocols that provide secure communication on the internet by encrypting data sent between two parties. Configuring SSL/TLS for MySQL helps ensure that sensitive data stored in databases remains protected against potential cyber attacks.

How can I check if SSL/TLS is already enabled on my MySQL server?

You can run a command to check whether your MySQL server has already been configured with SSL/TLS support, such as “SHOW VARIABLES LIKE ‘have_ssl’” or “openssl s_client -connect [your database host]:[port number]”.

What steps are involved in configuring SSL/TLS for MySQL on Linux systems?

The process of configuring SSL/TLS for MySQL involves generating a private key and a certificate signing request (CSR), obtaining an SSL certificate from a trusted provider, installing the certificate on your system, updating your MySQL configuration file with the new settings to make sure they take effect upon restart, then restarting your server.

Can I use self-signed certificates for my MySQL SSL setup?

Yes, you can use self-signed certificates but they will not provide the same level of trustworthiness as verified third-party certificates from trusted providers such as Verisign or Comodo.

Detailed Procedure To Set Up Your Own WordPress Hosting Platform On Ubuntu Or Debian Platform!
What Is SSL/TLS? How SSL, TLS 1.2, And TLS 1.3 Differ From Each Other?
Step-By-Step Procedure To Install SSL/TLS Certificate On Nginx Web Server!
Install SSL/TLS Certificate on the Nginx
What Is SSL/TLS