Google Cloud SQL

Pre-requisite: Google Cloud Platform and GCP Database

Google Cloud SQL is a completely managed relational database service. It provides high obtainability and automatic failover, which confirms that our database never fails and is available for application. If a server administrator is not available then with the help of the Cloud SQL users can easily deploy, maintain, and manage databases. When Google Cloud SQL integrates with Google cloud platform services like GCE(Google Compute Engine), App Engine, and Kubernetes Engine, it is easier to create and manage applications requiring databases.

• Instance: In order to run the database, we need to use a virtual machine called instance created in the Google Cloud Platform. We can create multiple database instances.
• Database: A database is a collection of data that is organized in a structured way. A database is in the form of a table which consists of more than one row and columns.
• Table: A table is an arrangement of information or data, usually in rows and columns or in a more complex structure. Tables are commonly used in reporting, research, and data analysis.
• Field: A field is a single piece of data that is stored in a record in a table.
• Primary Key: It’s a unique identifier like a driver’s license number, area code, or vehicle identification number. A relational database should have only one primary key. Each row of data must have a primary key value and none of the rows can be NULL.
• Replication: Replication is the ability to create a copy of a Cloud SQL instance or a local database and transfer your work to the copies.
• Backups: Backups restore lost data to Cloud SQL instances. If something went wrong, we can also restore it to the previous state by overwriting it with a backup. Enable automatic backup for each instance that contains needful data. Backups protect data from loss.

Steps to Create a Cloud SQL instance on GCP

Step 1: First, go to the navigation menu and click SQL.

Step 2: Now, Click on Create Instance.

Step 3: Create your instance with the following settings:

• Click choose MySQL

• To create a MySQL instance, we must put in an Instance ID and Password. Type “myinstance” for the Instance ID. In the password field click on the Generate link and the eye icon to see the password. Save this password which can be used in the next section. Leave all other fields at the default values.

Step 4: Click Create Instance.

Step 5: Finally myinstance is created.

SQL Queries to create a database and retrieve it in GCP

Step 1: Connect your Cloud SQL instance

gcloud sql connect myinstance --user=root

Step 2: In order to create a database called guestbook on your Cloud SQL instance, you have to run the below SQL query

CREATE DATABASE guestbook;

Step 3: Insert the following sample data into the guestbook database by using SQL queries like use, create, insert, etc…

USE guestbook;

CREATE TABLE entries ( guestName 
VARCHAR(255), content VARCHAR(255),
entryID INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(entryID));

INSERT INTO entries (guestName, content) 
values ("first guest", "I got here!");
INSERT INTO entries (guestName, content)
values ("second guest", "Me too!");

Step 3: To retrieve data we have to use this query

SELECT * FROM entries;