Using Cloud SQL with Kubernetes Engine

25 February 2021

The following paragraph will give you a brief idea about what exactly this blog is about:

After setting up a Kubernetes Deployment of WordPress connected to Cloud SQL via the SQL Proxy. The SQL Proxy will allow you to interact with a Cloud SQL instance like it was installed locally (localhost:3306), and even after you are on an unsecured port locally, the SQL proxy makes sure that you can connect to your cloud SQL instance over wire securely.

You will create following components:

• GKE cluster
• Cloud SQL instance
• Service account for pods to access cloud SQL
• Deploy WordPress on GKE cluster with SQL proxy as sidecar, connected to your cloud SQL instance

Using following 7 objective you can complete your task:

Activate Google Cloud Shell, and run the following command to list your account and project:

gcloud auth list

gcloud config list project

Obj. 1: Connect to the lab GKE cluster

Set environment variable for GKE zone and cluster

export my_zone=us-central1-a

export my_cluster=standard-cluster-1

Configure tab completion for the kubectl command-line tool.

source <(kubectl completion bash)

Configure access to your cluster for kubectl:

gcloud container clusters get-credentials $my_cluster --zone $my_zone

Clone the following directory and create a short link to working directory:

git clone https://github.com/GoogleCloudPlatform/training-data-analyst

ln -s ~/training-data-analyst/courses/ak8s/v1.1 ~/ak8s

Obj. 2: Enable Cloud SQL APIs

1. In the Google Cloud Console, on the Navigation menu, click APIs & Services.
2. Click + Enable APIs and Services.
3. For Search for APIs & Services, type SQL and then click the Cloud SQL API tile.
4. Click Enable to enable Cloud SQL API.
5. Repeat the above step to enable Cloud SQL Admin API.

Obj. 3:Create a Cloud SQL instance 

Run the following command to create the instance:

gcloud sql instances create sql-instance --tier=db-n1-standard-2 --region=us-central1

In Google cloud console, navigate to SQL and you will see a sql-instance listed.

ADD USER ACCOUNT and create an account, using sqluser as the username and sqlpassword as the password.

Create an environment variable to hold your Cloud SQL instance name:

export SQL_NAME=[Cloud SQL Instance Name]

Connect to your Cloud SQL instance:

gcloud sql connect sql-instance

Create the database required for WordPress and select the wordpress database:

create database wordpress;

use wordpress;

show tables;




Exit MySQL client:

exit;

Obj. 4: Prepare a Service Account with permission to access Cloud SQL

1. In the Google Cloud Console navigate to IAM & admin> Service accounts.
2. Create a service account with a name sql-access.
3. Select role for Cloud SQL client and click done.
4. Service account will appear in the Action column and create a JSON key.

Obj. 5: Create Secrets

To create a Secret for your MySQL credentials, enter the following in the Cloud Shell:

kubectl create secret generic sql-credentials \

    --from-literal=username=sqluser\

    --from-literal=password=sqlpassword

Upload the file credential.json file, in the cloudshell which are credentials for the service account. Move the credentials file to the current working directory.

Create a Secret for your Google Cloud Service Account credentials using the following command:

kubectl create secret generic google-credentials\

    --from-file=key.json=credentials.json

Obj. 6: Deploy the SQL Proxy agent as a sidecar container

A sample deployment manifest file called sql-proxy.yaml is available which you can deploy on a WordPress application container with a proxy agent as a side cart container.

Make sure you make the necessary changes such as name and values (IP address) and add the port 3306.

Use the sed command to update the placeholder variables for SQL connection name to your Cloud SQL instance:

sed -i 's/<INSTANCE_CONNECTION_NAME>/'"${SQL_NAME}"'/g'\

   Sql-proxy.yaml

Deploy the application and query the status of deployment:

kubectl apply -f sql-proxy.yaml

kubectl get deployment wordpress

List the service in your GKE cluster:

kubectl get services

The external LoadBalancer ip-address for the wordpress-service is the address you use to connect to your WordPress blog.

Obj. 7: Connect to your WordPress instance

In a new browser tab, connect to your WordPress site using external Loadbalancer Ip-address.

Enter Username and Password to administrate the site.

Switch back to your cloudshell and connect to your Cloud SQL instance.

gcloud sql connect sql-instance

If prompted use root password. Select wordpress database:

use wordpress;

show tables;

This will show the number of new database tables.

List all of the WordPress user table entries:

select * from wp_users;

This will list the database record for the WordPress entries admin accounts use to initialize WordPress.

exit;

The following tasks will be completed during this process:

• Create a Cloud SQL instance and database for WordPress
• Create credentials and Kubernetes Secrets for application authentication
• Configure a Deployment with a WordPress image to use SQL Proxy
• Install SQL Proxy as a sidecar container and use it to provide SSL access to a CloudSQL instance external to the GKE Cluster