AWS RDS snapshot restoration missing binlog data

Photo by Kevin Ku on Unsplash

We recently had cause to want to create a replica of one of our databases (trivial) that was writable and we could make changes to (not trivial)

As luck would have it, AWS provides documentation on just this situation: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.html

It’s clear that, once you create a database by restoring a snapshot, its Log tab in the web console should tell you the binlog position the snapshot was taken from. With this information, you can configure the replica and have it resume replication from the original database. This leads to a fully-replicated copy that you can make changes to.

However, we kept encountering a problem where the binlog information we needed wasn’t displayed. Google didn’t find us a fix, so we resorted to AWS Support.

Unfortunately, they could only confirm that this was a known bug and apologise that there was no ETA on a fix. They did, however, supply one potential workaround: In the Logs tab, the oldest error log should contain within it the binlog data, even if the web console doesn’t show it:

InnoDB: Last MySQL binlog file position 0 43496027, file name mysql-bin-changelog.390298

With this, you can then set the appropriate position and begin replication

CALL mysql.rds_set_external_master ('<HOST>', 3306, '<USER>', '<PASSWORD>', 'mysql-bin-changelog.390298', 43496027, 0);

And show slave status should confirm that all is working as it should from here.