DEV Community

Cover image for How To Secure MySQL 8 with SSL/TLS on Ubuntu 20.04
Arctype Team for Arctype

Posted on • Originally published at arctype.com

How To Secure MySQL 8 with SSL/TLS on Ubuntu 20.04

This tutorial will show you how to secure MySQL connections with SSL/TLS on Ubuntu 20.04.

Prerequisites

• A server running Ubuntu 20.04.
• A root password is set up on your server.

Install MySQL 8 Server

By default, MySQL server version 8 is included in the Ubuntu 20.04 default repository. Run the following command to install it.

apt install mysql-server -y

After the installation, verify the installed version of MySQL using the command below:

mysql -V

Sample output:

mysql Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Next, start the MySQL service and enable it to start at system reboot:

systemctl start mysql
systemctl enable mysql
Enter fullscreen mode Exit fullscreen mode

Set a MySQL Root Password

By default, the MySQL root password is not set. So it is recommended to set the MySQL root password for security reasons.

Run the following command to set the MySQL root password:

mysql_secure_installation

You will be asked several questions as shown below:

Press y|Y for Yes, any other key for No: Y
    Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
    New password: 

    Re-enter new password: 

    Estimated strength of the password: 50 
    Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
    Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
    Disallow root login remotely? (Press y|Y for Yes, any other key for No) :  
    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Enter fullscreen mode Exit fullscreen mode

At this point, MySQL is secured, and the root password is set. You can now proceed to the next step.

Check MySQL SSL/TLS Status

First, you will need to verify whether the SSL/TLS is enabled in MySQL.

To do so, log in to MySQL with the following command:​

mysql -u root -p

Once you are logged in, run the following command to check the SSL/TLS status:​

mysql> SHOW VARIABLES LIKE '%ssl%';

Sample output:

+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_capath                   |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_cipher                   |                 |
| mysqlx_ssl_crl                      |                 |
| mysqlx_ssl_crlpath                  |                 |
| mysqlx_ssl_key                      |                 |
| performance_schema_show_processlist | OFF             |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+
25 rows in set (0.01 sec)
Enter fullscreen mode Exit fullscreen mode

​As you can see, both have_openssl and have_ssl values are disabled.

You can also verify the current connection status with the following command:​

mysql> \s

Sample output:

--------------
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:      10
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.25-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Binary data as:     Hexadecimal
Uptime:         1 min 49 sec

Threads: 2  Questions: 15  Slow queries: 0  Opens: 147  Flush tables: 3  Open tables: 66  Queries per second avg: 0.137
--------------
Enter fullscreen mode Exit fullscreen mode

As you can see, the SSL connection is not in use in the current connection.​

Now, exit from the MySQL shell with the following command:​

mysql> EXIT;

Create SSL/TLS Certificates

Next, you will need to generate SSL/TLS certificate and key file in your server. You can create them using the mysql_ssl_rsa_setup script.​

Now, run this utility as shown below to generate the SSL/TLS certificate and key.​

mysql_ssl_rsa_setup --uid=mysql

This will generate and save all certificate and key files in MySQL's data directory located at /var/lib/mysql. You can check them with the following command:​

ls -l /var/lib/mysql/*.pem

You should see all files in the following output:

-rw------- 1 mysql mysql 1680 Jul 10 07:45 /var/lib/mysql/ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 10 07:45 /var/lib/mysql/ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 10 07:45 /var/lib/mysql/client-cert.pem
-rw------- 1 mysql mysql 1680 Jul 10 07:45 /var/lib/mysql/client-key.pem
-rw------- 1 mysql mysql 1680 Jul 10 07:45 /var/lib/mysql/private_key.pem
-rw-r--r-- 1 mysql mysql  452 Jul 10 07:45 /var/lib/mysql/public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jul 10 07:45 /var/lib/mysql/server-cert.pem
-rw------- 1 mysql mysql 1680 Jul 10 07:45 /var/lib/mysql/server-key.pem
Enter fullscreen mode Exit fullscreen mode

You can now use those files to enable the use of SSL on your MySQL server.

Enable SSL Connections on MySQL

To enable the SSL/TLS in MySQL. Restart the MySQL service using the command below:​

systemctl restart mysql

Now, connect to the MySQL shell and check the status with the following command:

mysql -u root -p --ssl-mode=required
mysql> SHOW VARIABLES LIKE '%ssl%';
Enter fullscreen mode Exit fullscreen mode

You should see that both have_openssl and have_ssl variables are now enabled.

+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_capath                   |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_cipher                   |                 |
| mysqlx_ssl_crl                      |                 |
| mysqlx_ssl_crlpath                  |                 |
| mysqlx_ssl_key                      |                 |
| performance_schema_show_processlist | OFF             |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+
Enter fullscreen mode Exit fullscreen mode

You can also check the current connection status with the following command:​

mysql> \s

Sample output:

-------------------
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Connection id:      8
Current database:   
Current user:       root@localhost
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.25-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Binary data as:     Hexadecimal
Uptime:         31 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 134  Flush tables: 3  Open tables: 53  Queries per second avg: 0.193
-------------------
Enter fullscreen mode Exit fullscreen mode

The above output indicates that your current MySQL session uses an SSL connection.

Enable Remote and Secure Connection in MySQL

By default, MySQL does not allow login from the remote host and allows an unsecured connection. So you will need to configure MySQL to allow connection and accept only secure connections.​

To do so, edit the MySQL main configuration file:

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

Add / Modify the following lines:

[mysqld]
require_secure_transport = ON
bind-address = 0.0.0.0
Enter fullscreen mode Exit fullscreen mode

Save and close the file, then restart the MySQL service to apply the configuration changes:​

systemctl restart mysql

Next, create a new MySQL user and database for remote MySQL clients.

mysql -u root -p
mysql> CREATE DATABASE remotedb;
mysql> CREATE USER 'remoteuser'@'mysql-client-ip' IDENTIFIED BY 'yourpassword' REQUIRE SSL;
Enter fullscreen mode Exit fullscreen mode

Next, grant all the privileges to the remotedb using the following command:​

mysql> GRANT ALL PRIVILEGES ON remotedb.* TO 'remoteuser'@'mysql-client-ip' WITH GRANT OPTION;

Next, flush the privileges and exit from the MySQL with the following command:

mysql> FLUSH PRIVILEGES;
mysql> EXIT;
Enter fullscreen mode Exit fullscreen mode

Verify SSL Connection from Remote MySQL Client

At this point, the MySQL server is configured to accept only secure connections from the remote host. Now, it's time to test it.​

First, log in to the client machine and connect your MySQL server with the following command:​

mysql -h mysql-server-ip -u remoteuser -p

Once you are connected, verify the SSL status with the following command:​

mysql> \s

You should see the following output:

-------------------
mysql  Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Connection id:      9
Current database:   
Current user:       remoteuser@mysql-client-ip
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.25-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:   10
Connection:     mysql-server-ip via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Binary data as:     Hexadecimal
Uptime:         15 min 31 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 147  Flush tables: 3  Open tables: 66  Queries per second avg: 0.011
-------------------
Enter fullscreen mode Exit fullscreen mode

The above output indicates that your current MySQL session uses an SSL connection.

To confirm that MySQL server only accepts the secure connection and rejects insecure, try to connect to the remote MySQL server using the string --ssl-mode=disabled:

mysql -h your-mysql-server-ip -u remoteuser -p --ssl-mode=disabled

You should see the following error:​

ERROR 1045 (28000): Access denied for user 'remoteuser'@'mysql-client-ip' (using password: YES)

That means your MySQL server only accepts the secure connection.​

Conclusion

Congratulations! You have successfully secured MySQL with SSL/TLS connections on Ubuntu 20.04 server. Now, whenever you connect to the MySQL server, your data will be encrypted.

Discussion (0)