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
rootand make a note of it for a later objective. - 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.
- 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 the 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-clientvm 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.sqlfile 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
forumdbinstance. - 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_proxycommand with:- Your
instancesset to the Connection name. =tcp:3306added to the end of the Connection name.- Your
credential_fileset 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
mysqlclient 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
forumfrom within themysqlclient. - Type
exitto 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 themysqlcommand. Make sure theforumdb.sqlfile 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:
