Rollback Aurora MySQL’s Major Version Upgrade When Delta Data Already Exists
05/08/2026
10
Introduction
With RDS/Aurora MySQL version 5.7 standard support having ended on October 31, 2024, upgrading to MySQL version 8.0 is necessary to reduce security risks, as well as to avoid paying AWS Extended Support costs.
But “There’s no place like Production” – I.M. Wright, incidents can still happen at any time.
Planning a rollback strategy is just as important as planning an execution strategy.
Rollback timing and appropriate strategies
- Error detected right at the time of upgrade: Stop the upgrade process, restore the cluster for an in-place upgrade, or delete the blue/green deployment for a blue/green upgrade
- Error detected during testing: Restore the cluster for an in-place upgrade, or rename the cluster for a blue/green upgrade
- Error detected some time after go-live and cannot be hotfixed:
- Dump all data from the 8.0 cluster, and restore that data to the 5.7 cluster
- Export delta data from the 8.0 cluster, and import the delta data into the 5.7 cluster
- Run data replication from cluster 8.0 to cluster 5.7
This article will present rollback strategy 3.2: Export delta data from database 8.0, and import delta data into database 5.7
Prerequisites
- The 8.0 Cluster/Writer instance has been configured with the parameter binlog_format=ROW
- The parameter status of the 8.0 Writer instance is In-sync
- Install python version 3.4 or higher
- Install binlog2sql following the instructions here https://github.com/danfengcao/binlog2sql. This is a tool that allows extracting SQL statements from binlog files.
- Note: Update the requirement.txt file to upgrade the python dependency PyMySQL version from 0.7.11 to 0.9.3, in order to be compatible with MySQL 8.0
Execution steps
Assuming the database upgrade is performed using the Blue/Green deployment strategy, after the switchover and deletion of the Blue/Green deployment, the old cluster running version 5.7 is still retained for rollback.
1. [Immediately after switchover] Get the binlog position information of the 8.0 cluster
Run the following query on the 8.0 cluster
SHOW MASTER STATUS;The result will look like below, with:
- Binlog filename: mysql-bin-changelog.000015
- Binlog position: 1793
mysql-bin-changelog.000015 17932. [Immediately after switchover] Increase the retention time of the binlog file on the 8.0 cluster. By default, the binlog file retains data for up to 1 hour.
Run the following query on the 8.0 cluster. The example below configures the binlog file retention: 7 days * 24 = 168 (hours)
CALL mysql.rds_set_configuration('binlog retention hours', 168);Check the configuration after applying:
CALL mysql.rds_show_configuration;
binlog retention hours 168 binlog retention hours specifies the duration in hours before binary logs are automatically deleted.3. [Incident occurs after go-live] Enable maintenance mode and block access to the 8.0 cluster
- Set Readonly for the cluster
- Remove Security Group inbound rule
- Kill outstanding database connections
4. [Incident occurs after go-live] Extract delta data from the 8.0 cluster, using the start position and binlog file obtained from step 1
python binlog2sql.py -h'rds-endpoint' -P3306 \
-u'db_username' -p'db_password' \
--databases 'database_name' \
--start-file mysql-bin-changelog.000015 --start-position 1793 >> delta_data.sql5. [Incident occurs after go-live] Run data checksum on the 8.0 cluster
CHECKSUM TABLE table_name1, table_name2,... EXTENDED;6. [Incident occurs after go-live] Import delta data into the 5.7 cluster
mysql -v -h rds_endpoint -P3306 -u db_username -p database_name < delta_data.sql7. [Incident occurs after go-live] Run data checksum on the 5.7 cluster
CHECKSUM TABLE table_name1, table_name2,... EXTENDED;8. [Incident occurs after go-live] Compare the checksum results on both the 5.7 and 8.0 clusters. Ensure the results are identical
9. [Incident occurs after go-live] Rename the 8.0 cluster to mark it as failed
10. [Incident occurs after go-live] Rename the 5.7 cluster back to the name the app is using
Some notes
Always run a dry-run to verify the rollback steps before releasing to production.
FAQs Section
You should consider a rollback when a serious issue appears after upgrading Aurora MySQL from 5.7 to 8.0 and the issue cannot be fixed safely with a hotfix. If the problem is found immediately during the upgrade, restoring or deleting the Blue/Green deployment may be enough. But if the system has already gone live and new production data exists in the 8.0 cluster, you need a rollback strategy that also preserves delta data.
Rollback becomes harder because the old Aurora MySQL 5.7 cluster no longer contains the latest data created after go-live. Simply switching traffic back to the old cluster may cause data loss. In this case, you need to extract the delta data from the Aurora MySQL 8.0 cluster and import it into the retained 5.7 cluster before restoring traffic.
Delta data refers to the new or changed data written to the upgraded Aurora MySQL 8.0 cluster after switchover. This can include new records, updates, and deletes created while production traffic was already running on the upgraded database.
The binlog position marks the starting point for tracking changes after the upgrade goes live. By capturing the binlog file and position immediately after switchover, you can later extract only the delta data created after that point instead of dumping the entire database.
Aurora MySQL binary logs are retained for a limited time by default. If an incident happens hours or days after go-live, the required binlog files may already be deleted. Increasing binlog retention gives the team enough time to extract delta data during rollback.
binlog2sql reads MySQL binlog files and converts database changes into SQL statements. In this rollback strategy, it is used to extract the delta data from the Aurora MySQL 8.0 cluster, starting from the binlog file and position captured after switchover.
