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
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
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)
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
--------------
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
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%';
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 |
+-------------------------------------+-----------------+
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
-------------------
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
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;
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;
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
-------------------
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.
Top comments (0)