MySQL 8.0 to 8.4 LTS Upgrade Guide — Part 2: Upgrade Testing

Testing is the most time-consuming but most valuable phase of the upgrade process. Skipping testing for a major version upgrade carries extreme risk. For large datasets (multi-terabyte), a failed untested upgrade could result in 10-20+ hours of downtime for rollback alone.

This part covers setting up a test environment, running pt-upgrade for query compatibility, performing a full dry-run, verifying tool compatibility, and optional canary testing.

Test Environment Setup

Set up a test environment that mirrors production as closely as possible. You need two instances for pt-upgrade comparison testing:

Setup Steps

1. Restore both instances from a recent production backup
2. Ensure both instances have identical data at the same point-in-time
3. Configure the 8.4 instance with your planned my.cnf (cleaned of removed parameters)
4. Apply binary logs to both so data matches the start of your slow query log capture window
5. Verify both instances are healthy and serving queries

Estimated Time: 15-20 hours

Query Compatibility Testing with pt-upgrade

pt-upgrade (Percona Toolkit) replays your actual production workload against both MySQL versions side-by-side and reports differences in query results, errors, or performance.

Step-by-Step Process

Step 1: Enable slow query logging on the production source to capture workload:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0;  -- Capture ALL queries

Step 2: Collect 1-3 hours of production traffic during peak hours.

Step 3: Disable slow query logging and copy the log:

SET GLOBAL slow_query_log = OFF;
cp /var/lib/mysql/*-slow.log /root/pt-upgrade-workload.log

Step 4: Create a query digest for structured replay:

pt-query-digest /root/pt-upgrade-workload.log --output slowlog \
  > /root/pt-upgrade-digest.out

Step 5: Run pt-upgrade (read-only test):

pt-upgrade h=test_80_host,u=user,p=pass \
  h=test_84_host,u=user,p=pass \
  --max-examples=1 /root/pt-upgrade-digest.out \
  1> pt-upgrade_RO.out 2> pt-upgrade_RO.err

Step 6: Run pt-upgrade (read-write test):

pt-upgrade h=test_80_host,u=user,p=pass \
  h=test_84_host,u=user,p=pass \
  --no-read-only --max-examples=1 /root/pt-upgrade-digest.out \
  1> pt-upgrade_RW.out 2> pt-upgrade_RW.err

Step 7: Analyze results for query failures, row count differences, and performance regressions.

Estimated Time: 20-25 hours (including collection, RO test, RW test, and analysis)

Full Dry-Run Upgrade

Beyond query testing, perform the entire upgrade procedure end-to-end on a staging replica before touching production. This validates:

• The complete upgrade procedure works without errors
• The my.cnf changes are correct and MySQL starts cleanly
• The upgrade duration matches your maintenance window estimates
• Your rollback procedure works if needed
• Your monitoring and alerting detects the upgraded instance correctly

Application and Tool Compatibility Testing

Verify that all ecosystem components work with MySQL 8.4:

Canary Testing (Optional but Recommended)

For critical production databases, consider canary testing before full cutover:

1. Set up the 8.4 instance as a replica receiving real production traffic
2. Configure ProxySQL or your load balancer to route a small percentage (5-10%) of read traffic to the 8.4 replica
3. Monitor error rates, query latency, and result correctness for 24-48 hours
4. Gradually increase traffic percentage if no issues are found
5. Proceed to full cutover once confident

This approach catches subtle issues under real production load patterns that synthetic testing may miss.

Part 2 Quick Checklist

• Set up test environment (8.0 baseline + 8.4 target)
• Collect slow query log from production (1-3 hours peak)
• Run pt-upgrade read-only test and analyze results
• Run pt-upgrade read-write test and analyze results
• Run application test suites against 8.4 test instance
• Load test 8.4 instance and compare performance
• Complete full dry-run upgrade on staging
• Canary test with partial traffic (optional)