Create a MySQL DB With Cloud SQL

Pre-requisite: Google Cloud DB

In this article, we will go through the process of creating a MySQL database using Cloud SQL, which is a fully-managed database service provided by Google Cloud Platform (GCP). Cloud SQL makes it easy to set up, maintain, manage, and administer a MySQL database in the cloud. This tutorial will cover all the steps involved in creating a MySQL database using Cloud SQL, including setting up the Cloud SQL instance, connecting to the instance, and creating a database and tables.

Key Terminologies

• Cloud SQL: Cloud SQL is a fully-managed database service provided by Google Cloud Platform (GCP). It allows you to create and manage databases in the cloud, without the need to set up and maintain your own hardware and infrastructure.
• MySQL: MySQL is a popular open-source relational database management system (RDBMS) that is used for storing and managing data in a structured format. It is widely used for web applications and online databases.
• Instance: An instance in Cloud SQL refers to a single MySQL database server. Each instance has a unique name and can be accessed using a unique connection string.
• Database: A database is a collection of tables that stores data in a structured format. In MySQL, a database is created using the CREATE DATABASE statement.
• Table: A table is a collection of related data stored in a structured format within a database. In MySQL, tables are created using the CREATE TABLE statement.

Now that we have defined the key terminologies, let's go through the steps involved in creating a MySQL database using Cloud SQL

Steps to Create a MySQL DB with Cloud SQL

Step 1: Sign up for a Google Cloud Platform (GCP) account if you don't already have one. You can sign up for a free trial that includes $300 in credit to use on GCP services.

Step 2: Go to the Cloud SQL page in the GCP Console from the Navigation Menu.

Step 3: Click on "Create instance" and select MySQL as your database engine. 

Step 4: On the "Create a Cloud SQL instance" page, give your instance a name and enter a password or generate the password by clicking the "Generate" button and note the password. If we select No Password, this instance will allow anyone to connect with full administrative privilege.

Step 5: Keep other fields as defaults, or you can select the desired version of MySQL and the region where you want to create your instance. You can also specify the instance's machine type and storage capacity in this step.

Step 6: Click the "Create" button to create the Cloud SQL instance. It may take a few minutes for the instance to be created.

Step 7: Once the instance has been created, you will see this type of window where all the details related to the instance will be present.

Step 8: In the Cloud Console, click the Cloud Shell icon in the upper right corner and click Continue

Step 9: Now add the following query at the Cloud Shell prompt to connect to your Cloud SQL instance:

gcloud sql connect myinstance --user=root

This query will connect your instance using the MySQL client in Cloud Shell.

Step 10: Click on Authorize

Step 11: When prompted, enter the password that was set when the MySQL instance was created. 

The Output Will Look like this:

Step 12: Now Create a database using the following query

CREATE DATABASE [Database-Name];

Step 13: Now, use the following query to insert some data into the database: 

USE sampletable;
CREATE TABLE entries (Name VARCHAR(255), content VARCHAR(255),
    entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
    INSERT INTO entries (guestName, content) values ("Firstname", "Hii I am first");
INSERT INTO entries (Name, content) values ("secondname", "Hii i am secondname");

Step 14: Let's display the inserted data by retrieving it from the database using the below query.

SELECT * FROM entries;

That's it; you have created a MySQL database using Google Cloud SQL.

Other Ways to Create SQL in GCP

• Creating a Cloud SQL instance using the GCloud Command Line Tool: You can also create a Cloud SQL instance using the GCloud command-line tool, which is a command-line interface for GCP services. This can be useful if you want to automate the creation of Cloud SQL instances or if you prefer working with the command line.
• Creating a Cloud SQL instance using Terraform: Terraform is an infrastructure as code (IaC) tool that allows you to define and manage infrastructure resources in a declarative way. You can use Terraform to create a Cloud SQL instance by writing a Terraform configuration file that specifies the desired configuration for the instance.
• Creating a Cloud SQL instance from a MySQL dump file: If you have an existing MySQL database that you want to migrate to Cloud SQL, you can create a Cloud SQL instance from a MySQL dump file. This can be done by creating a Cloud SQL instance and then restoring the dump file to the instance using the MySQL command-line client or a MySQL client like MySQL Workbench.

Conclusion

In this tutorial, we went through the steps involved in creating a MySQL database using the Cloud SQL console. We covered how to create a Cloud SQL instance, connect to the instance, create a database and tables and retrieve the data from the table. By following these steps, you can easily set up and manage a MySQL database in the cloud using Cloud SQL.