Terraform for CloudSQL deployments

Terraform is an infrastructure automation tool to provision and manage resources in the cloud. I've been particularly interested and enjoyed using Terraform when provisioning cloud infrastructure.

In this post I will show how to codify provisioning of a MySQL with replicas in GCP CloudSQL using Terraform from your local system. Please note that I will not be using dynamic configuration in this blog post.

First, make sure you installed the tool itself. Installation instructions can be found in the official documentation page. It is also required to install the GCloud SDK, follow the instructions in the official documentation. Moving forward, I'll assume you've already set up the gcloud sdk and terraform correctly in your system.

Create the provider.yaml file, copy the YAML code below and then run terraform init to initialize your setup.

terraform {
    backend "gcs" {
        bucket = "your-bucket-name"
        prefix = "your-bucket-prefix"
    }

    required_providers {
        google = {
            source = "hashicorp/google"
            version = ">= 5.2.0"
        }
    }
}

provider "google" {
    project = "your-project-id"
    region = "your-region-id"
}

Next, let's create the primary CloudSQL instance with a random suffix id. Create a main.tf file and paste the code below.

locals {
    resource = "cloudsql"
    env      = "production"
}

resource "google_sql_database_instance" "mysql_primary" {
    name                = "${local.resource}-${local.env}-source-${substr(sha256("${local.resource}-${local.env}-primary"), 2, 6)}"
    database_version    = "MYSQL_8_0_33"
    deletion_protection = false

    settings {
        tier                        = "db-custom-4-15360"
        activation_policy           = "ALWAYS"
        availability_type           = "REGIONAL"
        deletion_protection_enabled = false
        disk_autoresize             = true
        disk_size                   = 100
        disk_type                   = "PD_SSD"
        edition                     = "ENTERPRISE"

        backup_configuration {
            binary_log_enabled             = true
            enabled                        = true
            start_time                     = "04:00"
            transaction_log_retention_days = 3

            backup_retention_settings {
                retained_backups = 7
                retention_unit   = "COUNT"
            }
        }

        ip_configuration {
            ipv4_enabled = true
        }

        insights_config {
            query_insights_enabled = false
        }
    }
}

This resource block deploys a MySQL instance with 4 vCPUs and 16G RAM, at least 100G of persistent solid state drive auto-resizable running MySQL version 8.0.33 (as of this writing). Regional high availability is enabled with three days binlog retention and seven days of backup retention.

Let's create a super user since the default root@% user with no password will be deleted by Terraform on instance creation. Append the following code in the same main.tf file. Ideally we would want the password to be stored on GCP Secrets Manager to avoid secrets sprawl.

resource "google_sql_user" "admin_user" {
    name     = "your-admin-user"
    instance = google_sql_database_instance.mysql_primary.name
    host     = "cloudsqlproxy~%"
    password = "your-super-secret-password"
}

Ok! At this point we have the primary MySQL instance and the super user resource added.

Now let's add the replica instances. Append the following code in the main.tf file.

resource "google_sql_database_instance" "replica_1" {
    name                 = "${local.resource}-${local.env}-replica-${substr(sha256("${local.resource}-${local.env}-replica_1"), 2, 6)}"
    database_version     = "MYSQL_8_0_33"
    master_instance_name = google_sql_database_instance.mysql_primary.name
    deletion_protection  = false

    settings {
        tier                        = "db-custom-4-15360"
        activation_policy           = "ALWAYS"
        availability_type           = "ZONAL"
        deletion_protection_enabled = false
        disk_autoresize             = true
        disk_size                   = 100
        disk_type                   = "PD_SSD"
        edition                     = "ENTERPRISE"

        backup_configuration {
            binary_log_enabled             = true
            enabled                        = false
            transaction_log_retention_days = 3
        }

        ip_configuration {
            ipv4_enabled = true
        }

        insights_config {
            query_insights_enabled = false
        }
    }

    replica_configuration {
        master_heartbeat_period = 500
        connect_retry_interval  = 30
    }
}

Add as many replicas as you need with the same resource block just changing the first two lines from "replica-1" to "replica-N" where N is the sequence of the replica eg. replica_2, replica_3, replica_4.

resource "google_sql_database_instance" "replica_2" {
    name    = "${local.resource}-${local.env}-replica-${substr(sha256("${local.resource}-${local.env}-replica_2"), 2, 6)}"
    ...
}

The reason behind using separate resource {} blocks for each instance is to have full control over version upgrades instead of just upgrading the instances willy-nilly and cause an outage in production.

The next steps from here is to implement the code by running the following commands.

terraform plan -out tfplan
terraform apply tfplan

To cleanup, just run the following command and type yes when prompted.

terraform destroy

Thanks for reading!