Wordpress Deployment on GKE + CloudSQL MYSQL Instance -- MySQL Connection Error: (2002) Connection refused

Question

I am attempting to complete the following tutorial for deploying Wordpress on GKE: https://cloud.google.com/kubernetes-engine/docs/tutorials/persistent-disk

I have used terraform for provisioning the gcp resources, instead of gcp as the tutorial recommends. Here is the deployment that is resulting in a CrashLoopBackOff state.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: wordpress
  labels:
    app: wordpress
spec:
  replicas: 1
  selector:
    matchLabels:
      app: wordpress
  template:
    metadata:
      labels:
        app: wordpress
    spec:
      containers:
        - image: wordpress
          name: wordpress
          env:
          - name: WORDPRESS_DB_HOST
            value: 127.0.0.1:3306
          # These secrets are required to start the pod.
          - name: WORDPRESS_DB_USER
            valueFrom:
              secretKeyRef:
                name: cloudsql-db-credentials
                key: username
          - name: WORDPRESS_DB_PASSWORD
            valueFrom:
              secretKeyRef:
                name: cloudsql-db-credentials
                key: password
          ports:
            - containerPort: 80
              name: wordpress
          volumeMounts:
            - name: wordpress-persistent-storage
              mountPath: /var/www/html
        # Change archtek-wordpress:us-west1:archtek-wordpress-postgres-instance here to include your GCP
        # project, the region of your Cloud SQL instance and the name
        # of your Cloud SQL instance. The format is
        # ::
        - name: cloudsql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.11
          command: ["/cloud_sql_proxy",
                    "-instances=archtek-wordpress:us-west1:archtek-wordpress-mysql-instance=tcp:3306",
                    # If running on a VPC, the Cloud SQL proxy can connect via Private IP. See:
                    # https://cloud.google.com/sql/docs/mysql/private-ip for more info.
                    # "-ip_address_types=PRIVATE",
                    "-credential_file=/secrets/cloudsql/key.json"]
          securityContext:
            runAsUser: 2  # non-root user
            allowPrivilegeEscalation: false
          volumeMounts:
            - name: cloudsql-instance-credentials
              mountPath: /secrets/cloudsql
              readOnly: true
          imagePullPolicy: Always
      volumes:
        - name: wordpress-persistent-storage
          persistentVolumeClaim:
            claimName: wordpress-volumeclaim
        - name: cloudsql-instance-credentials
          secret:
            secretName: cloudsql-instance-credentials

When I describe the pod, I see the following in the logs:

wordpress-54c68dbf59-5djfx wordpress MySQL Connection Error: (2002) Connection refused

To rule out the idea that the credentials are invalid, I took the username and password used to create cloudsql-db-credentials, the k8s secret referenced in my deployment yaml, and ran this.

$: gcloud sql connect archtek-wordpress-mysql-instance -u wordpress

I can connect, no problem. But what I discovered I also cannot do is this:

$: mysql -u wordpress -p'$CLOUD_SQL_PASSWORD' \                                                                    ()
    -h 35.197.7.98       -P 3306 \
    -D archtek-wordpress:us-west1:archtek-wordpress-mysql-instance -v

which returns:

ERROR 2003 (HY000): Can't connect to MySQL server on '35.197.7.98' (60)

I know that when using the gcloud client to connect to a cloudsql database, it whitelists for ip for a 5 minute period prior to authentication, which might explain why the mysql client fails to authenticate. However, I'm not sure if this rationale holds up for my deployment in the cluster. Does it also need to be whitelisted for cloudsql to accept auth requests?

Here is the terraform file for provisioning the cloudsql instance:

resource "google_sql_database_instance" "postgres" {
  name             = "archtek-wordpress-mysql-instance"
  database_version = "MYSQL_5_7"
  settings {
    tier              = "db-f1-micro"
    availability_type = "ZONAL"
  }
}

Answer 1

The error you encountered when trying to connect outside from GKE cluster:

ERROR 2003 (HY000): Can't connect to MySQL server on '35.197.7.98' (60)

It's because the site (IP) you are connecting from is not authorized to do that. Using:

• $ gcloud sql connect ...

is allowing to connect to a SQL instance for a 5 minute period.

You don't need to authorize network you are connecting from when using CloudSQL proxy within GKE.

You can see the authorization part in GCP -> SQL -> Instance -> Connections.

Also, you can see the logs of your pods (wordpress and cloudsql-proxy) to determine which pod is causing problems (other than $ kubectl describe):

• $ kubectl logs POD_NAME -c wordpress

---

More reference on CloudSQL and sql-proxy:

• Cloud.google.com: SQL: MySQL: Diagnose issues

• Cloud.google.com: SQL: MySQL: sql-proxy: Troubleshooting

---

Assuming that you created your user for CloudSQL instance with .tf file from your github page, there could be a reason why it's failing. The part responsible for creating a user wordpress has wrong host parameter (below example is edited):

resource "google_sql_user" "users" {
  name     = "wordpress"
  instance = google_sql_database_instance.postgres.name
  # host     = "*" <- BAD
  host      = "%" # <- GOOD
  password = random_password.password.result
}

I couldn't connect to the server with a following parameter: host = "*". Changing it from "*" to "%" solved my issue.

---

I've managed to create .tf files which are similar to the parts of the official GKE guide:

• Cloud.google.com: Kubernetes Engine: Tutorials: Persistent disk

Guide to connect Terraform to the GCP project:

• Terraform.io: Google: Guides: Getting started

Files used:

• main.tf
• vpc.tf - create a VPC (basing on github linked in the comment)
• gke.tf - create a GKE cluster in new VPC
• mysql.tf - create a CloudSQL instance and a user wordpress
• pvc.tf - create a PVC for Wordpress deployment
• sa.tf - create a ServiceAccount and bind it with permission required to access CloudSQL instances
• secret.tf - create a key for above ServiceAccount and create Kubernetes secrets for Wordpress and CloudSQL pods
• deployment.tf - create a deployment that will run Wordpress and cloudsql-proxy

I ran below commands each time I added a new file (in above order):

• $ terraform init
• $ terraform apply

main.tf:

provider "google" {
  project = "ENTER-YOUR-PROJECT-ID"
  region  = "europe-west3"
  zone    = "europe-west3-c"
}

variable project {
  type = string
  default = "ENTER-YOUR-PROJECT-ID"
}

variable zone {
  type = string
  default = "europe-west3-c"
}

variable region {
  type = string
  default = "europe-west3"
}

vpc.tf:

resource "google_compute_network" "terraform-network" {
  name                    = "terraform-network"
  auto_create_subnetworks = "false"
}

resource "google_compute_subnetwork" "terraform-subnet" {
  name          = "terraform-subnet"
  region        = var.region
  network       = google_compute_network.terraform-network.name
  ip_cidr_range = "10.0.0.0/24"

}

gke.tf:

resource "google_container_cluster" "gke-terraform" {
  name     = "gke-terraform"
  location = var.zone
  initial_node_count = 1

  network    = google_compute_network.terraform-network.name
  subnetwork = google_compute_subnetwork.terraform-subnet.name
}

I also ran:

• $ gcloud container clusters get-credentials gke-terraform --zone=europe-west3-c

mysql.tf

resource "google_sql_database_instance" "cloudsql" {
  name             = "cloudsql-terraform"
  database_version = "MYSQL_5_7"
  settings {
    tier              = "db-f1-micro"
    availability_type = "ZONAL"
  }
}

data "google_sql_database_instance" "cloudsql" {
    name = "cloudsql-terraform"
}

resource "random_password" "wordpress-cloudsql-password" {
  length           = 18
  special          = true
  override_special = "_%@"
}


resource "local_file" "password-file" {
  content  = random_password.wordpress-cloudsql-password.result
  filename = "./password-file"
}

resource "google_sql_user" "cloudsql-wordpress-user" {
  name     = "wordpress"
  instance = google_sql_database_instance.cloudsql.name
  host     = "%"
  password = random_password.wordpress-cloudsql-password.result
}

pvc.tf:

resource "google_compute_disk" "terraform-pd" {
  name  = "terraform-disk"
  type  = "pd-standard"
  zone  = "europe-west3-c"
}

resource "kubernetes_persistent_volume" "terraform-pv" {

  metadata {
    name = "wordpress-pv"
  }
  spec {
    capacity = {
      storage = "10Gi"
    }
    storage_class_name = "standard"
    access_modes = ["ReadWriteOnce"]
    persistent_volume_source {
      gce_persistent_disk {
        pd_name = google_compute_disk.terraform-pd.name
      }
    }
  }
}

resource "kubernetes_persistent_volume_claim" "terraform-pvc" {
  metadata {
    name = "wordpress-pvc"
  }
  spec {
    access_modes = ["ReadWriteOnce"]
    storage_class_name = "standard"
    resources {
      requests = {
        storage = "10Gi"
      }
    }
    volume_name = kubernetes_persistent_volume.terraform-pv.metadata.0.name
  }
}

sa.tf:

resource "google_service_account" "cloudsql-proxy-terraform" {
  account_id   = "cloudsql-proxy-terraform"
  display_name = "cloudsql-proxy-terraform"
}

data "google_service_account" "cloudsql-proxy-terraform" {
  account_id = "cloudsql-proxy-terraform"
}

resource "google_project_iam_binding" "cloudsql-proxy-binding" {
  project = var.project
  role    = "roles/cloudsql.client"

  members = [
    "serviceAccount:${google_service_account.cloudsql-proxy-terraform.email}",
  ]
}

secret.tf:

resource "google_service_account_key" "cloudsql-proxy-key" {
  service_account_id = google_service_account.cloudsql-proxy-terraform.name
}

resource "kubernetes_secret" "cloudsql-instance-credentials-terraform" {
  metadata {
    name = "cloudsql-instance-credentials-terraform"
  }
  data = {
    "key.json" = base64decode(google_service_account_key.cloudsql-proxy-key.private_key)
  }
}

resource "kubernetes_secret" "cloudsql-db-credentials-terraform" {
  metadata {
    name = "cloudsql-db-credentials-terraform"
  }
  data = {
    "username" = "wordpress"
    "password" = random_password.wordpress-cloudsql-password.result
  }
}

deployment.tf:

resource "kubernetes_deployment" "wordpress-deployment" {
  metadata {
    name = "wordpress-deployment"
    labels = {
      app = "wordpress"
    }
  }

  spec {
    
    replicas = 1

    selector {
      match_labels = {
        app = "wordpress"
      }
    }

    template {
      metadata {
        labels = {
          app = "wordpress"
        }
      }

      spec {

          container {

            image = "wordpress"
            name  = "wordpress"

            env {
                  name = "WORDPRESS_DB_HOST"
                  value = "127.0.0.1:3306"
            }

            env {
                  name = "WORDPRESS_DB_USER"
                  value_from {
                      secret_key_ref {
                          name = kubernetes_secret.cloudsql-db-credentials-terraform.metadata.0.name
                          key = "username"
                      }  
                  }
            }

            env {
                  name = "WORDPRESS_DB_PASSWORD"
                  value_from  {
                      secret_key_ref  {
                          name = kubernetes_secret.cloudsql-db-credentials-terraform.metadata.0.name
                          key = "password"
                      }  
                  }
            }

            port {
              name           = "http"
              container_port = 80
              protocol       = "TCP"
            }

            volume_mount {
              mount_path = "/var/www/html"
              name       = "wordpress-persistent-storage"
            }
            
            

          }
          
          container { 
            image = "gcr.io/cloudsql-docker/gce-proxy:1.11"
            name  = "cloudsql-proxy"

            command = ["/cloud_sql_proxy", 
            "-instances=${google_sql_database_instance.cloudsql.connection_name}=tcp:3306",
            "-credential_file=/secrets/cloudsql/key.json"]
            
            security_context {
                run_as_user = 2 
                allow_privilege_escalation = "false"
            }

            volume_mount {
                mount_path = "/secrets/cloudsql"
                name       = "cloudsql-instance-credentials-terraform"
                read_only  = "true"
            }



          }

        volume {
            name = "wordpress-persistent-storage"
            persistent_volume_claim {
              claim_name = "wordpress-pvc"
            } 
        }

        volume {
            name = "cloudsql-instance-credentials-terraform"
            secret {
                secret_name = "cloudsql-instance-credentials-terraform"
            }
        }
        
        }
      }
}
}

After checking if the resources created correctly ( $ kubectl logs POD_NAME -c CONTAINER_NAME) you can expose your Wordpress with:

• $ kubectl expose deployment wordpress-deployment --type=LoadBalancer --port=80