Google Cloud SQL with hands-on.

There are different services available for various purposes in GCP. In this blog, we will discuss one of the database services given named Google Cloud SQL. Before getting in to this, it is good to know about Google Cloud Storage service. If you are interested, you can read my blog here about cloud storage. Now lets get into Google Cloud SQL service.

From the official site,

Fully managed relational database service for MySQL, PostgreSQL, and SQL Server with rich extension collections, configuration flags, and developer ecosystems.

It is,

• Fully managed service for relational databases.
• All the existing SQL databases in on-premises/local can be easily shifted to Google Cloud SQL.
• We can store up to 64 TB (Google increasing this amount regularly) of data in each instance of the Google Cloud SQL. Only Vertical scaling is available here. This means We can only increase the resources of the particular instance But we can’t attach another Google Cloud SQL instance to the existing one.
• Data is completely encrypted in Google Cloud SQL storage.
• Used primarily for storing transactional databases like Ecommerce, Banking applications backend.

Creating Google Cloud SQL instance

From the left panel select the SQL option, which will take you to the SQL instance page. We can create SQL instances/migrate data from this page. Now click on CREATE INSTANCE button. On the new page, you can see three SQL services from which we can choose the required one. Below GIF image shows the SQL instance page and selects create an instance.

Once we get to the SQL instance creation page, we have many configuration options to select. Let's discuss them one by one in detail.

• We have to give the instance name and admin password to log in once we create the MySQL instance. I selected the default database version given. You can choose from the given options. Next option to specify whether the instance is for either Production or Development. I selected Development in this. We can explore the current configurations set to the instance using the CONFIGURATION DETAILS dropdown.

• Now comes the important part. Choosing Region from Choose region and zonal availability configuration. It is always recommended to select the multiple zones option because of the high availability. When the primary instance is down, traffic will automatically be switched to the secondary instance. We will discuss this Failover concept in the coming topics. we can select our instances location from the dropdown.

• I have a GIF down here for all other configurations. Please go through the GIF to understand the configs. I selected default options for most of the options. Since this is the development practice instance, I chose Storage and Memory options as minimal as possible. Then create an instance with the above configurations given.

Now We can see the newly created instance in the instances table. We can see some basic details like the Public IP Address of the SQL instance which will be useful to get connect with the database.

Connecting to Cloud SQL

• After the successful creation of Cloud SQL instance, we would want to connect with it to do database operations. We have Public IP Address given to connect. But we cant simply use that to do the connection. We have to whitelist our public IP to make sure connection works.
• We will make a connection using GCP Cloud shell for now. How to find public address of Cloud Shell? Use below command in cloud shell and get the cloud shell public ip.

curl 'https://api.ipify.org?format=json'

• Once you got the public IP, We have to whitelist from the console. Go to Connections tab, add the IP to Authorized networks option.Below images shows adding IP address to the whitelist and connecting to MySQL through shell.

• Once you add the public IP of cloud shell to the whitelist, Go to the console, use below command to get connect with your MySQL instance.
  mysql -h <mysql_instance_public_ip> -u root -p
  You have to give the root password for the database what you gave during the instance creation. Now we are ready, we can do all the database operations.

Database Migration to Cloud SQL

It is necessary to migrate all our existing MySQL database to CloudSQL so that we can continue to do all the operations. It is pretty much easy to migrate our on-premise/local databases to CloudSQL.

• For this learning purpose, I have download one sample mysql dump from online. you can create your own dump from your local mysql if you have else download some sample dump from the market. In the databases page, you can see four databases available now. I will add the dump after that we can see new DB.
• To add the SQL dump to MySQL instance, add the dump to any of the bucket in Cloud Storage. From cloud storage we can import the dump to MySQL. once you uploaded the dump to storage, go to SQL page, click on the MySQL instance, Click the IMPORT button, select the required SQL dump file from Cloud Storage buckets, import it.
• Give it for some time. After that go the database section where you can see new database is available which is from your dump. You can Go to the Cloud shell and connect to the MySQL instance using public IP, then use mysql commands to see the new databases and tables.

Cloud SQL Failover

If you remember when we creating MySQL instance, we gave Zone availability as Multiple Zones. It will be useful at the time of Failover. When Primary instance fails, all the traffic will go to secondary zone. This will make sure no fail happens during the failover. You can manually check the failover happens manually by using the option available.

There are many more features and details available to explore in MySQL instance. Connections, Backups and many more. Do the investigation in all of these options to understand them.

For now, I am done with the Cloud MySQL. We will meet up with some other interesting GCP service in coming days. Thanks for your time for reading my blog. Thank You. ta ta.

Gobalakrishnan Viswanathan :)