Overview

In this lab, you set up a Kubernetes Deployment of WordPress connected to Cloud SQL via the SQL Proxy. The SQL Proxy lets you interact with a Cloud SQL instance as if it were installed locally (localhost:3306), and even though you are on an unsecured port locally, the SQL Proxy makes sure you are secure over the wire to your Cloud SQL Instance.

To complete this lab you'll create several components. First you create a GKE cluster, next you create a Cloud SQL Instance to connect to, and a Service Account to provide permission for your Pods to access the Cloud SQL Instance, this will be authenticated using Workload Identity. Finally you deploy WordPress on your GKE cluster, with the SQL Proxy as a Sidecar, connected to your Cloud SQL Instance.

Objectives

In this lab, you learn how to perform the following tasks:

• Create a Cloud SQL instance and database for Wordpress.
• Create credentials and Kubernetes Secrets for application authentication.
• Configure Workload Identity.
• 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.

Lab setup

Access Qwiklabs

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details panel.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details panel.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left, or type the service or product name in the Search field.

After you complete the initial sign-in steps, the project dashboard appears.

Activate Google Cloud Shell

Google Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud.

Google Cloud Shell provides command-line access to your Google Cloud resources.

1. In Cloud console, on the top right toolbar, click the Open Cloud Shell button.

   

2. Click Continue.

It takes a few moments to provision and connect to the environment. When you are connected, you are already authenticated, and the project is set to your PROJECT_ID. For example:

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

• You can list the active account name with this command:

gcloud auth list

Output:

Credentialed accounts: - @.com (active)

Example output:

Credentialed accounts: - google1623327_student@qwiklabs.net

• You can list the project ID with this command:

gcloud config list project

Output:

[core] project =

Example output:

[core] project = qwiklabs-gcp-44776a13dea667a6 Note: Full documentation of gcloud is available in the gcloud CLI overview guide .

Task 1. Connect to the lab GKE cluster

1. In Cloud Shell, type the following command to set the environment variable for the Google Cloud zone and cluster name:

export my_cluster=autopilot-cluster-1 export my_project=$(gcloud config get-value project) export my_region={{{ project_0.default_region | "Region" }}}

2. Configure tab completion for the kubectl command-line tool:

source <(kubectl completion bash)

3. Configure access to your cluster for kubectl:

gcloud container clusters get-credentials $my_cluster --region $my_region

Task 2. Enable Cloud SQL APIs

1. In the Google Cloud console, in 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.

If the API is already enabled, a Manage button appears instead, with an API enabled message. In that case, no action is required.

5. Repeat the above step to enable sqladmin API.

Task 3. Create a Cloud SQL instance

1. In the Cloud Shell, run the following command to create the instance:

gcloud sql instances create sql-instance --tier=db-n1-standard-2 --region=$my_region

2. In the Google Cloud console, navigate to SQL.
3. You should see sql-instance listed , click on the name, and then click on the Users menu.

Note: You have to wait a few minutes for the Cloud SQL instance to be provisioned.

When you see the existing root user listed you can proceed to the next step.

4. Go to Users and click Add User Account and create an account, using sqluser as the username and sqlpassword as the password.

5. Leave the Host name option set to Allow any host (%). and click ADD.

6. Go back to Overview menu, still in your instance (sql-instance), and copy your Instance connection name.

You will probably need to scroll down a bit to see it.

7. Create an environment variable to hold your Cloud SQL instance name, substituting the placeholder with the name you copied in the previous step:

export SQL_NAME=[Cloud SQL Instance Name]

8. Your command should look similar to the following:

$ export SQL_NAME=qwiklabs-gcp-d03ee58ad9ad507e:us-central1:sql-instance

9. Connect to your Cloud SQL instance:

gcloud sql connect sql-instance

10. When prompted to enter the root password press enter. The root SQL user password is blank by default.

The mysql> prompt appears indicating that you are now connected to the Cloud SQL instance using the MySQL client.

11. Create the database required for Wordpress. This is called wordpress by default:

create database wordpress;

12. Select the wordpress database:

use wordpress;

13. Select the wordpress database:

show tables;

This will report Empty set as you have not created any tables yet.

14. Exit the MySQL client:

exit;

Click Check my progress to verify the objective.

Create a Cloud SQL Instance.

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

1. To create a Service Account, in the Google Cloud console navigate to IAM & Admin> Service Accounts.
2. Click + Create Service Account.
3. Specify the Service account name called sql-access then click Create and Continue.
4. Click Select a role.
5. Search for Cloud SQL, select Cloud SQL Client and click Continue.
6. Click Done.

Click Check my progress to verify the objective.

Create Service Account.

Task 5. Create Kubernetes Service Account and configure Workload Identity

1. In the Cloud Shell, run the following command to create the Kubernetes Service Account:

kubectl create serviceaccount gkesqlsa

2. In the Cloud Shell, run the following command to bind the Google Cloud service account with the Kubernetes Service Account you just created:

gcloud iam service-accounts add-iam-policy-binding \ --role="roles/iam.workloadIdentityUser" \ --member="serviceAccount:$my_project.svc.id.goog[default/gkesqlsa]" \ sql-access@$my_project.iam.gserviceaccount.com

3. In the Cloud Shell, run the following command to annotate the Kubernetes Service Account with the details of the Google Cloud service account:

kubectl annotate serviceaccount \ gkesqlsa \ iam.gke.io/gcp-service-account=sql-access@$my_project.iam.gserviceaccount.com

Task 6. Create Secrets

You create two Kubernetes Secrets: one to provide the MySQL credentials and one to provide the Google credentials (the service account).

1. 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

If you used a different username and password when creating the Cloud SQL user accounts substitute those here.

Click Check my progress to verify the objective.

Create Secrets.

Task 7. Deploy the SQL Proxy agent as a sidecar container

Let's create a deployment manifest file called sql-proxy.yaml that deploys a demo Wordpress application container with the SQL Proxy agent as a sidecar container.

In the Wordpress container environment settings the WORDPRESS_DB_HOST is specified using the localhost IP address. The cloudsql-proxy sidecar container is configured to point to the Cloud SQL instance you created in the previous task. The database username and password are passed to the Wordpress container as secret keys, and Workload Identity is configured. A Service is also created to allow you to connect to the Wordpress instance from the internet.

Create and open a file called sql-proxy.yaml with nano using the following command:

nano sql-proxy.yaml

2. Once nano has opened, paste the following into the sql-proxy.yaml file:

apiVersion: apps/v1 kind: Deployment metadata: name: wordpress labels: app: wordpress spec: selector: matchLabels: app: wordpress template: metadata: labels: app: wordpress spec: serviceAccountName: gkesqlsa containers: - name: web image: gcr.io/cloud-marketplace/google/wordpress:6.1 #image: wordpress:5.9 ports: - containerPort: 80 env: - name: WORDPRESS_DB_HOST value: 127.0.0.1:3306 # These secrets are required to start the pod. # [START cloudsql_secrets] - name: WORDPRESS_DB_USER valueFrom: secretKeyRef: name: sql-credentials key: username - name: WORDPRESS_DB_PASSWORD valueFrom: secretKeyRef: name: sql-credentials key: password # [END cloudsql_secrets] # Change '<INSTANCE_CONNECTION_NAME>' here to include your Google Cloud # project, the region of your Cloud SQL instance and the name # of your Cloud SQL instance. The format is # $PROJECT:$REGION:$INSTANCE # [START proxy_container] - name: cloudsql-proxy image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.8.0 args: - "--structured-logs" - "--port=3306" - "<INSTANCE_CONNECTION_NAME>" securityContext: runAsNonRoot: true --- apiVersion: "v1" kind: "Service" metadata: name: "wordpress-service" namespace: "default" labels: app: "wordpress" spec: ports: - protocol: "TCP" port: 80 selector: app: "wordpress" type: "LoadBalancer" loadBalancerIP: ""

3. Press Ctrl+O, and then press Enter to save your edited file.

4. Press Ctrl+X to exit the nano text editor.

The important sections to note in this manifest are:

• In the spec section the Kubernetes Service Account is configured.
• In the Wordpress env section the variable WORDPRESS_DB_HOST is set to 127.0.0.1:3306. This will connect to a container in the same Pod listening on port 3306. This is the port that the SQL-Proxy listens on by default.
• In the Wordpress env section the variables WORDPRESS_DB_USER and WORDPRESS_DB_PASSWORD are set using values stored in the sql-credential Secret you created in the last task.
• In the cloudsql-proxy container section the command switch that defines the SQL Connection name, "INSTANCE_CONNECTION_NAME> contains a placeholder variable that is not configured using a ConfigMap or Secret and so must be updated directly in this example manifest to point to your Cloud SQL instance.
• The Service section at the end creates an external LoadBalancer called "wordpress-service" that allows the application to be accessed from external internet addresses.

1. Use sed to update the placeholder variable for the SQL Connection name to the instance name of your Cloud SQL instance:

sed -i 's/<INSTANCE_CONNECTION_NAME>/'"${SQL_NAME}"'/g'\ sql-proxy.yaml Note: The sed command in UNIX is stands for stream editor and it can perform many functions on files such as replace, insertion, or deletion. Though most common use of sed is for substitution. By using sed you can edit files even without opening them, which is much quicker way to find and replace something in file, than first opening that file in an editor and then changing it.

2. Deploy the application:

kubectl apply -f sql-proxy.yaml

3. Query the status of the Deployment:

kubectl get deployment wordpress Note: You need to repeat this command until you see that an instance is available.

Output:

NAME READY UP-TO-DATE AVAILABLE AGE wordpress 1/1 1 1 45s

4. List the services 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.

5. Repeat the command until you get an external address as shown here.

NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) kubernetes ClusterIP 10.12.0.1 443/TCP wordpress-service LoadBalancer 10.12.3.17 35.238.218.6 80:31095/TCP

Click Check my progress to verify the objective.

Deploy the SQL Proxy agent as a sidecar container.

Task 8. Connect to your Wordpress instance

1. Open a new browser tab and connect to your Wordpress site using the external LoadBalancer ip-address. This will start the initial Wordpress installation wizard.
2. Select English (United States) and click Continue.
3. Enter a sample name for the Site Title.
4. Enter a Username and Password to administer the site.
5. Enter an email address.

None of these values are particularly important, you will not need to use them.

6. Click Install Wordpress.

After a few seconds you will see the Success! Notification. You can log in if you wish to explore the Wordpress admin interface but it is not required for the lab.

The initialization process has created new database tables and data in the wordpress database on your Cloud SQL instance. You will now validate that these new database tables have been created using the SQL proxy container.

7. Switch back to the Cloud Shell and connect to your Cloud SQL instance:

gcloud sql connect sql-instance

8. When prompted to enter the root password press enter. The root SQL user password is blank by default.

The mysql> prompt appears indicating that you are now connected to the Cloud SQL instance using the MySQL client.

9. Select the wordpress database:

use wordpress;

10. Select the wordpress database:

show tables;

This will now show a number of new database tables that were created when Wordpress was initialized demonstrating that the sidecar SQL Proxy container was configured correctly:

MySQL [wordpress]> show tables; +-----------------------+ | Tables_in_wordpress | +-----------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_termmeta | | wp_terms | | wp_usermeta | | wp_users | +-----------------------+ 12 rows in set (0.19 sec)

11. List all of the Wordpress user table entries:

select * from wp_users;

This will list the database record for the Wordpress admin account showing the email you chose when initializing Wordpress.

12. Exit the MySQL client:

exit;

End your lab

When you have completed your lab, click End Lab. Google Cloud Skills Boost removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.