Secure MySQL Connection

Currently this feature is only supported on Windows.

Overview

MySQL supports secure (encrypted) connections between MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. This article explains how to configure your MySQL server and TCAdmin.

How to configure the MySQL Server

The following instructions are provided for your convenience. We do not provide support for configuring ssl on your MySQL server.

For detailed instructions read the MySQL reference manual: http://dev.mysql.com/doc/refman/5.0/en/creating-ssl-certs.html

Install OpenSSL

To know if your server already has OpenSSL installed execute "openssl" from ssh or from a command prompt. If you see "OpenSSL>" it is already installed.

CentOS 5/6, Fedora 15, Redhat 5

yum install openssl

Debian 6, Ubuntu 10/11/12

apt-get install openssl

Windows

OpenSSL for Windows can be downloaded from http://slproweb.com/products/Win32OpenSSL.html

Create the Server Certificates

Important: Execute the commands line by line. Execute them all at once will not create all certificate files.

After executing the following commands these files should exist in C:\Certificates or /var/local/ssl/certs.

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

Linux

mkdir -p /var/local/ssl/certs
cd /var/local/ssl/certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 9000  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Windows

Open a command prompt as administrator. If you installed the 64bit version of OpenSSL change C:\OpenSSL-Win32 to C:\OpenSSL-Win64.

cd \
mkdir Certificates
cd Certificates
C:\OpenSSL-Win32\bin\openssl genrsa 2048 > ca-key.pem
C:\OpenSSL-Win32\bin\openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem
C:\OpenSSL-Win32\bin\openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
C:\OpenSSL-Win32\bin\openssl rsa -in server-key.pem -out server-key.pem
C:\OpenSSL-Win32\bin\openssl x509 -req -in server-req.pem -days 9000  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Update the MySQL Configuration and Restart

Linux

Edit /etc/my.cnf.

Find this line:

[mysqld]

Under that line add these lines:

ssl-ca=/var/local/ssl/certs/ca-cert.pem
ssl-cert=/var/local/ssl/certs/server-cert.pem
ssl-key=/var/local/ssl/certs/server-key.pem

Then restart the MySQL service.

Windows

Edit the my.ini in your MySQL installation folder. For example C:\Program Files\MySQL\MySQL Server 5.5\my.ini.

Find this line:

[mysqld]

Under that line add these lines:

ssl-ca=C:/Certificates/ca-cert.pem
ssl-cert=C:/Certificates/server-cert.pem
ssl-key=C:/Certificates/server-key.pem

Then restart the MySQL service.

Test your Secure Connection

Execute this command. On Windows you might have to enter the full path to mysql.exe. Replace ROOT_PASSWORD with the MySQL server's root password.

mysql -uroot -pROOT_PASSWORD --ssl-key=

After a successful connection execute this command:

status

Check the value next to "SSL:". If it says "Cipher in use is DHE-RSA-AESXXX-SHA" the connection is secure.