Creating a Google Cloud SQL Instance and Loading Data

30 minutes
  • 3 Learning Objectives

About this Hands-on Lab

In this lab, we get hands-on with Google’s Cloud SQL basics by creating a MySQL database and then securely connecting to the database with a service account. We also upload some pre-generated data and run some simple queries.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Create a MySQL 2nd Generation Cloud SQL Instance

In your GCP console, create a new MySQL Cloud SQL instance. While creating it, make sure to do the following:

  1. Set the Instance ID to forumndb.
  2. Create a password for root and make a note of it for a later objective.
  3. Leave all other options as their defaults.

Note: It will take up to 10 minutes to create the database instance. You can complete the next objective while you wait.

Create a VM to Run a Secure MySQL Client

There are three tasks in this objective:

  1. First, we’ll create the VM with the following:
    1. Give the instance the name mysql-client.
    2. Change the Machine type to e2-small.
    3. Make sure the Boot Disk is set to Debian GNU/Linux 10 (buster) and not a different image.
  2. Create the service account used to connect with Cloud SQL securely:
    1. Set the Service Account Name to forumdb-access.
    2. Under Grant this service account access to project set it to Cloud SQL Client.
    3. Create a JSON key by clicking the 3 vertical dots in the Actions column in the row of your new service account and follow the dialogue.
  3. Upload the key to our VM and configure the MySQL Client and Cloud SQL Proxy:
    1. Go back to our mysql-client vm and click the SSH button.
    2. Using the menu in the top-right of the terminal window (shaped like a cog), upload the JSON key file that you just downloaded.
    3. Upload the forumdb.sql file for this lab (alternatively, clone the git repo into your VM).
    4. Update packages on the VM.
    5. Install the MySQL client.
    6. Download the Cloud SQL proxy from Google.
    7. Make the file executable.

Your VM is now ready for the next objective.

Load and Query the SQL Data

For this objective, we must complete the following:

  1. Enable an API for the Cloud SQL Proxy and grab the connection name for our DB:
    1. In your GCP console, from the Dashboard, enable the Cloud SQL Admin API.
    2. From the GCP console menu, go to the SQL page and select the forumdb instance.
    3. Under Connect to this instance, locate the Connection name and click the clipboard icon to copy it.
  2. Run the Cloud SQL proxy to create a secure connection to the database using the service account we created:
    1. In your SSH terminal, run the cloud_sql_proxy command with:
      • Your instances set to the Connection name.
      • =tcp:3306 added to the end of the Connection name.
      • Your credential_file set to the filename for your JSON key file.
      • End the command with & to run it in the background.
      • Make sure you enter this command all on one line.
      • Check for the message "Ready for new connections".
    2. Use the mysql client to connect to your database as root, connecting to the localhost to use the Cloud SQL proxy. You will need the root password we created in the first objective.
    3. Create a database called forum from within the mysql client.
    4. Type exit to return to the command line. Press the up arrow to retrieve the last command, and add a database name and SQL dump import to the end of the mysql command. Make sure the forumdb.sql file is in your working directory when you run this command.
    5. Connect to the database again.
    6. Run some simple SQL commands to query the new database.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!