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:
- Set the Instance ID to
forumndb
. - Create a password for
root
and make a note of it for a later objective. - 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.
- Set the Instance ID to
- Create a VM to Run a Secure MySQL Client
There are three tasks in this objective:
- First, we’ll create the VM with the following:
- Give the instance the name
mysql-client
. - Change Machine Type to
e2-small
. - Make sure the Boot Disk is set to
Debian GNU/Linux 10 (buster)
and not a different image.
- Give the instance the name
- Create the service account used to connect with Cloud SQL securely:
- Set the Service Account Name to
forumdb-access
. - Under Grant this service account access to project set it to
Cloud SQL Client
. - 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.
- Set the Service Account Name to
- Upload the key to our VM and configure the MySQL Client and Cloud SQL Proxy:
- Go back to our
mysql-client
vm and click the SSH button. - Using the menu in the top-right of the terminal window (shaped like a cog), upload the JSON key file that you just downloaded.
- Upload the
forumdb.sql
file for this lab (alternatively, clone the git repo into your VM). - Update packages on the VM.
- Install the MySQL client.
- Download the Cloud SQL proxy from Google.
- Make the file executable.
- Go back to our
Your VM is now ready for the next objective.
- First, we’ll create the VM with the following:
- Load and Query the SQL Data
For this objective, we must complete the following:
- Enable an API for the Cloud SQL Proxy and grab the connection name for our DB:
- In your GCP console, from the Dashboard, enable the Cloud SQL Admin API.
- From the GCP console menu, go to the SQL page and select the
forumdb
instance. - Under Connect to this instance, locate the Connection name and click the clipboard icon to copy it.
- Run the Cloud SQL proxy to create a secure connection to the database using the service account we created:
- 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".
- Your
- Use the
mysql
client to connect to your database asroot
, connecting to the localhost to use the Cloud SQL proxy. You will need the root password we created in the first objective. - Create a database called
forum
from within themysql
client. - 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 themysql
command. Make sure theforumdb.sql
file is in your working directory when you run this command. - Connect to the database again.
- Run some simple SQL commands to query the new database.
- In your SSH terminal, run the
- Enable an API for the Cloud SQL Proxy and grab the connection name for our DB: