Creating a Google Cloud SQL Instance and Loading Data

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.

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.

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.