How to Setup Self Signed SSL/TLS on MySQL

This brief tutorial shows students and new users how to enable self-signed SSL/TLS certificates and connect to MySQL Server via SSL connection on Ubuntu Linux 20.04 | 18.04.

By default when you install MySQL server, it will only allow connections from the local system for users with the correct credentials regardless of transport protocol.

If you want to add another layer of security, you can enable SSL/TLS certificate settings and force all users to connect securely.

For this tutorials, we’re going to be creating a self signed certificate to configure with MySQL.

To get started with configuring MySQL with SSL/TLS certificates, follow the steps below:

Create SSL Cert

Since we’re creating self-signed certificates, simply run the commands below to create a directory where the cert files will be created.

After creating the directory, change into it and begin creating your self signed certificates.

sudo mkdir /var/lib/mysql/pki
cd /var/lib/mysql/pki

Now that the directory is created and you have changed into it, run the commands below to create the CA certificate and private key.

Create CA key and CA cert

sudo openssl genrsa -out ca-key.pem 2048
sudo openssl req -new -x509 -nodes -days 365 -key ca-key.pem -out ca-cert.pem

The commands above will generate a 2048 bit key length and create a new 1 year (365 days) private key.

You may increase the key length and expiration date if you want for the private key.

While creating the private key, you’ll be prompted for details of the key you’re generating.

Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:MN
Locality Name (eg, city) []:BP
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

Next, create a private key for the server. You’ll be prompted as above. Type the details that you want to include with the cert.

Create server private key

sudo openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem

When you’re done above, export the server’s private key to an RSA-type key using the commands below:

sudo sudo openssl rsa -in server-key.pem -out server-key.pem

After all the above, run the commands below to generate a SSL cert using the commands below:

Generate SSL/TLS cert

sudo openssl x509 -req -in server-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

In the directory, you should have these files:

  • ca-cert.pem
  • ca-key.pem
  • server-cert.pem
  • server-key.pem
  • server-req.pem

Configure MySQL SSL/TLS Connection

Now that you’re created a self signed certificate, go to MySQL and configure to connect over SSL/TLS.

Then make MySQL user owner of the directory above.

sudo chown -R mysql. /var/lib/mysql/pki

When you’re done, open MySQL configuration file.

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

Then add the highlighted lines to enable SSL/TLS

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
#port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking
ssl-ca=/var/lib/mysql/pki/ca-cert.pem
ssl-cert=/var/lib/mysql/pki/server-cert.pem
ssl-key=/var/lib/mysql/pki/server-key.pem

require_secure_transport = ON
.....................................
.....................................

After adding the lines above, restart MySQL.

The require_secure_transport = ON option forces all users to connect over SSL.

sudo systemctl restart mysql

Next, connect to MySQL via SSL and verify SSL/TLS are loaded by running the command below:

sudo mysql -u root -p --ssl-mode=required

Then run the query below:

show variables like '%ssl%'; 

It should show similar lines as below:

+-------------------------------------+------------------------------------+
 | 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                              | /var/lib/mysql/pki/ca-cert.pem     |
 | ssl_capath                          |                                    |
 | ssl_cert                            | /var/lib/mysql/pki/server-cert.pem |
 | ssl_cipher                          |                                    |
 | ssl_crl                             |                                    |
 | ssl_crlpath                         |                                    |
 | ssl_fips_mode                       | OFF                                |
 | ssl_key                             | /var/lib/mysql/pki/server-key.pem  |
 +-------------------------------------+------------------------------------+

The “have_ssl” in MySQL says whether SSL support is available, while “have_openssl” says, specifically, whether OpenSSL is compiled in.

So, if you have MySQL built with YaSSL, have_ssl will be YES, while have_openssl will be NO.

To connect via the client over SSL/TLS, run the commands below:

sudo mysql --ssl-mode=REQUIRED

Then show the cipher being used:

show status like 'ssl_cipher';

It should display similar lines as below:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)

After enabling SSL/TLS, you should begin creating users and requiring SSL/TLS to login.

create user dbuser identified by 'password_here' require ssl; 

Exit and you’re done.

To force all connections to use SSL regardless of what a user configuration is, run the SQL statement below. This example forces the root user to use SSL before connecting.

UPDATE mysql.user SET ssl_type = 'ANY' WHERE user = 'root';
FLUSH PRIVILEGES;

Check MySQL status to view current configuration

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

Connection id:		12
Current database:	
Current user:		[email protected]
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.23-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:			5 min 10 sec

Conclusion:

This post showed you how to configure MySQL server to connect over SSL/TLS. If you find error above, please use the form below to report.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.