Configure SSL in Mysql server3 min read

We need your support!!
Other Amount:
techrunnr.com:
24-Hour Flash Sale. Courses from just ₹ 490.
Prabhin Prabharkaran Administrator
DevOps Engineer

He is a Technical professional. He is a person who loves to share tricks and tips on the Internet. He Posts what he does!

follow me

Mysql is a famous open source RDBMS. In this document we will be showing how to Configure SSL in Mysql server. By default SSL is disabled in mysql server.

Prerequisites.

Linux machine with openssl and mysql server installed.

 

Configuration

Login to mysql server and check SSL is enabled or disabled.

mysql -u root -p

 

mysql> SHOW VARIABLES LIKE ‘%ssl%’;

Output

+—————+———-+
| 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)

 

Now we will create certificate for server and client.
Execute the following commands to create the certificates and provide the necessary inputs as well.

mkdir /etc/mysql/ssl
cd /etc/mysql/ssl

 

Create CA certificate

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

 

Create server certificate, remove passphrase, and sign it. [server-cert.pem = public key, server-key.pem = private key]

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

 

Create client certificate, remove passphrase, and sign it. [client-cert.pem = public key, client-key.pem = private key]

 

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

After generating the certificates, verify certificates are correct

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

You should get output like this.

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

Enter the following statements in my.cnf

vi /etc/mysql/my.cnf

 

[client]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Restart the mysql service to take effect new configuration.

 

Verify the SSL configuration.

Login to mysql server

mysql -u root -p

mysql> SHOW VARIABLES LIKE ‘%ssl%’;

Output

+—————+———-+
| Variable_name | Value |
+—————+———-+
| have_openssl | YES          |
| have_ssl          | YES          |
| ssl_ca              |ca.pem      |
| ssl_capath     |                    |
| ssl_cert          | xx.pem      |
| ssl_cipher     |                    |
| ssl_crl            |                    |
| ssl_crlpath   |                    |
| ssl_key          |    xx.pem  |
+—————+———-+
9 rows in set (0.01 sec)

 

mysql> \s
————–
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 13 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.384

The output in green color shows SSL connection is enable for root user.

 

We need your support!!
Other Amount:
techrunnr.com:
#1
#2
#3
Questions Answered
Articles Written
Overall Points

Prabhin Prabharkaran

He is Technical professional. He is a person who loves to share tricks and tips on the Internet. He Posts what he does!!

You may also like...

Leave a Reply