EmergencyEMERGENCY? Get 24/7 Help Now!

Percona Toolkit for MySQL with MySQL-SSL Connections

 | October 16, 2014 |  Posted In: MySQL, Percona Toolkit

PREVIOUS POST
NEXT POST

I recently had a client ask me how to use Percona Toolkit tools with an SSL connection to MySQL (MySQL-SSL). SSL connections aren’t widely used in MySQL due to most installations being within an internal network. Still, there are cases where you could be accessing MySQL over public internet or even over a public “private” network (ex: WAN between two colo datacenters). In order to keep packet sniffers at bay, the connection to MySQL should be encrypted.

If you are connecting to Amazon RDS from home or office (ie: not within the AWS network) you better be encrypted!

As there is already a MySQL Performance Blog post on how to setup MySQL SSL connections, we can skip that and dive right in.

As you probably know, the mysql client can read multiple configuration files; the primary one being /etc/my.cnf  You probably also know that the client reads a config file in your $HOME directory: .my.cnf (that’s dot-my-dot-cnf).  It is inside this file that we can set parameters for our shell-user account when connecting to MySQL hosts.

Percona Toolkit uses Perl’s DBI:mysql to make connections to MySQL hosts. This library is linked to the libmysqlclient C library which is responsible for reading and parsing the global config file as well as your $HOME config file. Let’s set some options here that are not directly available in the toolkit scripts. Using $MY_FAVORITE_EDITOR, edit your $HOME/.my.cnf as such:

You must use the absolute path to the CA file. Relative paths won’t cut it:

Test your connection first using the mysql client:

Excellent! Now we can use any Percona Toolkit script and connect via SSL:

Sweet!

Unfortunately, Percona Toolkit scripts are hard-coded to read the [client] section of your .my.cnf. If you don’t want to overwrite any existing configuration that may be present, you can make a new configuration and specify that file to any toolkit script using -F. Again, relative paths won’t work here. Use the absolute path; even if you are in the same directory.

Now you can continue using our awesome tools in a secure manner.

Cheers!
-Matthew

PREVIOUS POST
NEXT POST
Matthew Boehm

Matthew joined Percona in the fall of 2012 as a MySQL consultant. His areas of knowledge include the traditional Linux/Apache/MySQL/PHP stack, memcached, MySQL Cluster, massive sharding topologies, PHP development and a bit of MySQL-C-API development. Previously, Matthew was a DBA for the 5th largest world-wide MySQL installation at eBay/PayPal and also hails from managed hosting environments. During his off-hours, Matthew is a nationally ranked, competitive West Coast Swing dancer and travels to competitions around the US. He enjoys working out, camping, biking and playing Guild Wars 2 with his son.

One Comment

  • I’ve been trying to use XtraBackup with an SSL connection and not had any luck. I even created a thread on the forums with no resolution.

    https://www.percona.com/forums/questions-discussions/percona-xtrabackup/15596-how-to-backup-using-ssl#post20009

    Any ideas? Thanks so much for the help.

Leave a Reply