Skip to content

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.
  • Labs icon Lab
  • A Cloud Guru

Creating a Google Cloud SQL Instance and Loading Data

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.


Path Info

Clock icon Beginner
Clock icon 30m
Clock icon Apr 09, 2021

Contact sales

By filling out this form and clicking submit, you acknowledge our privacy policy.

Table of Contents

  1. Challenge

    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. Change Machine Type to Lightweight 1 vCPU, 3.75 GB.

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

  2. Challenge

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

  3. Challenge

    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.

The Cloud Content team comprises subject matter experts hyper focused on services offered by the leading cloud vendors (AWS, GCP, and Azure), as well as cloud-related technologies such as Linux and DevOps. The team is thrilled to share their knowledge to help you build modern tech solutions from the ground up, secure and optimize your environments, and so much more!

What's a lab?

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.

Provided environment for hands-on practice

We will provide the credentials and environment necessary for you to practice right within your browser.

Guided walkthrough

Follow along with the author’s guided walkthrough and build something new in your provided environment!

Did you know?

On average, you retain 75% more of your learning if you get time for practice.

Start learning by doing today

View Plans