Setting up Amazon Aurora as a replica of an external MySQL primary is a common way of synchronizing and/or migrating self-managed MySQL databases to RDS/Aurora.
Nowadays, using GTIDs for MySQL is the preferred way of doing replication, for example, because it offers features such as auto-position and thus makes it easy to change replication topologies.
According to the AWS docs and also AWS blogs, it’s possible to use GTID for replication from an external primary into Aurora.
But there’s one important issue which is not covered by the docs or blogs, and this is setting the gtid_executed / gtid_purged variables, which new MySQL replicas usually need GTIDs, so they know their initial binlog position. This value is not set in Aurora/RDS, at least when restoring from a Xtrabackup on S3. Also, RDS/Aurora does not allow setting this value:
mysql> SET GLOBAL gtid_purged='<gtid_string_found_in_xtrabackup_binlog_info>';
When I started the replication without the GTID set, it immediately stopped working with obscure errors, since it was apparently starting to replicate events from the primary at a random position, such as:
mysql> show slave status\G
But, AWS Support to the rescue, knew a workaround by setting the gtid_executed value directly in the
mysql> insert into mysql.gtid_executed(source_uuid,interval_start,interval_end) values('5f70944c-9bbe-11e9-a9d2-0a75ff943724',1,19);
Note: This also works with a set of multiple GTIDs. Just insert more rows)
Now, reboot the DB instance. Check the value has been set into the
mysql> show variables like 'gtid_purged';
And, if it looks correct, start the replication:
mysql> CALL mysql.rds_reset_external_master;
Note: These commands are for Aurora 2/MySQL 5.7. Aurora 3 / MySQL 8 have naming changes to a more inclusive language, the commands look slightly different.
After setting the correct initial value for the
gtid_executed value, the replication should be running smoothly and catching up.
Like what you read?
You can hire me or make a donation via PayPal!