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:
1 2 3 4 | [client] user = myuser password = foobar ssl-ca = /Users/drmac/ca-cert.pem |
You must use the absolute path to the CA file. Relative paths won’t cut it:
1 | ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed |
Test your connection first using the mysql client:
1 2 3 4 5 6 | asura:~ drmac$ mysql -h 74.13.19.17 -e "SHOW STATUS LIKE 'Ssl_cipher'" +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | Ssl_cipher | DHE-RSA-AES256-SHA | +---------------+--------------------+ |
Excellent! Now we can use any Percona Toolkit script and connect via SSL:
1 2 3 | asura:~ drmac$ pt-table-checksum -h 74.13.19.17 -d foo -t zipcodes TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 10-13T14:10:02 0 0 45358 7 0 5.959 foo.myzipcodes |
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.
1 2 3 | asura:~ drmac$ cp .my.cnf mytestconfig.cnf asura:~ drmac$ rm .my.cnf asura:~ drmac$ pt-table-checksum -h 74.13.19.17 -d foo -t zipcodes -F /Users/drmac/mytestconfig.cnf |
Now you can continue using our awesome tools in a secure manner.
Cheers!
-Matthew
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.
Hi, it seems like these instructions are out of date? With pt-show-grant v.3.0.9, following these instructions does not seem to enable an SSL connection: 3.0.9
Ugh apologies for the typo. A detailed account of steps to reproduce this issue can be found here: https://dba.stackexchange.com/questions/235072/how-to-use-the-percona-toolkit-to-connect-to-azure-database-for-mysql-when-ssl-i