How to Use Cloud SQL to Create and Manage Relational Databases
Last Updated :
19 Jan, 2024
Data is everywhere, and managing data is a challenge. Most web websites and applications, businesses, and organizations use databases to store their data. while everything is moving online in a rapid manner managing databases becomes a challenge for organizations. Google Cloud Platform solves all major Relational database problems by introducing their Cloud SQL. Cloud SQL supports all major RDBMS and provides migration from offline databases. In this article, we will learn how businesses can use Cloud SQL to create databases and manage Relational Databases and use their most of time on business logic rather than investing in managing Databases.
What is SQL Database?
Relational databases are referred to as SQL databases. SQL is a structured query language used for programming and managing Relational Databases. A relational database is a database where data are stored in the form of tables and tables share a relationship among themselves. In these types of Databases, each row reflects a data entity, and every column defines a specific information field. SQL provides all major operations like create, read, update, and delete for a Relational Database or SQL Database.
What is Cloud SQL?
Cloud SQL is a fully-managed relational database service provided by Google Cloud Platform. Cloud SQL manages your databases so you don't have to, so your business can run without disruption. From automatic backups to high availability, this service of database management, allowing organizations to focus on business logic while leaving the headache of database administration to Cloud SQL. Cloud SQL supports a variety of popular relational database management systems (RDBMS) including: MySQL, PostgreSQL, SQL Server. Learn more on Cloud SQL by visiting google-cloud-sql.
Benefits of Cloud SQL
- Multiple Database Support: Cloud SQL supports multiple database engines, which including MySQL, PostgreSQL, and Microsoft SQL Server. This helps users to choose the best suitable database for them and migrate from the existing database.
- Low maintenance cost: Cloud SQL provides all essential features of Relational Database Management System and tools for monitoring and managing Databases with minimal cost. We get the feature of pay per use with Cloud SQL.
- Automated Tasks: Cloud SQL supports automatic task management like storage management, backup or redundancy management, undergoing updates, capacity management, or providing data access to ensure businesses can focus on scaling their business.
- Easy Integration Cloud SQL provides easy access to instances and easy integration with Compute Engine, Kubernetes Engine, and App Engine. Cloud SQL supports data analytic features and supports SQL queries to the databases through Big-Query.
- High availability: Cloud SQL provides a highly available service, through multiple zones and using multiple cloud storages which helps reducing down-time and Cloud Databases always active for interactions.
Setting Up a SQL Database- Process
Step 1: Create Instance
Visit console.cloud.google.com and Log in with your valid credentials. Then on the left sidebar select SQL.
On the next page click on create instance.
Step 2: Choose Database Engine
When creating a Cloud SQL instance on Google Cloud Platform (GCP), you have the flexibility to choose from several database engines based on your specific requirements. Select the database engine that suites your requirement (e.g., MySQL, PostgreSQL, SQL Server).
Step 3: Configure Database Instance
An instance refers to a single, isolated database running on a cloud server. It includes the database engine, storage, and configuration settings.
Give your instance a name and set a secure password to prevent un-authorized access. Select Database version as MySQL 8.0.
Set Availability Zone as your nearest and explore other configurations, you can change accordingly or left unchanged. For further more customization scroll down to customize your instance and explore other customization options like Machine configuration, Storage, etc.
Once done, click on create.
Step 4: Check Database Instance
Wait for sometime, until the instance is created. You will notice a tick mark before your instance name, once it is created.
Now you have successfully created a Relational Database using Cloud SQL on GCP.
Enabling SQL Admin API
SQL Admin API in Google Cloud Platform (GCP) allows you to programmatically manage your Cloud SQL databases using API requests. Cloud SQL provides a REST API for administering your instances programmatically. The SQL Admin API provides a set of methods for tasks such as creating and managing database instances, configuring user accounts, and handling backups.
Search for "APIs & Services" on the search-bar available in the Google Cloud console or on the left sidebar look for APIs and Services and then click on "Enable APIs & Services".
This will move you to GCP API Library, search for SQL Admin API there and click on it and then click on Enable API.
Creating Database
Once our SQL instance is ready, let's create a Database. Navigate to all instances and then select the database instance you have created and scroll down to find Open Cloud Shell.
Wait for few seconds until the terminal opens up. You will find a pre-written command is there for you to connect your database to the Cloud shell. Press ENTER and give your instance's password.
After sometime MySQL prompt will open and you can execute MySQL quires.
To learn about MySQL Quires check this article on mysql-common-mysql-queries.
To create a new Database in MySQL execute the following query
CREATE DATABASE guestbook;
This will create a new database named "guestbook" in cloud sql.
Now using the same database create a new table and insert two rows of data. Let's name our table as "entries" and columns as "guestName", "content", and "entryID". We will make "entryID" as the primary key for the table.
/* Using the guestbook database*/
USE guestbook;
/*Create table */
CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),
entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));
/*Insert Data*/
INSERT INTO entries (guestName, content) values ("first guest", "I got here!");
INSERT INTO entries (guestName, content) values ("second guest", "Me too!");
Let's check the table we have just created. Execute the following query to see the table.
SELECT * FROM entries;
As you can see 2 rows are there in the table with the data we have inserted.
Managing Relational Database using Cloud SQL
Google Cloud platform provides plenty of features to manage relational databases. You can use charts to observe your instance behavior. Create your databases using GUI or using cloud shell. Cloud SQL provides option for replication and high availability. You can always opt for better performance and storage by upgrading your plans and services so that you don't feel problems operating your database services running on Cloud SQL. If you are migrating from an existing database, use tools like mysqldump or pg_dump to import data into your Cloud SQL instance.
Access the sidebar on left side to view all services provided for efficient management of your Relational Database.
On Configuration section you can see all the configurations made to your RDBMS. This include Database edition, Database version, Backup Settings, etc. You can see that there is an option given for upgrade so you can opt for update anytime.
On Database section you will be able to see all your existing databases and create an delete databases. More more efficient management of Relational Database, you can use cloud shell and execute your queries. Check the article on Cloud SQL to learn how queries are executed.
To check about resource usage visit System insights and you will be able to see all usages.
Conclusion
Cloud SQL provides user the priviledge to create and manage Relational Databases effortlessly. Automatic frequent tasks and backup processes Cloud SQL helps reducing down-time and Integration with google services is one of the major feature of Cloud SQL. Through this article we have discussed creation and management of Google Cloud's Cloud SQL. You can explore more on Google Cloud domain by visiting the article on
Similar Reads
How To Use Azure Cosmos DB To Create And Manage NoSQL Databases
Azure Cosmos DB is a multi-model, globally distributed database service provided by Microsoft Azure. It comes as a fully managed database service with turnkey global distribution and transparent multi-master replication. You can run globally distributed, low-latency operational and analytics words a
4 min read
How to Create a MySQL Database with Cloud SQL on Google Cloud Platform?
The collection of all the data used for the proper functioning of our product is called a need database (DB) and the system that manages the DB is called a need Database Management System(DBMS).Why Data Management Is Essential?We know that in any startup or business or product based companies like F
8 min read
How to Create Database and Collection in MongoDB
MongoDB is a widely used NoSQL database renowned for its flexibility, scalability, and performance in managing large volumes of unstructured data. Whether you’re building a small application or handling big data, MongoDB offers an easy-to-use structure for managing your data. In this article, we wil
6 min read
Non-Relational Databases and Their Types
In the area of database management, the data is arranged in two ways which are Relational Databases (SQL) and Non-Relational Databases (NoSQL). While relational databases organize data into structured tables, non-relational databases use various flexible data models like key-value pairs, documents,
7 min read
How to Use Cloud Datastore For NoSQL Database On GCP?
Developers can store and retrieve data using Cloud Datastore, a powerful NoSQL document database offered by Google Cloud Platform (GCP). This detailed article will examine the major elements of using Cloud Datastore as a NoSQL database on GCP, covering everything from setup to advanced querying and
6 min read
Cloud Spanner vs. Traditional Databases
Cloud Spanner and traditional databases represent two distinct paradigms in the world of data management. While traditional databases adhere to well-established relational models and are typically deployed on single nodes, Cloud Spanner offers a distributed, horizontally scalable approach to databas
7 min read
How to Design a Database for Content Management System (CMS)
A content management system is a computer application that allows publishing, editing, and modifying content, organizing, deleting as well as maintenance from a central interface. An RDBMS is reliable in storing and managing the content of a CMS to a large extent due to its regional database. In thi
6 min read
Minimal Permission to Create SQL Database
In SQL Server, the ability to create a database is governed by specific permissions and roles that can be granted to users. Understanding these permissions is essential for ensuring security and maintaining the principle of least privilege in our database environment.In this article, we will learn a
4 min read
Creating APIs for Relational Database
In the era of software development, relational databases stand as pillars of structured data storage and organization. These databases, with their tables, rows, and columns, provide a reliable framework for managing information. But what makes them truly powerful is their collaboration with APIs, th
8 min read
How to Use Cloud Storage to Store Your Data?
In today's fast-evolving world where data is the new currency, it is important to manage and store data appropriately, whether you are a business professional, a student, or simply someone who values your data and privacy. Cloud storage is one solution to it and is more effective than the traditiona
10 min read