Setting up MySQL over TLS
- Apr
- 30
- Posted by TKH Specialist
- Posted in linux
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
-
ssl-ca=SIGNING-CERT-FILE
and all subsequent network traffic when using the
mysql
command-line client will encrypted. TheSIGNING-CERT-FILE
is the same as above. - Use command-line option
-
You don’t need to edit the
my.cnf
file if you run the client like thismysql --ssl-ca=SIGNING-CERT-FILE ...
Add the following line under the [client]
section
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.
Recent Comments
- Stefan on Flush This!
- Timestamping your Bash History | Matt's Entropy on Remember when you issued that command…?
- Matt Smith on Remember when you issued that command…?
- Ruan on Putting ‘lsof’ to use
- Dylan F. Marquis on External Mapping of LDAP attributes in CAS