What is Google Cloud SQL?
Google Cloud SQL is a relational database service where its main offerings are relational data, transactional data (mainly used in banks). For e.g. Without database transactions your bank would not offer you to transfer money from one account to another, What if the transfer of $100 didn’t result in receiving in destination account, your bank just lost $100. That’s why SQL is required. It has features like commit or rollback.
- A classic relational database has a lot of setup required, management, Configuration, maintenance, administer, to get rid of all these. GCP provides you a platform where one can easily manage or administer your database instances.
- It offers MySQL, PostgreSQL, SQL Server as a fully managed service, it offers a database that is capable of handling terabytes of data (up to 30 TB). You always have an option of running your own DB server in VM machine but then you have management overhead.
- It provides read replica, external replica, failover features, if there is an outage, it will failover to another zone
- Backup option is there, either scheduled or on-demand
- You can scale vertically by changing machine type or horizontally like Read replica
- Customer data is always encrypted where on google internal network or in DB tables, or in the backup.
- Cloud SQL is compatible with other Google services like app engine, compute engine, or external applications also like MySQL workbench.
- It reduces maintenance costs and automates Database provisioning, backups, patches, capacity increases ensuring 99.95% availability.
- It provides you High Availability with automatic failover.
- Data is always encrypted at rest or in transit.
- It helps you focus on your app rather than management.
The primary instance writes logs to the system database every second in terms of the heartbeat signal, in any case, if heartbeats aren’t detected for 60 seconds, a failover process is initiated. This may also occur if the zone containing the primary instance experiences an outage. In case of failover, the standby instance serves as a backup database from the secondary zone.
- This lab walks you through Google Cloud SQL and its features.
- You will be creating a Database Instance initially.
- Secondly, you will create your Database.
- Finally, you will create your tables and insert data into the table
- Starting Cloud Shell.
- Creating a Database instance.
- Creating a MySQL Database.
- Creating a table in your database.
- Inserting data into your table.
Creating a Google Cloud SQL Instance:
- Click on the hamburger icon on the top left corner
- Click on SQL.
3. Click on create instance to create your database instance.
4. Choose your database engine as MySQL.
5. Enter instance id as whizlabs-cloudsql-<yourname>. For e.g. If your name is rajeev then enter instance id as whizlabs-cloudsql-rajeev.
6. Set any password for your root user.
7. Select region as us-central1 and Any zone. Please note, User Interface might differ for you. You should choose an us-central1 region and zone as us-central1-a.
8. Choose database version as MySQL 5.7
9. Click on show configuration options.
10. Make sure the machine type is lightweight and has 1 Core with 3.75 GB of memory. If the machine type is different, Change the machine type by clicking the down arrow on the right side.
11. Click on the dropdown to change the machine type as Lightweight and select 1 vCPU, 3.75 GB.
12. Change the Storage type by clicking the down arrow on the right side.
13. You can choose any storage type. In our case, we are choosing SSD and storage capacity as 10 GB.
14. Click on Create Instance to finally create your DB Instance.
Creating a Google Cloud SQL Database:
- Once you create DB Instance, click on databases
- Click on create database.
3. Enter the database name as whizlabs_mysql_db.
4. Keep the character set as utf8.
5. Keep the collation as default collation.
6. Click on Create.
7. Click on Cloud Shell icon to start Cloud Shell.
8. Enter gcloud sql connect whizlabs-cloudsql-rajeev— user=root to connect your database with your instance.
9. Enter the password which you selected earlier.
10. Enter use whizlabs_mysql_db; to select your database.
11. Enter the below query to create a table with two columns.
CREATE TABLE users (username VARCHAR(255), userid INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(userid));
12. Enter the query to insert the data into the table.
INSERT INTO users (username) values (“itadmin”);
13. Enter the query to insert the data into the table.
INSERT INTO users (username) values (“hrdep”);
14. Enter the query to fetch the data from the table. select * from users;
Completion and Conclusion:
- In this lab, you have created a Database instance.
- You have created a database, a table, and inserted data into the table.