Overview

In this lab, you use Cloud SQL to create databases that are managed by Google. You create both PostgreSQL and MySQL databases, and you connect to those databases using the Google Cloud CLI and from a virtual machine.

Objectives

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

• Create a Cloud SQL PostgreSQL database.
• Connect to the database using the Cloud SDK.
• Create a Cloud SQL MySQL database using the CLI.
• Connect to the MySQL database from a virtual machine.

Setup and requirements

In this task, you use Qwiklabs and perform initialization steps for your lab.

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

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Task 1. Create a Cloud SQL PostgreSQL database

1. Open a new web browser window and navigate to the Google Cloud Console (console.cloud.google.com). Use the project selector to choose the first project with a leading name of 'qwiklabs-gcp.'

2. On the Navigation menu (), click SQL.

   If SQL is not listed in the navigation menu, click View All Products. In the All Products page, scroll to the section on Databases , and then click SQL.

3. Click Create Instance and choose the PostgreSQL option.

4. Under Choose a Cloud SQL edition select Enterprise.

5. Name the instance ID postgresql-db and enter a password you can remember (or if you prefer, click the Generate button and copy the generated password to a text file).

6. For Choose region and zonal availability set the region to and select Multiple zones.

7. Accept the rest of the defaults and click Create Instance.

Note: It will take 5-10 minutes for your database to be ready.

8. When the database is ready, click its name in the console to see its details.

Click Check my progress to verify the objective. Create a Cloud SQL PostgreSQL instance

Task 2. Connect to the database using the Cloud SDK

1. In the Connect to this instance section, click the Open Cloud Shell link.

This opens Cloud Shell at the bottom of the window.

2. If prompted, click Continue.

Notice that the connection command is already entered at the prompt.

3. Press ENTER to run the command and then click Authorize if prompted.

It will take a little while for Cloud Shell's IP address to be allowlisted by the Cloud SQL network.

4. When prompted, enter your password.

5. To see the databases, type the following (a backslash and the lowercase letter L):

\l

6. To create a database, enter the following command:

CREATE DATABASE petsdb;

7. Confirm that it worked:

\l

8. To connect to the database, enter the following command, and then enter your password when prompted:

\c petsdb;

9. Create a table:

CREATE TABLE Pets(ID INT PRIMARY KEY NOT NULL, Name TEXT, Breed TEXT);

10. Add a record:

INSERT INTO Pets (ID, Name, Breed) Values (1, 'Noir', 'Schnoodle');

11. Run a query:

SELECT * FROM Pets;

12. To disconnect from the database, press CTRL+C and return to Cloud Shell.

Click Check my progress to verify the objective. Create a database on Cloud SQL PostgreSQL instance

Task 3. Create a Cloud SQL MySQL database using the CLI

1. At the Cloud Shell command prompt, enter the following command to create a MySQL database using the CLI:

gcloud sql instances create mysql-db --tier=db-n1-standard-1 --zone={{{ project_0.default_zone | "ZONE" }}}

It will take a few minutes for the database to be ready.

2. When the command completes, make note of the database server's primary IP address. Copy and paste this address in a text file, because you will need it later.

3. Enter the following command to set the password for the root account. Make the password something you can remember.

gcloud sql users set-password root --host=% --instance=mysql-db --password=your-password-here

4. Enter the following command to connect to your Cloud SQL database using the CLI. It will take a little while for Cloud Shell's IP address to be allowlisted by the Cloud SQL network.

gcloud sql connect mysql-db --user=root --quiet

5. When prompted, enter your password.

6. At the mysql prompt, type the following to ensure that it works:

SHOW DATABASES;

7. To return to the Cloud Shell command prompt, type exit.

Click Check my progress to verify the objective. Create a Cloud SQL MySQL database using the CLI

Task 4. Connect to the MySQL database from a virtual machine

1. In Cloud Shell, enter the following command to create a Debian 11 virtual machine that you can use as a test client:

gcloud compute instances create test-client --zone={{{ project_0.default_zone | "ZONE" }}} --image-family=debian-11 --image-project=debian-cloud --machine-type=e2-micro

2. When the command completes, copy the external IP address to the clipboard. You use this IP address to allow this machine to access to the database.

3. Return to the Console and the Cloud SQL service, and then select the mysql-db database to see its details.

4. Click Edit from the Overview list at the top of the page.

5. Open the Connections section, and then click Add Network under Authorized networks. Name the new connection VM and paste the virtual machine's external IP address in the Network text box.

6. Click Done, and then click Save.

7. Once the update is complete, return to the Cloud Shell, enter the following command to SSH into the test client:

gcloud compute ssh test-client --zone={{{ project_0.default_zone | "ZONE" }}}

8. To install the MySQL client software on this machine, run the following commands:

sudo apt-get update sudo apt-get install -y default-mysql-client

9. To log on to the database server, use the command below but change the IP address to your Cloud SQL database's IP address. (You should have pasted this address in a text file earlier in the lab. If not, you can find it in the Cloud SQL console.)

mysql --host=[Database Public IP Address] --user=root --password

10. At the mysql prompt, type the following to ensure that it works:

SHOW DATABASES;

11. To return the the Cloud Shell command prompt, type exit.

Click Check my progress to verify the objective. Connect to the MySQL database from a virtual machine

Congratulations! You used Cloud SQL to create databases that are managed by Google. You created both PostgreSQL and MySQL databases, and you connected to those databases using the Google Cloud CLI and from a virtual machine.

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.