Securing MySQL connections using SSL on Ubuntu

MySQL requires no introduction, being one of the most popular open source database management systems available today. It is widely used by many popular websites, including Wikipedia. Its popularity stems from the plethora of functions and features it offers to its users. If you connect to your MySQL server remotely, you can ensure the security of the MySQL connection by implementing SSL/TLS.

This guide will walk you through the process of enabling SSL/TLS in your MySQL server. Enabling SSL/TLS will allow for the secure transfer of data between the server and client. If you are not familiar with SSL/TLS, allow me to provide you with a brief explanation.

SSL stands for Secure Socket Layer, while TLS stands for Transport Layer Security. Both terms are synonymous with each other, as SSL/TLS simply encrypts the data being exchanged between the server and client. In the case of MySQL, your MySQL server acts as the server, while your local machine functions as the client. By enabling SSL/TLS security, you can securely transmit data to your server.

Now that you understand what we aim to accomplish, we can proceed with the actual tutorial. Here are the prerequisites:

Prerequisites

All you need is a server with MySQL already installed. If possible, avoid experimenting with the actual server that is running in a production environment. Click here to deploy a brand new Linux VPS to test SSL/TLS before implementing it on your production environment.

Once you have a server with MySQL installed, we can move on to the first step. Securing MySQL connections with SSL/TLS is not overly complicated; simply understand the steps involved and you will be able to do it on your own in the future.

Verify Current SSL Status

It is possible that SSL/TLS encryption is already enabled on your server. We can check if it is already active by executing the following command in your console to log in to your MySQL server.

$ mysql -uroot -p;

Enter the correct MySQL server and you will be logged in. Once logged in, execute the following command to check the current SSL status. The command and expected output are provided below.

mysql> s
--------------
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Connection id:5
Current database:
Current user:root@localhost
SSL:Not in use
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:latin1
Db     characterset:latin1
Client characterset:utf8
Conn.  characterset:utf8
UNIX socket:/var/run/mysqld/mysqld.sock
Uptime:3 min 38 sec

Threads: 1  Questions: 17  Slow queries: 0  Opens: 113  Flush tables: 1  Open tables: 106  Queries per second avg: 0.077
--------------

If you see Not in Use corresponding to SSL in the status command, SSL is not enabled. You can also run the following query to check the status.

mysql> SHOW VARIABLES LIKE '%SSL%';
+---------------+----------+
| 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)

If you receive similar outputs, SSL is not active on your MySQL server and your MySQL connections are not secure. Let’s see how to secure MySQL connections using SSL.

Create and Enable SSL Certificate

Creating an SSL certificate for MySQL is the simplest task in this guide. If you are using Ubuntu 16.04 or newer, you only need to execute a single command to generate the SSL certificate and key files for your MySQL server.

Execute the following command in your console to generate the SSL certificate and keys for MySQL:

$ sudo mysql_ssl_rsa_setup --uid=mysql

It will take a few seconds to generate the required files. Note that this is a self-signed SSL certificate. The SSL certificate and key files are stored in the /var/lib/mysql directory. If you want to view the files created by the previous command, execute the following command in your console:

$ ls -la  /var/lib/mysql | grep '.pem'

It is important to check if the files were created successfully. Sometimes, you may encounter an error when executing the command to create the SSL certificate, even if the certificate files are created successfully.

Once completed, we can enable SSL encryption in our MySQL server. To enable the certificate, we need to restart the MySQL service. Execute the following command to do so:

$ sudo service mysql restart

That’s it! Your MySQL connections are now secured with SSL. Let’s verify this and then learn how to require users to connect securely to the MySQL server. To verify if SSL is actually working, log in to your MySQL server and specify the host as follows:

$ mysql -uroot -p -h127.0.0.1;

Once logged in, execute the same s command and you should see the following output:

mysql> s
--------------
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

.
Current user:root@localhost
SSL:Cipher in use is DHE-RSA-AES256-SHA
.
Connection:127.0.0.1 via TCP/IP
.
TCP port:3306
.
--------------

Similarly, if you run the other command to select variables with ssl like mentioned above, it will show YES instead of DISABLED. Finally, we can proceed and set up a user account with remote access and SSL required.

Strictly Require SSL and Enable Remote Connections

In this step, we will update the MySQL configuration file to modify the bind-address directive and make SSL connection mandatory. Execute the following command to open the MySQL configuration file in edit mode:

$ sudo nano /etc/mysql/my.cnf

Once open, add the following lines at the end of the file:

[mysqld]
bind-address = 0.0.0.0
require_secure_transport = ON

The bind-address directive will enable remote connections, while require_secure_transport will enforce the use of SSL connections. Restart the MySQL server using the following command to apply the changes:

$ sudo service mysql restart

Now, let’s create a user that can connect remotely to our MySQL server.

Create User with Remote Access

In this step, we will create a user with remote access to the MySQL server. However, our MySQL server will only allow connections if they are secured with SSL/TLS. We will create a database, a user, and grant all privileges on that database to the user.

mysql> CREATE USER 'exampleUser'@'REMOTE_IP' IDENTIFIED BY 'PASSWORD' REQUIRE SSL;

The REQUIRE SSL clause we added to the query will force the user to connect using SSL only. Let’s create a database and grant all privileges on that database to our user.

mysql> CREATE DATABASE example;
mysql> GRANT ALL PRIVILEGES ON example.* TO 'exampleUser'@'REMOTE_IP';

Don’t forget to replace the placeholders and example names with the actual information. Once the user is created, you can test the connection by connecting from the IP address you used when creating the user. Use the following command to connect to the remote MySQL server:

user@client:~$ mysql -uexample -p -h REMOTE_IP;

After connecting to the server, execute the s command and check the SSL status, just as we did before. That’s all you need to do to enable SSL on MySQL connections.

Conclusion: When connecting to your MySQL server remotely, it is essential to enable SSL/TLS to enhance the security of your data. It only requires a few commands and configuration edits. For advanced users, you can transfer client keys to the client so that both the server and client have a single Certificate Authority (CA) they can trust, which further enhances the security of your connections.

If you need assistance enabling SSL/TLS on MySQL, feel free to let us know in the comment section below. WebHostingPeople users can also contact our customer support for assistance!