How to Enable SSL/TLS for MySQL in Ubuntu

MySQL allows users to securely connect to databases using SSL/TLS certificates. In this article we will look at MySQL SSL Configuration – how to enable SSL/TLS for MySQL in Ubuntu.

How to Enable SSL/TLS for MySQL

Here are the steps to Setup SSL connections in MySQL.

1. Install MySQL

Open terminal and run the following commands to install MySQL.

$ sudo apt-get update
$ sudo apt-get install mysql-client

Bonus Read : MySQL Change Collation of All Tables

2. Check SSL Status

Log into MySQL as root

$ mysql -uroot -p

You will be prompted for root password. After logging in, enter the following command to find present SSL/TLS 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       |          |
+---------------+----------+

You will find that have_openssl and have_ssl both have DISABLED value. It means that SSL is not enabled in MySQL.

Bonus Read : Top Database Blogs to Follow

3. Generate SSL/TLS certificates

Next, we need to generate SSL/TLS certificates and point MySQL server to their locations. Typically, people use openssl to generate SSL certificates and then move them to /var/lib/mysql which is the default location of SSL files for MySQL server.

However, MySQL 5.7 and later versions already ship with a utility mysql_ssl_rsa_setup to simplify this process. It generates SSL certificates and stores them at /var/lib/mysql.

Since we need mysql process to be able to read these files, we will make mysql as the owner of these files, as shown below.

Run the following command to generate SSL certificates

$ sudo mysql_ssl_rsa_setup --uid=mysql

Generating a 2048 bit RSA private key ...................................+++ .....+++ 
writing new private key to 'ca-key.pem' 
----- 
Generating a 2048 bit RSA private key ......+++ .................................+++ 
writing new private key to 'server-key.pem' 
----- 
Generating a 2048 bit RSA private key ......................................................+++ .................................................................................+++ 
writing new private key to 'client-key.pem' 
-----

You can check the generated files by running the following command

$ ls -all /var/lib/mysql/*.pem

You will see something like the following

256740      4 -rw-r--r--   1 mysql    mysql        1078 May 1 12:24 /var/lib/mysql/server-cert.pem
   256735      4 -rw-------   1 mysql    mysql        1675 May 1 12:24 /var/lib/mysqlsql/ca-key.pem
   256739      4 -rw-r--r--   1 mysql    mysql         451 May 1 12:24 /var/lib/mysqlsql/public_key.pem
   256741      4 -rw-------   1 mysql    mysql        1689 May 1 12:24 /var/lib/mysqlsql/client-key.pem
   256737      4 -rw-r--r--   1 mysql    mysql        1064 May 1 12:24 /var/lib/mysqlsql/ca.pem
   256743      4 -rw-r--r--   1 mysql    mysql        1048 May 1 12:24 /var/lib/mysqlsql/client-cert.pem
   256736      4 -rw-------   1 mysql    mysql        1625 May 1 12:24 /var/lib/mysqlsql/private_key.pem
   256738      4 -rw-------   1 mysql    mysql        1615 May 1 12:24 /var/lib/mysqlsql/server-key.pem

These are the SSL certificate file and private key pairs for certificate authority, MySQL Server and MySQL client.

Bonus Read : Top MySQL Workbench Alternatives

4. Enable SSL Connections in MySQL Server

Next, restart MySQL Server to enable SSL/TLS in MySQL.

$ sudo systemctl restart mysql 

MySQL Server will automatically look for SSL certificate files in /var/lib/mysql when it starts. So you do not have to manually specify their location in MySQL configuration file.

Bonus Read : How to Enable MySQL Query Cache

5. Verify SSL connection

Log into MySQL as before (step 2) and run the following command.

SHOW VARIABLES LIKE '%ssl%';
Output
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+

Now you will find have_openssl and have_ssl variables are YES. You will also see that ssl_ca, ssl_cert and ssl_key are populated with appropriate values.

You can also check your connection details.

mysql> \s

-------------- 
. . . 
SSL: Cipher in use is DHE-RSA-AES256-SHA 
. . . 
Connection: 127.0.0.1 via TCP/IP 
. . . 
--------------

6. Configure SSL for remote connection (Optional)

If your MySQL server has remote incoming connections, then you can enable SSL for remote connection, and even make it mandatory by modifying the MySQL server configuration file.

Open terminal and run the following command to open MySQL configuration.

$ sudo vi /etc/mysql/my.cnf

Add [mysqld] block of code as shown, below the two !includedir statements.

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

[mysqld] 
# Require clients to connect either using SSL 
# or through a local socket file 
require_secure_transport = ON
bind-address = 0.0.0.0

In the last line, we have set bind-address to 0.0.0.0 to allow remote connections. You need not add it if you have already enabled remote connections.

Restart MySQL Server to apply changes. From now on, MySQL will require SSL.

For remote connections, please don’t forget to open port 443, instead of the default 3306. This is because SSL connections happen over port 443, and not 3306.

Hopefully, this article will help you enable SSL/TLS connection in MySQL.