It is essential to upgrade MySQL to the most recent version. Do you believe it’s tough to test and upgrade to a newer version?
For a variety of reasons, including new features, performance advantages, bug corrections, and so on, databases with obsolete versions are vulnerable. Major version upgrades, on the other hand, can be problematic if they haven’t been extensively tested with your application, as the procedure may break it, prevent it from functioning properly, or result in performance concerns.
Let’s go through a few useful tools that can assist you with MySQL upgrades.
The tool helps you run application SELECT queries and generates reports on how each query pattern performs on the servers across the different versions of MySQL we tested.
We need to gather all application queries by activating the slow log for a day or a few hours that covers the majority of the queries, however, the slow log will be enormous, and applying them will take time, thus the pt-query-digest tool can assist in query digest preparation for upgrade testing.
The tool aids in determining the differences in MySQL settings between files and server variables. This allows us to compare the upgraded version to the previous version, allowing us to validate the configuration differences.
1 | pt-config-diff h=<Testnode1> h=<Testnode2> |
pt-upgrade Testing Requirements and Steps
Let’s go over the requirements and steps for testing application queries with pt-upgrade. For testing purposes, we require and suggest having two servers that meet production specs and are connected to the same network.
For example, to test MySQL 5.7 and MySQL 8, we build the two instances, one with MySQL 5.7 and the other with MySQL 8, both from a recent production backup
Replication Compatibility Test for the Production Data
Set up replication for both test nodes for a day, replicating from the production primary to see if the replication works from the lower current production version to the newer version, i.e. covering the actual application workload. Before we begin pt-upgrade testing, it’s important to stop replication on both test nodes at the same binary log position to confirm the data in both nodes are identical.
The High-Level Plan Will be as Follows:
- Install MySQL on the two test nodes. The current MySQL version will be on one node, while the target version will be on the other.
- Production data needs to be restored to the test nodes, Percona XtraBackup can be used to backup and restore the backup from the production node to the test nodes.
- As part of the replication compatibility test as aforesaid, set up replication for both test nodes for a day i.e. covering the actual workload, replicating from the production primary.
- The slow log can be used to collect all queries from the production nodes using long_query_time = 0 and log_slow_rate_limit =1. We need to gather all application queries by activating the slow log for a day or a few hours, which should cover the majority of the application queries. However, in most cases, the slow log will be massive, and applying them using the pt-upgrade will take time, so the pt-query-digest tool can help with query digest preparation for pt-upgrade testing.
- In the digest, for example, we can take a max of 50 samples per query.
1 2 | Note: When processing large slow logs, the tool may use some memory, so keep an eye on it if you're running it on production servers. pt-query-digest --sample 50 --no-report --output slowlog <slow_log_file> > <digest>.out |
- If any below session variables are found in the digested slow log, it is likely to be checked out and removed so that the queries can continue to run as they are.
1 | SET SQL_SAFE_UPDATES=...,SQL_SELECT_LIMIT=...,MAX_JOIN_SIZE=.... |
- Before starting the pt-upgrade tests, to ensure that the data on both test nodes is identical, stop replication on both test nodes at the same binary log position.
- Readonly Test
All queries will be played back using the pt-upgrade tool in read-only mode a couple of times on test nodes logging the results. We can discard the first run’s results because this is just to warm up the Innodb Buffer pool, and execute the same pt-upgrade command again.
1 | $ pt-upgrade h=Test1 h=Test2 --max-examples=1 <digest>.out 1> pt-upgrade_results.out 2> pt-upgrade_results.err |
- Read Write Test
All queries will be played back in read-write mode enabling the –no-read-only option once on the test nodes, logging the results.
1 | $ pt-upgrade h=Test1 h=Test2 --no-read-only --max-examples=1 <digest>.out 1> pt-upgrade_results_RW.out 2> pt-upgrade_results_RW.err |
Read-only and read-write test reports are now available to evaluate and isolate queries that are slower, return more/fewer rows, or return different rows altogether, and return an error or warning. We can implement fixes based on the report to address the issues raised by pt-upgrade testing. This may require MySQL setup changes, query optimization, query rewriting, schema changes, index additions or revisions, and so on.
Whether you’re looking to mitigate vulnerabilities or take advantage of new features, benefits, and fixes, upgrading MySQL is essential. In this on-demand webinar, Percona Experts will share best practices for ensuring your major MySQL upgrades run smoothly.
