Secure MySQL Connections with SSL on Centos 7

In this article, we want to teach you to Secure MySQL Connections with SSL on Centos 7.

MySQL is a database management system that allows you to manage relational databases. It is open-source software backed by Oracle.

It is pretty easy to master in comparison with other database software like Oracle Database, or Microsoft SQL Server.

Also, it can run on various platforms UNIX, Linux, Windows, etc. You can install it on a server or even on a desktop. Besides, MySQL is reliable, scalable, and fast.

Secure Sockets Layer (SSL) was the most widely deployed cryptographic protocol to provide security over internet communications before it was succeeded by TLS (Transport Layer Security) in 1999.

SSL provides a secure channel between two machines or devices operating over the internet or an internal network.

what you read in this post?

How To Secure MySQL Connections with SSL on Centos 7

How To Secure MySQL Connections with SSL on Centos 7

To complete this guide, you just need a server with MySQL already installed. You can follow our article How To Install MySQL on Centos 7.

If possible, do not try new things on the actual server that is running in the production environment.

Now you can follow the steps below to secure your MySQL connections with SSL.

Generate New SSL Certificate Files for MySQL on Centos 7

By default, MySQL has its own SSL certificate files in the ‘/var/lib/mysql’ directory.

In this guide, you will learn to generate your own SSL Certificate files with OpenSSL, and then configure them with MySQL.

At this point, you need 3 certificates: CA Certificate, Server Certificate and Key, Client Certificate and Key.

You can create them with OpenSSL.

First, create a directory for the SSL certificate file and switch to it with the following commands:

# mkdir -p /etc/certs
# cd /etc/certs

Then, use the commands below to generate a new CA certificate ca.pem file:

# openssl genrsa 2048 > ca-key.pem
# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

You will be asked to enter information that will be incorporated into your certificate request.

Important Note: The CA certificate info must be different from the client and server info.

After that, you need to generate the server certificates with the following commands. By generating new certificate files, you will remove the passphrase and sign them with the CA certificate.

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
# openssl rsa -in server-key.pem -out server-key.pem
# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

At this point, you need to generate the client certificates. The MySQL server will only accept remote connections from the client who has these certificate files. To do this, run the commands below:

# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
# openssl rsa -in client-key.pem -out client-key.pem
# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

All the certificates have been generated. You can verify them by using the following command:

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

In your output you should see:

Output
server-cert.pem: OK
client-cert.pem: OK

Here you need to change the owner of the certs directory to the ‘mysql’ user and change permissions of all key files with the following commands:

# chown -R mysql:mysql /etc/certs/
# chmod 600 client-key.pem server-key.pem ca-key.pem

Enable SSL for MySQL

At this point, you can enable the SSL for your MySQL. First, you need to back up the default certificate files to the new backup directory.

Create a new backup directory ‘mysql-certs‘ for all certificates with the following command:

mkdir -p ~/mysql-certs/

Then, switch to the ‘/var/lib/mysql’ directory and move all default certificates to the backup directory with the commands below:

# cd /var/lib/mysql/
# mv ca.pem ca-key.pem ~/mysql-certs/
# mv server-key.pem server-cert.pem ~/mysql-certs/
# mv client-key.pem client-cert.pem ~/mysql-certs/
# mv private_key.pem public_key.pem ~/mysql-certs/

Now restart the MySQL service on Centos 7 with the following command:

systemctl restart mysqld

Next, check SSL from the MySQL console. Login to mysql with the new password:

mysql -u root -p

Run the command below to see the SSL status for MySQL:

SHOW GLOBAL 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)

You will see SSL status is ‘DISABLED‘.

Also, you can use the command below to see the root user has been connected without SSL.

STATUS;
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 min 24 sec
Threads: 2 Questions: 6 Slow queries: 0 Opens: 134 Flush tables: 3 Open tables: 53 Queries per second avg: 0.071
--------------

At this point, you can enable the SSL connection for MySQL on Centos 7. Open the MySQL configuration file with your favorite text editor, here we use vi:

vi /etc/my.cnf

In the ‘[mysqld]‘ section, add the configuration below:

# Type your own certificates directory
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem
ssl-key=/etc/certs/server-key.pem

When you are done, save and close the file.

Restart MySQL service on Centos 7 to apply the changes:

systemctl restart mysqld

Now log in to the MySQL console again to check the SSL status:

mysql -u root -p

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
----------------------------------------------------------------------------------
+-------------------------------------+-----------------+
| Variable_name                       |      Value      |
+-------------------------------------+-----------------+
| have_openssl                        |   YES           |
| have_ssl                            |   YES           |
| ssl_ca                              | /etc/certs/ca.pem|
| ssl_capath                          |                 |
| ssl_cert                            |/etc/certs/server-cert.pem|
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_key                             |/etc/certs/server-key.pem|
+-------------------------------------+-----------------+
9 rows in set (0.01 sec)

SSL is enabled for MySQL connections, but if you run the status command, you will see that your root user is still not connected with the SSL connection.

Again you need to edit the MySQL configuration file to force all local user connections with SSL.

vi /etc/my.cnf

Add the following contents at the end of the file:

[client]
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/client-cert.pem
ssl-key=/etc/certs/client-key.pem

When you are done, save and close the file.

Restart MySQL service:

systemctl restart mysqld

Connect to the MySQL again and check the connection and SSL enabled:

# mysql -u root -p
# mysql> STATUS;

--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 8
Current database:
Current user: root@localhost
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 32 sec
Threads: 2 Questions: 6 Slow queries: 0 Opens: 134 Flush tables: 3 Open tables: 53 Queries per second avg: 0.187
--------------

You will see that SSL has been enabled and the root user has been connected with the SSL.