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> |
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
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.
|
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 |
|
1 |
SET SQL_SAFE_UPDATES=...,SQL_SELECT_LIMIT=...,MAX_JOIN_SIZE=.... |
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 |
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.
