Activate SSL connection in MySQL

Secure connection? Today we will explore an interesting task about activating and using SSL connection in MySQL. We will provide all necessary scripts and commands to re-play all steps. So assume that we have CentOS 6.5 with OpenSSL and MySQL already installed:

Our test host is a Virtual Machine with static ip address: 192.168.1.77. The rest of contents of this article is quite straight just follow commands:

### Create Environment ###
[root@linuxsrv3 ~]# cd /etc/
[root@linuxsrv3 etc]# mkdir mysql_ssl_certs
[root@linuxsrv3 etc]# ls -ld mysql_ssl_certs/
drwxr-xr-x. 2 root root 4096 2014-05-27 17:33 mysql_ssl_certs/
### Create CA certificates ###
[root@linuxsrv3 etc]# cd mysql_ssl_certs/
# 1
[root@linuxsrv3 mysql_ssl_certs]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus ..............+++ .......................+++ e is 65537 (0x10001)
# 2 [root@linuxsrv3 mysql_ssl_certs]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem
You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
# 3 ### Create server certificate #

server-cert.pem = public key, server-key.pem = private key

[root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key ...+++ ..........................+++ writing new private key to 'server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student
# 4 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key
# 5 [root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key
# 6 ### Create client certificate ###

client-cert.pem = public key, client-key.pem = private key

[root@linuxsrv3 mysql_ssl_certs]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key .............................................................+++ ......................+++ writing new private key to 'client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank.
----- Country Name (2 letter code) [XX]:AZ
State or Province Name (full name) []:BAKU
Locality Name (eg, city) [Default City]:BAKU
Organization Name (eg, company) [Default Company Ltd]:Student
Organizational Unit Name (eg, section) []:Student
Common Name (eg, your name or your server's hostname) []:shahriyar
Email Address []:rzayev.sehriyar@gmail.com
Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:Sh@rzstt01 An optional company name []:Student
# 7 [root@linuxsrv3 mysql_ssl_certs]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
# 8
[root@linuxsrv3 mysql_ssl_certs]# openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok subject=/C=AZ/ST=BAKU/L=BAKU/O=Student/OU=Student/CN=shahriyar/emailAddress=rzayev.sehriyar@gmail.com Getting CA Private Key

That’s all. As you see our client Connection over ssluser is using SSL connection. In Final stage of our article o want to show some coding examples for developers. I use Python 2.7.6 with official mysql-connector package installed. To make article little i paste this codes on gist. So here is the first file is mysql_connection_without_ssl.py: mysql_connection_without_ssl.pyNote: There is an infinite loop inside code just for showing what is unsecured connection like.
Run this Python script(it will select from country table) and on another terminal run ngrep to read packages:

As you see we can see what is retrieved from our select statement. And know lets try The another script which uses SSL: mysql_connection_with_ssl.py Run This Python script. To do this you will have to copy certificates from remote server to local and ofcourse edit my.cnf on local machine to reflect to new certificates. So as you see from Python code we specify paths where certificates reside on our local machine.

Thank you for note about other available tools.
Yes i have read about a new introduced tool for this purpose in MySQL 5.7.
i have never heard about ‘mysslgen’, but definitely from know i will check it, and maybe you will see another article about this tool 🙂