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 let me know if you find any errors or omissions in the document —Upsorn Praphamontripong, 24-September-2023)

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 see CS 4750's Announcement "Database server (and web server) options, and Google Cloud Platform coupon"). If more coupons are needed, please make a private post on Piazza. We will get you more coupons. Do NOT enter your credit card information.

Assuming that you already have a Google account. Go to the Google Cloud Console and login to your account
Click the dropdown icon to select your project or create a new project
Select New Project

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

Note: depending on how your account was set, you may be required to enter Location or Organization. Please select University of Virginia and proceed to create the project.

Create SQL instance

Access the project you just created
Click the Navigation Menus (a "hamburger menu" icon, top left of the screen), then select SQL
Click CREATE INSTANCE to create a cloud SQL instance
Choose MySQL database engine
Click Enable API. This may take a few minutes
Enter the instance info. Be sure to remember the Root password. You will need this when connecting to the instance.

Choose MySQL 8 for a database version.

For CS 4750, to minimize GCP service charge, select Enterprise, Sandbox environment.

Choose us-east4 for a region.

Select Single zone for availability.
Click the Create instance 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.

indicates that the SQL instance has been started.

indicates that the SQL instance has not been started.

Start SQL instance

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

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.

Start the SQL instance
On the instance overview screen, under Connect to this instance section, select Connect using Cloud Shell
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

If this is the first time setting up the instance or using the cloud shell, you may be asked to authorize cloud shell.

If you receive a permission denied error, click the link specified in Cloud Shell to enable Cloud SQL Admin API.

Then, go to Cloud shell and run gcloud sql connect <your-instance-ID> --user=root --quiet to connect to the instance.
Enter your root password and then press enter

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

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.

Start the SQL instance
On the instance overview screen, select Databases
click the Create database button
Enter a database name, select utf8 for the Character set, and then click the CREATE button

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

To delete a database, click the 3-dot and then delete.

You may run a SQL command to create a database.

Start the SQL instance
Connect to the instance using GCP cloud shell
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.

Create a table

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

Start the SQL instance
Connect to the instance using GCP cloud shell
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));
```
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.

Start the SQL instance
Connect to the instance using GCP cloud shell
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.

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.

Start the SQL instance
Connect to the instance using GCP cloud shell
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.
Enter the following code
```
SELECT * FROM entries;
```

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

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");

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
Start the SQL instance
Go to GCP console, on the SQL instance overview screen, click the IMPORT option.
Choose the file to import data from

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

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

Then click the Select button.

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.

Export SQL file

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.
Start the SQL instance
Go to GCP console, on the SQL instance overview screen, click the EXPORT option.
Select the format of the data to be exported and choose whether you will export the entire instance or specific database(s) in the instance.

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

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

Click the Select button, and then Export.

To retrieve the exported file, go to your GCP console — Bucket details screen.

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: Remember to stop the SQL instance to avoid incurring charges.