Cloud SQL for MySQL disaster recovery: A complete failover and fallback process

Phase 1: Setting up an HA database instance for DR

The following phases (1-3) guide you through a complete failover and fallback process. You run all the commands by using the gcloud command in Cloud Shell. To simplify the process, the tutorial uses default settings when possible (for example, the default Cloud SQL version). In your production environment, you might add other configurations.

Set environment variables

This section provides examples of environment variables that define the various names and regions that are required for the commands that you run in this tutorial. You can adjust these example variables to fit your needs.

The following tables describe instance names, their roles, and their deployment regions for each phase of the DR and fallback process in this tutorial. You can also provide your own names and regions.

The instance names in the preceding tables aren't encoded with their roles. In a DR situation, the function of an instance might change—for example, a replica might become the primary. If the name of the new primary contains the word replica , confusion and conflicts might arise. Therefore, we recommend not encoding instance names with the function or role that they perform.

The preceding tables list the names of standby instances. Even though this tutorial doesn't exercise an HA failover, the tutorial includes the names of standby instances for completeness.

The fallback phase recreates the original deployment of the initial phase in the same original regions. However, in a fallback, the names of the instances must change because the original names aren't immediately available even after the original instance is deleted. To support the speedy creation of instances in the fallback phase, you should use instance names that don't match the names used in the initial phase.

• In Cloud Shell, set environment variables that are based on the specifications in the preceding tables:

    export 
    
   primary_name 
   = 
  instance-1 export 
    
   primary_tier 
   = 
  db-n1-standard-2 export 
    
   primary_region 
   = 
  us-west1 export 
    
   primary_root_password 
   = 
  my-root-password export 
    
   primary_backup_start_time 
   = 
   22 
  :00 export 
    
   cross_region_replica_name 
   = 
  instance-3 export 
    
   cross_region_replica_region 
   = 
  us-west2 
  

  If you want to use a different tier for your primary instance, list the tiers that are available to you, and then assign a different value to the primary_tier:

   gcloud  
  sql  
  tiers  
  list 
  

  For a list of regions where you can deploy Cloud SQL, see Instance settings .

Create a primary database instance

1. In Cloud Shell, create a single instance of Cloud SQL:

    gcloud  
   sql  
   instances  
   create  
    $primary_name 
     
    \ 
     
   --tier = 
    $primary_tier 
     
    \ 
     
   --region = 
    $primary_region 
    
   

   The gcloud command pauses until the instance is created.

2. Set the root password:

    gcloud  
   sql  
   users  
   set-password  
   root  
    \ 
     
   --host = 
   %  
    \ 
     
   --instance  
    $primary_name 
     
    \ 
     
   --password  
    $primary_root_password 
    
   

Create a primary database

1. In Cloud Shell, log in to the MySQL shell and enter the root password at the prompt:

    gcloud  
   sql  
   connect  
    $primary_name 
     
   --user = 
   root 
   

2. At the MySQL prompt, create a database and upload test data :

    CREATE  
   DATABASE  
   guestbook ; 
   USE  
   guestbook ; 
   CREATE  
   TABLE  
   entries  
    ( 
   guestName  
   VARCHAR ( 
    255 
    ) 
   ,  
   content  
   VARCHAR ( 
    255 
    ) 
   ,  
   entryID  
   INT  
   NOT  
   NULL  
   AUTO_INCREMENT,  
   PRIMARY  
   KEY ( 
   entryID )) 
    ; 
   INSERT  
   INTO  
   entries  
    ( 
   guestName,  
   content ) 
     
   values  
    ( 
    "first guest" 
   ,  
    "I got here!" 
    ) 
    ; 
   INSERT  
   INTO  
   entries  
    ( 
   guestName,  
   content ) 
     
   values  
    ( 
    "second guest" 
   ,  
    "Me too!" 
    ) 
    ; 
    
   

3. Check that the data was successfully committed:

    SELECT  
   *  
   FROM  
   entries ; 
    
   

   Verify that two rows of data are returned.

4. Exit the MySQL shell:

    exit ; 
    
   

At this point, you have a single database that includes a table and some test data.

Change the primary instance to an HA database instance

You can only configure Cloud SQL as a regional HA system, not as a cross-regional system. (Setting up a cross-region read replica is different than configuring Cloud SQL as a cross-regional system.) For more information, see Enabling and disabling high availability on an instance .

• In Cloud Shell, create an HA-enabled Cloud SQL instance:

   gcloud  
  sql  
  instances  
  patch  
   $primary_name 
    
   \ 
    
  --availability-type  
  REGIONAL  
   \ 
    
  --enable-bin-log  
   \ 
    
  --backup-start-time = 
   $primary_backup_start_time 
   
  

Add a cross-region read replica for DR with automatic update

The following steps are sufficient to create a cross-region read replica for this tutorial:

1. In Cloud Shell, set up a cross-region read replica:

    gcloud  
   sql  
   instances  
   create  
    $cross_region_replica_name 
     
    \ 
     
   --master-instance-name = 
    $primary_name 
     
    \ 
     
   --region = 
    $cross_region_replica_region 
    
   

2. (Optional) To check that the database was replicated, in the Google Cloud console, go to the Cloud SQL Instancespage.

   Go to Instances

   

   The Google Cloud console shows that the primary instance ( instance-1 ) is enabled for HA and that a cross-region read replica ( instance-3 ) exists.

3. Using the same root password for the primary, log in to the cross-region read replica:

    gcloud  
   sql  
   connect  
    $cross_region_replica_name 
     
   --user = 
   root 
   

4. At the MySQL prompt, select the data to ensure that replication is working:

    USE  
   guestbook ; 
   SELECT  
   *  
   FROM  
   entries ; 
    
   

5. Exit the MySQL shell:

    exit ; 
    
   

For details on how to set up a full cross-region read replica, see the Cloud SQL documentation

For large databases in a production environment, we recommend that you back up the primary database and create the cross-region read replica from the backup. This step helps reduce the time it takes for the read replica to synchronize with the primary database. This process is described in the next section. However, you can choose to skip this step and continue with Phase 2 .

Add a cross-region read replica based on a dump file

One way to optimize the creation of a cross-region read replica is to synchronize the replica from an earlier, consistent primary database state instead of synchronizing at the point of accessing the new primary. This optimization requires creating a dump file that the replica uses as the starting state.

For the steps to create a replica based on a dump file, see Replicating from an external server to Cloud SQL (v1.1) . This approach can be helpful for large production databases. However, this tutorial skips this step because the test dataset is small enough for a complete replication.

Phase 2: Simulating a disaster (region outage)

In this phase, you will simulate the outage of a primary region in a production setting by making the primary database unavailable.

Check for cross-region read replica lag

In the following steps, you determine the replication lag of the cross-region read replica:

1. In the Google Cloud console, go to the Cloud SQL Instancespage.

   Go to Instances

2. Click the read replica ( instance-3).

3. In the metrics drop-down list, click Replication Lag:

   

   The metric changes to Replication Lag. The graph shows no delay:

   

Ideally, the replication lag is zero when a primary region outage occurs, as a delay of zero ensures that all transactions are replicated. If it's not zero, some transactions might not be replicated. In this case, the cross-region read replica won't contain all the transactions that were committed on the primary.

Make the primary instance unavailable

In the following steps, you simulate a disaster by stopping the primary. If a cross-region read replica is attached to the primary, you must first detach the replica, otherwise you can't stop the Cloud SQL instance.

1. In Cloud Shell, remove the cross-region read replica from the primary:

    gcloud  
   sql  
   instances  
   patch  
    $cross_region_replica_name 
     
    \ 
     
   --no-enable-database-replication 
   

   When you're prompted, accept the option to continue.

2. Stop the primary database instance:

    gcloud  
   sql  
   instances  
   patch  
    $primary_name 
     
   --activation-policy  
   NEVER 
   

Implement DR

1. In Cloud Shell, promote the cross-region read replica to a standalone instance:

    gcloud  
   sql  
   instances  
   promote-replica  
    $cross_region_replica_name 
    
   

   When you're prompted, accept the option to continue. The Cloud SQL Instancespage shows the former cross-region read replica ( instance-3 ) as the new primary, and the former primary ( instance-1 ) as stopped:

   

   After you promote the cross-region read replica as the new primary, you enable it for HA. As a best practice, you should update the environment variables with proper naming.

2. Update the environment variables:

     export 
     
    former_primary_name 
    = 
    $primary_name 
    export 
     
    primary_name 
    = 
    $cross_region_replica_name 
    export 
     
    primary_tier 
    = 
   db-n1-standard-2 export 
     
    primary_region 
    = 
    $cross_region_replica_region 
    export 
     
    primary_root_password 
    = 
   my-root-password export 
     
    primary_backup_start_time 
    = 
    22 
   :00 export 
     
    cross_region_replica_name 
    = 
   instance-5 export 
     
    cross_region_replica_region 
    = 
   us-west3 
   

3. Start the new primary:

    gcloud  
   sql  
   instances  
   patch  
    $primary_name 
     
   --activation-policy  
   ALWAYS 
   

4. Enable the new primary as an HA regional instance:

    gcloud  
   sql  
   instances  
   patch  
    $primary_name 
     
    \ 
     
   --availability-type  
   REGIONAL  
    \ 
     
   --enable-bin-log  
    \ 
     
   --backup-start-time = 
    $backup_start_time 
    
   

5. Create a cross-region read replica in a third region:

    gcloud  
   sql  
   instances  
   create  
    $cross_region_replica_name 
     
    \ 
     
   --master-instance-name = 
    $primary_name 
     
    \ 
     
   --region = 
    $cross_region_replica_region 
    
   

   In an earlier step, you set the cross_region_replica_region environment variable to us-west3 .

   After the failover completes, the Cloud SQL Instancespage in the Google Cloud console shows that the new primary ( instance-3 ) is enabled as HA and has a cross-region read replica ( instance-5 ):

   

6. (Optional) If you have regular backups, follow the process described earlier to synchronize the new primary with the latest backup version.

7. (Optional) If you're using a Cloud SQL proxy, configure the proxy to use the new primary in order to resume the application processing.

Handle a short-lived region outage

It's possible that the outage that triggers a failover is resolved before the failover completes. In this case, it might make sense to cancel the failover process and continue using the original primary Cloud SQL instance in the region where the outage occurred.

Depending on the specific state of the failover process, the cross-region read replica might have been promoted already. In this case, you must delete it and re-create a cross-region read replica.

Delete the original primary to avoid a split-brain situation

To avoid a split-brain situation, you need to delete the original primary (or make it inaccessible to database clients).

After a failover, a split-brain situation can occur when clients write to the original primary database and the new primary database at the same time. In this case, the content of the two databases is inconsistent. After a failover, the original primary database is outdated and must not receive any read or write traffic.

• In Cloud Shell, delete the original primary:

   gcloud  
  sql  
  instances  
  delete  
   $former_primary_name 
   
  

  When you're prompted, accept the option to continue.

  In the Google Cloud console, the Cloud SQL Instancespage no longer shows the original primary instance ( instance-1 ) as part of the deployment:

  

Phase 3: Implementing a fallback

To implement a fallback to your original region (R1) after it becomes available, you follow the same process that is described in Phase 2. That process is summarized as follows:

1. Create a second cross-region read replica in the original region (R1). At this point, the primary has two cross-region read replicas, one in region R3, and one in region R1.

2. Promote the cross-region read replica in R1 as the final primary.

3. Enable HA for the final primary.

4. Create a cross-region read replica of the final primary in us-west2 .

5. To avoid a split-brain situation, delete all instances that are no longer required (the original primary and the cross-region read replica in R3).

As discussed earlier, it's a best practice to create an initial backup that contains the defined start state for the new primary database.

The final deployment now has an HA primary (with the name instance-6 ) and a cross-region read replica (with the name instance-8 ).

Comparing advantages and disadvantages of a manual versus automatic DR

The following table discusses the advantages and disadvantages of implementing a DR process either manually or automatically. The goal isn't to determine a correct versus incorrect approach but to provide criteria to help you determine the best approach for your needs.

As a best practice, we recommend that you start with a manual implementation. Then, voluntarily run the implementation regularly (preferably in production) to ensure that the manual process works and that all team members know their roles and responsibilities. We recommend that you define your manual process in a step-by-step process document. After every implementation, you should confirm or refine the process document.

After you fine-tune the process and are confident that it's reliable, you then determine whether to automate the process. If you select and implement an automated process, you need to test the process regularly in production to ensure that you can implement it reliably.