How to Failback from Read Replica for DR in case of MySQL Flexible Server?

Jeyasingh, Vijayalingam 61 Reputation points
2024-09-04T11:57:21.9433333+00:00

When we configure MySQL Flexible Server with Read Replica, in case of Primary Region is down, the Replication is stopped, and Read Replica becomes the stand-alone server with Read & Write capabilities. One the Primary region is up, how to failback from Read Replica for DR to the original Primary Instance?

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
812 questions
Accepted answer
  1. ShaktiSingh-MSFT 15,056 Reputation points
    2024-09-06T06:06:13.8666667+00:00

    Hi Jeyasingh, Vijayalingam

    Thanks for your patience.

    We have received below reply from the internal team:

    For failback, you have 2 options:

     

    1. If you are using cross region read-replica and have promoted the read replica to become standalone (read/write), then for failback to old primary region, you have to rely on provisioning another read replica in the old primary region and promote it. In this scenario, you will not be able to leverage the old primary instance.
    2. If you are using data-in replication to replicate data between DR regions and have failed over to secondary replica during DR, then for failback to old primary region, the only option is to set up data-in replication again between the servers. In specific example you mentioned, it will be data-in replication set up between server in UK south to East US2 which will require you to perform a dump/restore from the source server to ensure data consistency and configure data-in replication. More details here https://video2.skills-academy.com/en-us/azure/mysql/flexible-server/how-to-data-in-replication?tabs=bash%2Ccommand-line

    Hope this helps.

    Thanks

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 15,056 Reputation points
    2024-09-04T13:27:12.7433333+00:00

    Hi Jeyasingh, Vijayalingam •,

    Welcome to Microsoft Q&A forum.

    As I understand, you want to know DR in case of Azure Database for MySQL Primary Server is down and Read Replica is activated.

    For both zone-redundant and same-zone HA:

    • If there's a failure, the time needed for the standby replica to take over the role of primary depends on the time it takes to replay the binary log from the primary storage account to the standby. So we recommend that you use primary keys on all tables to reduce failover time. Failover times are typically between 60 and 120 seconds.
    • The standby server isn't available for read or write operations. It's a passive standby to enable fast failover.
    • Always use a fully qualified domain name (FQDN) to connect to your primary server. Avoid using an IP address to connect. If there's a failover, after the primary and standby server roles are switched, a DNS A record might change. That change would prevent the application from connecting to the new primary server if an IP address is used in the connection string.

    Can I use the standby replica for read or write operations? The standby server isn't available for read or write operations. It's a passive standby to enable fast failover.

    Is replication between the primary and standby replicas synchronous? The replication between the primary and the standby is similar to semisynchronous mode in MySQL. When a transaction is committed, it doesn't necessarily commit to the standby. But when the primary is unavailable, the standby does replicate all data changes from the primary to make sure there's no data loss.

    You can make use of Cross region replication using Data-in replication with Azure Database for MySQL - Flexible Server described here:

    https://techcommunity.microsoft.com/t5/azure-database-for-mysql-blog/cross-region-replication-using-data-in-replication-with-azure/ba-p/3563231

    In above post set-up, any changes to primary instance in one region will be replicated to the server in the other region by using the native replication technique. Take advantage of this solution to scale read workloads or to address DR considerations for potential regional disasters.

    Hope this helps.

    Let us know if you have more queries or a different ask.

    Thanks

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.