MySQL disaster recovery: cross-region asynchronous replica with GTIDs

#1. Prerequisites on the source

gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = binlog
binlog_format = ROW
binlog_expire_logs_seconds = 604800

CREATE USER 'repl'@'10.60.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.60.%';

#2. Seed the DR replica

Clone plugin (8.0.17+, the fastest):

-- on the DR replica
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SET GLOBAL clone_valid_donor_list = 'mysql-prd-01.corp.local:3306';
CLONE INSTANCE FROM 'clone_user'@'mysql-prd-01.corp.local':3306 IDENTIFIED BY '...' REQUIRE SSL;

Or use XtraBackup: xtrabackup --backup, copy it, --prepare, then --copy-back on the replica.

#3. Start replication

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'mysql-prd-01.corp.local',
  SOURCE_USER = 'repl', SOURCE_PASSWORD = '...',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 1,
  SOURCE_CONNECT_RETRY = 10, SOURCE_RETRY_COUNT = 8640,
  GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;
SET GLOBAL super_read_only = ON;

Replica tuning for cross-region latency:

replica_parallel_workers = 8
replica_preserve_commit_order = ON
binlog_transaction_dependency_tracking = WRITESET    # on the source (8.0; the default from 8.4)

#4. Monitor

SHOW REPLICA STATUS\G
-- Replica_IO_Running: Yes, Replica_SQL_Running: Yes, Seconds_Behind_Source: ...
SELECT * FROM performance_schema.replication_applier_status_by_worker;

Alert when either thread stops, or when lag is above the RPO. A heartbeat table (pt-heartbeat) gives a more accurate lag than Seconds_Behind_Source.

#5. Promote in a disaster

1. Confirm the source is lost (or fence it so apps can't write to it).
2. Let the replica apply whatever it has already received (relay log): wait until Retrieved_Gtid_Set is contained in Executed_Gtid_Set.
3. Promote it:

STOP REPLICA;
RESET REPLICA ALL;
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

4. Repoint DNS or ProxySQL / MySQL Router to the new primary.
5. Start backups on the new primary.

#6. Failback

When the old region comes back, don't let the old primary accept writes. Rebuild it as a replica of the new primary (clone again, or CHANGE REPLICATION SOURCE with auto-position if its GTID set is a subset). Then make a planned switchover back:

1. Set the current primary to super_read_only = ON.
2. Wait for the replica to apply everything (compare gtid_executed).
3. Promote the old primary and repoint apps.
4. Rebuild replication in the original direction.

#Managed equivalents

• RDS MySQL: cross-region read replica, promoted with aws rds promote-read-replica. Aurora MySQL: Global Database (see the AWS Aurora post).
• Azure Database for MySQL Flexible Server: cross-region read replica, then "promote".

Warning

Transactions on the source that hadn't reached the replica are lost after promotion. If they're found later on the old primary (the "errant" GTIDs), compare and reconcile them by hand. Don't just restart replication.