In today’s interconnected world, data security is paramount. Protecting sensitive information transmitted between applications and databases is crucial, and SSL/TLS (Secure Sockets Layer/Transport Layer Security) plays a vital role in achieving this. Percona Toolkit, a collection of command-line tools for MySQL, MongoDB, and other databases, has long been a go-to resource for database administrators. In this blog post, we’ll explore how Percona Toolkit’s Perl tools, which work with MySQL, leverage it to ensure secure database operations.

SSL/TLS in MySQL Database Communications

SSL/TLS provides an encrypted channel for communication, preventing eavesdropping, tampering, and message forgery. For database operations, where sensitive customer data, financial records, or intellectual property often reside, enabling SSL/TLS is not just a best practice—it’s a necessity.

Starting from version 8.0, MySQL not only supports SSL but also makes  caching_sha2_password  the default authentication plugin. This authentication method requires a secure connection between the server and the client, or an unencrypted connection that supports password exchange using an RSA key pair.

Percona Toolkit before 3.7.0 and SSL/TLS

The Percona Toolkit uses the Perl programming language and the DBD::mysql driver in most of its tools that work with MySQL.

The DBD::mysql driver implements DSN (data source name) to connect to a MySQL database. This implementation has many options, including SSL/TLS support.

Percona Toolkit also connects to databases using DSN (data source name) and implements its own DSN syntax. Percona Toolkit DSN is not the same as DBD::MySQL DSN. When you specify DSN as an argument to the Percona Toolkit utility, it parses this DSN and creates a new DSN recognizable by the DBD::mysql.

Percona Toolkit’s DSN did not have SSL/TLS support until version 3.7.0.

Both Percona Toolkit and DBD::mysql support configuration files. However, DBD::mysql requires the option mysql_ssl=1, which is not a standard MySQL client option. It also does not support the standard client option ssl_mode. As a result, we cannot enable SSL support using configuration files.

SSL/TLS Support in version 3.7.0 and newer

To resolve this issue, in version 3.7.0, we added a new DSN option: s that passes the option mysql_ssl to DBD::mysql and enables SSL/TLS connection. We also added the command-line option –mysql_ssl and its short form -s to enable SSL/TLS even if you don’t specify DSN when connecting to MySQL.

For example, to use pt-archiver with SSL/TLS, connect to the database using this command:

Or, if you prefer long options:

All other SSL/TLS-related options, such as ssl-ca, ssl-cert, ssl-cipher, and others, could be specified in the configuration file if necessary. We intentionally did not implement separate DSN options for them to avoid making Percona Toolkit’s DSN too complicated. If you need them in DSN, please file a request at https://jira.percona.com/projects/PT.

This solution adds complication for working with such tools as pt-online-schema-change, pt-table-checksum, and pt-table-sync, which connect not only to a single server but to all servers in the replication chain. In this case, if all servers in the chain do not share the same client key and certificate, and you want to provide paths to them, you need to connect to the source server and use option –recursion-method=dsn, where you specify configuration for each of the servers. Otherwise, the tool will use the same configuration file as specified in the main DSN for the initial connection.

This work was part of the preparation for Percona Toolkit to work with MySQL 8.4 and finalized as a fix for https://jira.percona.com/browse/PT-191 in the upcoming version 3.7.1.

Conclusion

Enabling SSL/TLS support in Percona Toolkit is a significant step towards bolstering database security. With the introduction of the new DSN option ‘s‘ in version 3.7.0 and the option –mysql_ssl in version 3.7.1, Percona Toolkit now seamlessly integrates with secure MySQL connections, addressing the evolving security landscape and the default caching_sha2_password plugin in MySQL 8.0+. While other SSL/TLS options are managed via configuration files to maintain DSN simplicity, users working with replication chains should be mindful of –recursion-method=dsn for server-specific key and certificate paths. This enhancement, finalized in version 3.7.1 as a fix for PT-191, ensures that Percona Toolkit remains a robust and secure solution for database administrators in an increasingly interconnected and security-conscious world.

Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments