As we approach end of life for MySQL 5.7 later this year, many businesses are currently working towards upgrading to MySQL 8. Such major version upgrades are rarely simple, but thankfully there are tools that can help smooth the process and ensure a successful upgrade.
It should be noted that while the technical aspects of the upgrade process are beyond the scope of this blog post, it is crucial to create a testing environment to verify the upgrade before proceeding to upgrade your production servers, particularly with MySQL 8.
As there is no procedure for downgrading from MySQL 8 other than restoring a backup, testing and validation are more critical than previous major version upgrades.
With that disclaimer out of the way, let’s examine some of the tools that are available to simplify the upgrade process.
Percona Toolkit
Percona Toolkit is a collection of advanced open source command-line tools developed and used by the Percona technical staff that are engineered to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
Several of these tools can help with upgrade planning, making the entire process much easier and less prone to downtime or issues.
- pt-upgrade
- The pt-upgrade tool helps you run application SELECT queries and generates reports on how each query pattern performs on the servers across different versions of MySQL.
- pt-query-digest
- As best practice dictates gathering and testing all application queries by activating the slow log for a period of time, most companies will end up with an enormous amount of slow log data. The pt-query-digest tool can assist in query digest preparation for your upgrade testing.
- pt-config-diff
- The pt-config-diff tool helps determine the differences in MySQL settings between files and server variables. This allows a comparison of the upgraded version to the previous version, allowing validation of configuration differences.
- pt-show-grants
- The pt-show-grants tool extracts, orders, and then prints grants for MySQL user accounts. This can help to export and back up your MySQL grants before an upgrade or allow you to easily replicate users from one server to another by simply extracting the grants from the first server and piping the output directly into another server.
- pt-upgrade
Early last year, my colleague Arunjith Aravindan wrote a great blog post covering these Percona tools while also detailing the pt-upgrade process itself. You can read his post here: Percona Utilities That Make Major MySQL Version Upgrades Easier.
For more information or to download the Percona Toolkit, please visit: https://docs.percona.com/percona-toolkit/
MySQL Shell Upgrade Checker
In recent years, MySQL has continued to evolve and introduce new features to enhance its capabilities. One of the more relevant additions to the MySQL toolset is the MySQL Shell Upgrade Checker, which is a built-in upgrade tool that enables users to check whether their MySQL instances are compatible with a specific version before upgrading.
The MySQL Shell Upgrade Checker is designed to help users avoid potential issues that may arise during the upgrade process. This tool works by analyzing the structure and content of the existing database and then comparing it to the requirements of the new version. In doing so, it can detect any potential issues, such as deprecated syntax or incompatible data types, and provide guidance on resolving them.
The MySQL 8 Shell Upgrade checker will check for compatibility of the dataset, looking for things such as:
- Use of old temporal types
- Use of database objects which conflict with reserved words
- Removed system variables
All in all, the MySQL Shell Upgrade Checker contains around 21 total checks and will produce a detailed report of any errors, warnings, or notices.
MySQL 8 & MySQL Shell Upgrade Checker vs. pt-upgrade
Don’t confuse the MySQL Shell Upgrade Checker Utility with the pt-upgrade tool since they are used for very different kinds of major version upgrade testing.
The MySQL Upgrade Checker Utility performs a variety of tests on a selected MySQL server to ascertain whether the upgrade will be successful. This tool, however, does NOT confirm whether the upgrade will be compatible with the application queries or routines, which is where pt-upgrade would come in.
For more information on the MySQL 8 Shell Upgrade Checker, please refer to the following:
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
Conclusion
As always, with anything database related, one of the most important tasks to complete prior to any upgrade is to test extensively. This limits the risk of unforeseen circumstances and allows time to address any unfavorable results before the upgrade. Downgrading a major version is not very feasible once writes are sent to the new MySQL 8 source, so proper planning beforehand is critical.
The tools listed in this post can assist with this planning and help negate any nasty surprises before they become a problem. Percona is always here to help as well, so if you’d like to discuss our Support or Professional Service options, please feel free to contact us!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
