GCP and Cloud SQL
Cloud SQL is a fully managed database service. Cloud SQL is Google’s relational database. It is a cloud hosted MySQL or PostgreSQL or SQL server database. Similar to a regular MySQL database, Google Cloud SQL lets us create, modify, configure and utilize a relational database. The fully managed database service where tasks such as applying patches and updates, managing backup, configuring replications, database security etc are taken care by the cloud providers. Cloud SQL is similar to Amazon’s Relational Database Service (RDS).
What are the relational databases? These databases store data in a structured format in the form of tables having rows and columns. Tables and schemas are created to store the data. Tables may be related to each other.
In a relational table, the primary keys are the columns whose values uniquely identify each row. When two tables are related to each other then the primary key of one table is stored as the foreign key in the other table. For example, in the employee table we have the dept_id column that stores the dept_num of an employee. The other table dept will be the master or parent table having dept_id as the primary key. If a particular dept number record gets deleted from the dept table, the foreign key relationship ensures that all related employee rows get deleted before the particular dept row can get deleted.
We can have not null constraints as well as the unique constraint defined in a relational table. The not null constraint defined at the column level ensures that the column can’t have any null values. Similarly, the unique constraint at the column level ensures the column always have unique values and never two rows with the same column values exist.
DDL the data definition language that involves in the schema related operations such as creating tables or altering tables or dropping any table.
DML the data manipulation language that involves operations related to data manipulation such as inserts, deletes, updates.
For performing DDL or DML, the Structured Query Language (SQL) is the programming language.
In a relational database operation, a transaction starts with BEGIN and ends with COMMIT. A transaction involves all the operations associated with accessing and modification of data. Transactions in relational database use the ACID property. It stands for Atomicity, Consistency, Isolation and Durability.
Features of Google Cloud SQL
With Google Cloud SQL GUI, very simple for an user to create the My SQL or PostGreSQL database.
Fully managed by Google like patching, replications etc
High availability of data.
Google Cloud SQL is highly compatible with other Google services as well.
Data is always secured and highly available.
Automatic backups and binary logging
The simplest, quickest and most useful backup is the automatic one that occurs daily at the time specified by the user while creating the instance.
Automatic backups backup the data at periodic intervals. Binary logging enables point in time recovery and data replication. Point in time recovery enables restore or recovery of data from a time in the past .
Cloud SQL provides import and export of the data and Cloud Storage will store the backup.
High Availability
Read replicas and fail over replicas ensure a highly available system by removing any single point of failure. Read replicas are the clone of the primary instance. Read replicas are useful when the application is performing more reads then writes. Some of the read related traffic can be routed to these read replicas. The primary job of the failover replica is to be ready as a replacement primary instance. By creating a failover replica in a different zone than the primary database, we can make sure that for whatever reason the database crash in one zone, the database in the other zone can work with little interruption.
In case of crash, the Failover DB becomes the primary database instance.
Scaling
The computing power and the disk performance determine the performance of the Cloud SQL.
Working with Cloud SQL instance
Creating a MySQL Database in Cloud SQL
Click on the SQL:
Next click on Create Instance
Will choose MySQL.
Next will enter the below details and click on Create. This will create the MySQL DB instance named testinstance.
Click on the Show configuration options.
Connectivity: How one would like to connect to the database instance.
With the Machine Type and Storage option, one might want to change the size of the VM instance or increase the size of the associated disk.
The maintenance window enables user to select time when regular maintenance will happen that may need the system to restart hence minimizing any disruption.
The automatic backup happens between 6 PM to 10 PM. Point in time recovery is enabled.
We can create a read replica of an instance as shown below:
Creating Database in the testinstance:
Available databases in testinstance => databases as shown:
Working with Data in Google Cloud SQL
Will create a bucket under console ==> Storage and upload the table creation script as well as the data insert script.
The create table script:
CREATE TABLE fish_data(
id INT ,
Species VARCHAR(50),
Weight INT,
Length1 INT,
Length2 INT,
Length3 INT,
Height INT,
Width INT,
PRIMARY KEY (id)
);The insert data script:
INSERT INTO fish_data values(1,”Bream”,242,23.2,25.4,30,11.52,4.02),
(2,”Roach”,40,12.9,14.1,16.2,4.1472,2.268),
(3,”Whitefish”,270,23.6,26,28.7,8.3804,4.2476);
Will navigate to the instance testinstance and click on the Import. Next we will select the table creation script from the bucket and select the database under which we want to create the table. Similarly we do for the insert script. In the testinstance → operations, we can view the logs for both the activities.
With the instance overview page, we can click on the Connect using the Cloud Shell to open MySQL instance via the shell. We connect to MySQL with the password that we provided while creating the instance. We move to the database where we want to create the table and the data.
gcloud sql connect testinstance — user=root — quiet
mysql> use testdb
Will change the database to custdb where we created the customer table.
From Jupyter notebook we connect to the mysql instance:
connection_string = <the project>:us-central1:<database instance>
The newly created database testdb1 is reflected.
Closing thought
Relational databases are great for storing data that relates to other data using foreign key references for example ecommerce or banking transactions. There are various options like opting for Google Cloud SQL or owning MySQL server on a virtual machine in a cloud service like Compute Engine or Amazon’s EC2 or using Amazon’s Relational Database Service.
