Managing Cloud SQL with Terraform

In this guide, we’ll explore how to manage Google Cloud SQL using Terraform.

Video Tutorial

Prerequisites

• Google Cloud SDK installed and configured
• Terraform installed (version 1.0.0 or later)
• A GCP project with billing enabled
• Basic understanding of databases

Project Structure

.
├── main.tf                   # Main Terraform configuration file
├── variables.tf              # Variable definitions
├── outputs.tf               # Output definitions
├── terraform.tfvars         # Variable values
└── modules/
    └── cloudsql/
        ├── main.tf          # Cloud SQL specific configurations
        ├── variables.tf      # Module variables
        ├── databases.tf     # Database configurations
        └── outputs.tf       # Module outputs

Provider Configuration

terraform {
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 4.0"
    }
  }
}

provider "google" {
  project = var.project_id
  region  = var.region
}

Variables

variable "project_id" {
  description = "The ID of the GCP project"
  type        = string
}

variable "region" {
  description = "The region to deploy resources to"
  type        = string
  default     = "us-central1"
}

variable "instance_name" {
  description = "Name of the Cloud SQL instance"
  type        = string
}

variable "database_version" {
  description = "The MySQL or PostgreSQL version to use"
  type        = string
  default     = "POSTGRES_14"
}

variable "tier" {
  description = "The machine type to use"
  type        = string
  default     = "db-f1-micro"
}

Network Configuration

resource "google_compute_network" "private_network" {
  name                    = "private-network"
  auto_create_subnetworks = false
}

resource "google_compute_subnetwork" "private_subnet" {
  name          = "private-subnet"
  ip_cidr_range = "10.0.0.0/24"
  region        = var.region
  network       = google_compute_network.private_network.id
}

resource "google_compute_global_address" "private_ip_address" {
  name          = "private-ip-address"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.private_network.id
}

resource "google_service_networking_connection" "private_vpc_connection" {
  network                 = google_compute_network.private_network.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_address.name]
}

Cloud SQL Instance

resource "google_sql_database_instance" "instance" {
  name             = var.instance_name
  region           = var.region
  database_version = var.database_version

  depends_on = [google_service_networking_connection.private_vpc_connection]

  settings {
    tier = var.tier

    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.private_network.id
    }

    backup_configuration {
      enabled    = true
      start_time = "02:00"
    }

    maintenance_window {
      day          = 7    # Sunday
      hour         = 3    # 3 AM
      update_track = "stable"
    }
  }

  deletion_protection = true
}

resource "google_sql_database" "database" {
  name     = "example-database"
  instance = google_sql_database_instance.instance.name
}

resource "google_sql_user" "users" {
  name     = "example-user"
  instance = google_sql_database_instance.instance.name
  password = "changeme"  # Use a secure method to manage passwords
}

Outputs

output "instance_name" {
  value       = google_sql_database_instance.instance.name
  description = "The name of the Cloud SQL instance"
}

output "private_ip_address" {
  value       = google_sql_database_instance.instance.private_ip_address
  description = "The private IP address of the Cloud SQL instance"
}

output "connection_name" {
  value       = google_sql_database_instance.instance.connection_name
  description = "The connection name of the instance to be used in connection strings"
}

Best Practices

1. Security:

   • Use private IP
   • Implement SSL/TLS
   • Regular password rotation
   • Use IAM authentication

2. High Availability:

   • Enable automated backups
   • Configure failover replicas
   • Use appropriate regions

3. Performance:

   • Choose appropriate machine type
   • Monitor query performance
   • Configure maintenance windows

4. Cost Optimization:

   • Right-size instances
   • Use committed use discounts
   • Monitor usage patterns

Common Operations

Creating Resources

terraform init
terraform plan
terraform apply

Connecting to Database

# Using Cloud SQL Proxy
cloud_sql_proxy -instances=<CONNECTION_NAME>=tcp:5432

Destroying Resources

# Remove deletion protection first
terraform apply -var="deletion_protection=false"
terraform destroy

Best Practices and Tips

1. Backup Strategy:

   • Enable point-in-time recovery
   • Test backup restoration
   • Configure backup retention

2. Monitoring:

   • Set up alerts
   • Monitor performance metrics
   • Track connection counts

3. Security:

   • Regular security audits
   • Implement network security
   • Manage access controls

Conclusion

You’ve learned how to set up and manage Google Cloud SQL using Terraform. This setup provides:

• Automated database deployment
• Secure and scalable infrastructure
• Best practices implementation
• Easy database management and maintenance