Database setup on Google Cloud Platform

This is a short introduction to help you setup and test access to your database using Google Cloud Platform (GCP). For more information on SQL commands, please refer to database textbook(s).
(Please tell me if you find any errors or omissions in the document —Upsorn Praphamontripong, 19-January-2020)

Create a new project

Redeem and use your Google coupon. Do not enter your credit card. (If you have not received an email with the link to redeem a coupon, please notify the course instructor via email.)

  1. Assuming that you already have a Google account. Go to the Google Cloud Console and login to your account
  2. Click the "Select a project" dropdown icon (next to "Google Cloud Platform")

    dropdown to select a project

  3. Select  New Project

    dropdown to select a project

    This will prompt a screen allowing you to create a new project. Enter your  Project Name (e.g., cs4640s20), be sure the  Billing account is associated with the Google coupon you redeemed. Then, click  CREATE.

    image showing how to create a new project


Create SQL instance

  1. Access the project you just created
  2. Click the Navigation Menus (a "hamburger menu" icon, top left of the screen), then select  SQL

    image showing how to create a SQL instance

  3. Click  CREATE INSTANCE  to create a cloud SQL instance

    image showing how to create a SQL instance

  4. Choose  MySQL  database engine

    image showing how to create a SQL instance

  5. Enter the instance info. Choose  us-east4  for a region;  Any for a zone; and  MySQL 5.7  for a database version. Be sure to remember the Root password. You will need this when connecting to the instance.

    image showing how to create a SQL instance

  6. Click the  Create button. It may take awhile for the instance to be created. Once the instance has been created, you should see a screen listing your instance.

    image displaying SQL instances

    indicates that the SQL instance has been started.

    indicates that the SQL instance has not been started.


Start SQL instance

  1. On the screen listing your instance(s), click the instance (for example, db-demo)
  2. On the instance overview screen, click one of the  START  buttons.

    image showing how to start a SQL instance

Note: the SQL instance must be started before databases can be created or SQL commands can be run.

Connect to SQL instance

There are several ways to connect to a MySQL instance. In this quick introduction, we will use GCP cloud shell.

  1. Start the SQL instance
  2. On the instance overview screen, under  Connect to this instance  section, select  Connect using Cloud Shell

    image showing how to start a SQL instance

  3. Once the Cloud Shell has been activated, you should see a screen similar to the following. Press the  enter  key to run the command gcloud sql connect <your-instance-ID> --user=root --quiet

    image showing how to connect to an instance

  4. Enter your root password and then press  enter

    image showing how to connect to an instance

    Once the connection has been established, you should see the  mysql>  prompt.

    image showing how to connect to an instance


Create a database

Let's create a  guestbook  database. To create a database, there are several options.

You may use the  Create database  feature of Google Cloud Console.
  1. Start the SQL instance
  2. On the instance overview screen, select  Databases 
  3. click the  Create database  button

    image to create a database

  4. Enter a database name, select  utf8  for the  Character set,  and then click the  CREATE  button

    image to create a database

    Once a database has been created, you should see a screen listing your database.

    image to create a database

    To delete a database, click a trash icon.

You may run a SQL command to create a database.

  1. Start the SQL instance
  2. Connect to the instance using GCP cloud shell
  3. Run the command CREATE DATABASE guestbook;

    Note: SQL commands are not case sensitive. This example uses uppercase and lowercase simply to make it easy to read.

    image to create a database


Create a table

There are several ways to create a table. In this quick introduction, we will use GCP cloud shell.

  1. Start the SQL instance
  2. Connect to the instance using GCP cloud shell
  3. Let's use a  guestbook  database, create a table named  entries,  which contains 3 columns. Enter the following code
    USE guestbook; 
    CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255), 
        entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));

    image to create a table

    You may run  DESC entries;  to view the table schema (structure).


Insert data

There are several ways to insert data into a table. In this quick introduction, we will use GCP cloud shell.

  1. Start the SQL instance
  2. Connect to the instance using GCP cloud shell
  3. Enter the following code to specify the database to be used
    USE guestbook;

    Note: if you have previously specified the database, no need to include this code.

  4. Enter the following code
    INSERT INTO entries (guestName, content) values ("Humpty", "Humpty's here!");
    INSERT INTO entries (guestName, content) values ("Dumpty", "Dumpty's here too!");

    screen showing the SQL command to insert data into a table


Retrieve data

There are several ways to retrieve data from a table. In this quick introduction, we will use GCP cloud shell.

  1. Start the SQL instance
  2. Connect to the instance using GCP cloud shell
  3. Enter the following code to specify the database to be used
    USE guestbook;

    Note: if you have previously specified the database, no need to include this code.

  4. Enter the following code
    SELECT * FROM entries;

    screen showing the SQL command to retrieve data from a table


Exit Cloud Shell

To disconnect from the  mysql  terminal (monitor), run the  exit  command. To disconnect from the Cloud Shell, run the  exit  command.

image showing how to disconnect from a cloud instance using cloud shell


Import SQL file

(Thanks to Sam Shankman)

  1. Create a blank file named  friendbook.sql.  Paste the following content in the file
    CREATE DATABASE friendbook;
    
    USE friendbook;
    CREATE TABLE friends
       (friendName VARCHAR(255),
        phone VARCHAR(255),
        entryID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(entryID));
    
    INSERT INTO friends (friendName, phone) values ("Humpty", "111-111-1111");
    INSERT INTO friends (friendName, phone) values ("Dumpty", "222-222-2222");  
  2. Assuming that you have already created a storage bucket under your GCP project.

    If you have not created a storage bucket, please refer to Create GCP storage bucket.

    Upload  friendbook.sql  to your GCP bucket

  3. Start the SQL instance
  4. Go to GCP console, on the SQL instance overview screen, click the  IMPORT  option.

    image showing how to import a .sql file to the Cloud SQL instance

  5. Choose the file to import data from

    image showing how to import a .sql file to the Cloud SQL instance

    You may need to click  ">"  to navigate to the desired file.

    Assuming that your  friendbook.sql  file is in a bucket named  webpl-demo.

    image showing how to import a .sql file to the Cloud SQL instance

    image showing how to import a .sql file to the Cloud SQL instance

    Then click the  Select  button.

    image showing how to import a .sql file to the Cloud SQL instance

    If you wish to execute a  .sql  on an existing database, select a destination database. Then, click  Import.

    To verify that your SQL file has been run successfully, connect to the instance using the Cloud Shell. Run the following code

    USE friendbook;
    SELECT * from friends; 
    You should see two records of data entered into your newly created  friends  table.

    image showing how to import a .sql file to the Cloud SQL instance


Export SQL file

(Thanks to Sam Shankman)

  1. Assuming that you have already created a storage bucket under your GCP project.

    If you have not created a storage bucket, please refer to Create GCP storage bucket.

  2. Start the SQL instance
  3. Go to GCP console, on the SQL instance overview screen, click the  EXPORT  option.

    image showing how to export a .sql file from the Cloud SQL instance

  4. Specify the destination where the  .sql  file will be exported to.

    image showing how to export a .sql file from the Cloud SQL instance

    Assume the exported file will be called  export-friendbook.sql  and stored in  webpl-demo  bucket.

    image showing how to export a .sql file from the Cloud SQL instance

    image showing how to export a .sql file from the Cloud SQL instance

    Click the  Select  button, and then  Export.

    image showing how to export a .sql file from the Cloud SQL instance

    To retrieve the exported file, go to your GCP console — Storage's Browser  screen.

    image showing how to export a .sql file from the Cloud SQL instance


Stop SQL instance

Go to GCP console, on the instance overview screen, click the  STOP  option.

image showing how to stop a MySQL instance

Note: the SQL instance must be stopped to avoid incurring charges.