TrobuleShooting

Configure SQL Instance

Step 1: Create an instance for MySQL DB in GCP. Create a MySQL DB With Cloud SQLOnce, the Cloud SQL instance is successfully created, and you can see an instance in GCP....

Primary and Secondary Cloud SQL Instance

Given architecture diagram shows a primary Cloud SQL instance in us-central1-f and a secondary(stand-by) instance in us-central1-b. In case of a zonal failure, the stand-by instance is promoted to become a primary instance i.e., the instance is us-central1-b becomes a primary instance....

What Happens If the Whole Region Goes Down?

In this scenario, the primary instance and standby instance both will go down. Regional failures are not common but are possible. A higher level of redundancy is required to resolve region failures. Cross-region High Availability can be enabled by creating a read replica in a different region. In case of a region failure, the read replica can be promoted to a primary instance, thereby ensuring cross-region HA....

Multiple Read Replicas

If there are multiple read replicas, they are dependent on the primary instance for replication. In case of a region failure, the DR replica gets promoted to become a primary instance, but what about other read replicas?? All other read replicas have to be re-created and any applications that are accessing this replica have to update the endpoint....

TrobuleShooting

Cascading replica: Instead of creating multiple replicas from the primary instance, read replicas can be sourced from Disaster recovery read replicas. In this way, the read replica for our read-only applications was already configured as a Cascading Replica of the DR read replica (newly promoted primary instance), it is already utilizing that instance as a source for replication. So, to sum up, no need to create other downstream replicas in case of regional failure since they are not sourced from a Primary instance but a DR read replica. The updated architecture diagram shows that the read replica in us-central1-f is sourcing data from the DR (Disaster Recovery) read replica rather than the primary instance in us-central1. In case of a regional failover in this architecture, the read-replica is not impacted, thereby continuing the replication from the DR replica (newly promoted Primary instance)...

Steps To Create DR Replicas

Step 1: Select the DR (Disaster Recovery) read replica and open it. To Create a read replica out of the DR read replica point in time recovery has to be enabled. Click on Enable Replication...

Automating the Process

You must have some automation in place to effectively reduce downtimes in case of a region failure. Automation of the process would cut down the cost by expediting the failover process. An automation script can be written that does automation of conducting a cross-region failover in Google Cloud SQL using HA Replicas and Cascading Replicas...

Conclusion

Cloud SQL provides native Highly available architecture within a single region. In case of zonal failure, Cloud SQL will automatically manage the failover by making the secondary zone instance to be primary without switching the IP. However, In case of a regional failover, read replicas are to be created in a different zone than the primary instance. Read replicas are promoted as primary HA in case of region failure. Cascading replicas are required since multiple read replicas being sourced from primary instances incorporate a dependency on the primary instance, if the primary instance goes down, these read replicas have to be deleted and re-created. In this scenario, the application fetching data from these read-replica will have to suffer some downtime, since new read-replica creation and endpoint update might take some time. To resolve this, Cascading read replicas can be created, A Cascading read-replica is a read-replica sourcing from another read-replica. The DR read replica can act as a source for other read replicas being created. This way, in case of a regional failure, no application will suffer a downtime since all the read replicas will be sourced from DR read replicas (which will be promoted to the Primary instance in case of regional failover). It’s highly recommended to incorporate these features in your architecture for a Highly available, fault-tolerant architecture that allows automatic failover, reducing downtime for the architecture implemented in GCP....

FAQs On Cloud SQL for MySQL with automatic failover on GCP

1. What Is Cloud SQL With Automatic Failover?...