mysql ssl tls

Setting up MySQL over TLS

MySQL supports session encryption using TLS. Here’s how to configure your server and client to use it.

On The Server

To start, you will need a server SSL certificate file and a key file, and a file containing the certificate that signed your cert. In the MySQL configuration file /etc/my.cnf or /etc/mysql/my.cnf, add these three lines to both the [mysqld] and [mysqld_safe] sections,

ssl-ca=SIGNING-CERT-FILE
ssl-cert=CERT-FILE
ssl-key=KEY-FILE

Restart your server so this new configuration will take effect.

Using MySQL Command-Line Client

You have two choices here.

Edit the my.cnf file

Add the following line under the [client] section

ssl-ca=SIGNING-CERT-FILE

and all subsequent network traffic when using the mysql command-line client will encrypted. The SIGNING-CERT-FILEis the same as above.

Use command-line option
You don’t need to edit the my.cnf file if you run the client like this

mysql --ssl-ca=SIGNING-CERT-FILE ...

Using Perl as a Client

First, you will need to configure my.cnf as above in the section “Using MySQL Command-Line Client”. Below is an example of how to call Perl’s DBI packag using DBI->connect with the mysql_ssl option

$handle = DBI->connect(
   "dbi:mysql:DB_NAME:DB_HOST:mysql_ssl=1", 
   DB_USER, 
   DB_PASSWORD
);

Replace DB_NAME, DB_HOST, DB_USER, DB_PASSWORD with the database name, host, user and user’s password. Warning: If the database does not support SSL, the connection will still succeed, but it will be plain text.

Using Python as a Client

You will need a copy of cert for the signing authority of the MySQL server’s cert, as in previous examples. The difference here is that Python will read the signing authority cert directly, and not via the MySQL my.cnf file. We use the Python’s MySQLdb module to connect to MySQL. Here’s is an example

import MySQLdb
dbh = MySQLdb.connect(
   host=DB_HOST,
   user=DB_USER,
   passwd=DB_PASS,
   db=DB_NAME,
   ssl={"ca":"SIGNING-CERT-FILE"}
)

Warning: If the database does not support SSL, the connection will still succeed, but it will be plain text.

Verifying SSL Transport

The only way to verify that your connection is using SSL is to sniff the traffic on the server or client using tcpdump, like this

tcpdump -nn -s2048 -X host CLIENT-OR-SERVER

where CLIENT-OR-SERVER is the IP address of the MySQL client if you are listening on the server, and vice versa.