Since 2017 re:Invent I’ve been trying to upgrade our 2TB production database to 5.7 but I face roadblocks all the way.
The latest one we faced in January 2019 is the Backtrack functionality. As it appears – if you EVER enable it on your 5.6 database you will NOT be able to upgrade to 5.7 even if you DISABLE backtrack or even if you disable it, make a SNAPSHOT and restore to a new instance with no Backtrack enabled.
We have this bug/problem confirmed by AWS support and, guess what – they didn’t fix it but just updated the spec on February 16th confirming the issue. No ETA on this. Here is the spec update from aws doc github repo.
What is Backtrack after all? Backtrack is a great feature that should enable you to rewind your database to a position in time. It is up to you to define how much time should it keep the track for you / max 72 hours.
+
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Backtrack.html
Here are some important warnings including those related to this post:
- Backtracking is not supported with binary log (binlog) replication. Cross-region replication must be disabled before you can configure or use backtracking.
- Backtracking is only supported for Aurora MySQL 5.6. It isn’t supported for Aurora MySQL 5.7. Because of this limitation, you currently can’t follow certain upgrade paths from Aurora MySQL 5.6 to 5.7 if you created the Aurora MySQL 5.6 cluster with the Backtrack setting enabled:
- You can’t restore a snapshot of the Aurora MySQL 5.6 DB cluster to Aurora MySQL 5.7.
- You can’t perform point-in-time recovery on the Aurora MySQL 5.6 DB cluster to restore it to Aurora MySQL 5.7.
- These limitations still apply even if you turn off Backtrack for the Aurora MySQL 5.6 cluster.
Unfortunately, last November when we had another Aurora issue and we couldn’t use Backtrack option. The root issue was related to XA transactions but, after this doc update, we realized that the problem with Backtrack was probably related to enabled binlog. We were badly surprised that it doesn’t work.
So, we are stuck with 5.6 unless AWS fixes this bug or … we create binlog replica cluster with no Backtrack enabled and promote it to a primary server… Well … that won’t work because creating binlog starts with the latest snapshot and it is “dirty”. Dump 2TB database to files and recreate it is the only option …