LAB29_GCP_Database Architecture: Cloud SQL HA and Read Replicas

Database Architecture: Cloud SQL HA and Read Replicas

☁ GCP 🔥 Intermediate Cloud SQL High Availability Read Replica Database

Scenario

Your e-commerce database is mission-critical. If an availability zone goes down, your system must failover instantaneously. Furthermore, your analytics team runs heavy dashboard queries that are slowing down transaction processing. You must architect a Cloud SQL High Availability (Regional) primary instance, and deploy a Cross-Region Read Replica for the analytics team.

Your Objectives
  • Provision the primary google_sql_database_instance. Set availability_type = "REGIONAL".
  • Enable Point-in-Time Recovery and automated backups (required for replicas).
  • Provision a second google_sql_database_instance indicating master_instance_name. Place this in a different geographical region (e.g. us-east1).
  • Output connection strings for both Read/Write operations (Primary) and Read-Only operations (Replica).

Additional Context

High Availability (Regional): In GCP, HA means the primary instance has a standby instance passively waiting in another zone within the same region. Disk writes are synchronously replicated across zones. If the primary zone fails, GCP promotes the standby automatically to primary using the same IP address.

Read Replicas: Replicas are completely separate instances with their own IP addresses used for offloading reads. They use asynchronous replication. If your primary whole region goes down, you must manually promote a cross-region read replica to act as the new standalone primary.

variables.tf

variable "project_id" { type = string }
variable "primary_region" { type = string; default = "us-central1" }
variable "replica_region" { type = string; default = "us-east1" }

main.tf

provider "google" {
  project = var.project_id
}

# Need a random suffix because SQL Instance names cannot be fully reused for 7 days post-deletion
resource "random_id" "db_name_suffix" {
  byte_length = 4
}

# 1. Primary Highly Available Instance
resource "google_sql_database_instance" "primary" {
  name             = "ecommerce-primary-${random_id.db_name_suffix.hex}"
  region           = var.primary_region
  database_version = "MYSQL_8_0"

  settings {
    # HA requires at least a standard tier, micro instances don't fully support HA properly in prod
    tier              = "db-n1-standard-1"
    availability_type = "REGIONAL" # Cross-zone HA
    
    backup_configuration {
      enabled            = true
      binary_log_enabled = true # STRICTLY REQUIRED for creating read replicas
    }
  }
  
  deletion_protection = false # Set false for easy lab cleanup
}

# 2. Cross-Region Read Replica
resource "google_sql_database_instance" "read_replica" {
  name                 = "ecommerce-replica-${random_id.db_name_suffix.hex}"
  region               = var.replica_region
  database_version     = "MYSQL_8_0"
  
  # This links the replica back to the primary instance
  master_instance_name = google_sql_database_instance.primary.name

  # Replicas don't need HA themselves unless you plan to promote them
  settings {
    tier              = "db-n1-standard-1"
    availability_type = "ZONAL"
  }
  
  deletion_protection = false
}

outputs.tf

output "primary_ip_address" {
  value       = google_sql_database_instance.primary.public_ip_address
  description = "Connection IP for your Application (Writes/Reads)"
}

output "replica_ip_address" {
  value       = google_sql_database_instance.read_replica.public_ip_address
  description = "Connection IP for Analytics team (Reads Only)"
}

Workflow Commands

terraform init

# NOTE: Deploying an HA primary AND a Read Replica can take 15 to 25 minutes to complete!
terraform apply -var="project_id=my-gcp-project-123"

terraform destroy -var="project_id=my-gcp-project-123"

Related Posts