Google Associate Cloud Engineer – Exploring Database in Google Cloud Platform Part 1

1. Step 01 – Getting started with Cloud SQL

Welcome back. Now that we completely looked at the different categories of databases, let’s now start looking at each of the individual database options in depth. Let’s start with Cloud SQL. Cloud SQL is a fully managed relational database service configure your needs and do not worry about managing the database. If you have a database in your data center, you need to worry about the availability of it. You need to make sure that you have standby databases. You need to make sure that you need to take regular backups. You need to make sure that you have archives around it. How it? If you are creating a database using Cloud SQL, you don’t really need to worry about it at all. All those features are provided by Cloud SQL. It’s a fully managed relational database service. Cloud, SQL Supports MySQL PostgreSQL and SQL Server. It is a regional service. It’s very, very important.

It’s a regional service, it’s not a global service. And it provides high availability, relatively high availability of up to 99. 95%. With Cloud SQL, you can use either SSDs or HDDs, so you can use a solid state drive or a hard disk drive to store your data. However, if you’d want the best possible performance, it is recommended to use SSDs solid state drives. You can use up to 416gb of Ram and up to 30 terabytes of data. Cloud SQL is typically recommended for simple relational use cases. If you want to migrate your local MySQL, postwar SQL, and SQL Server databases to the cloud, that’s a good option. If you want to reduce your maintenance cost for a simple relational database, Cloud SQL is a good option. However, using cloud spanner instead of cloud SQL is recommended. If any of these scenarios is true, one thing to always remember is cloud spanner is very, very expensive compared to Cloud SQL.

Cloud spanner is very, very expensive. So only if you have these kind of requirements you need to go for Cloud spanner. You have huge volumes of relational data running into hundreds of terabytes, for example, or you need infinite scaling for a growing application, or you need a global database which is distributed across multiple regions, or you need higher availability. You want 99. 9% availability. In these kind of scenarios, you can go for cloud spanner. However, if you would want to create a local MySQL PostgreSQL or a sequel server database, or if you have thousands of transactions per second and you’d want a simple relational database to reduce your maintenance in those kind of situations, you can go for Cloud SQL. In this step, we got started with Cloud SQL. I’ll see you in the next step.

2. Commands executed in this section

Come back in this step. Let’s get started with Cloud SQL. If you type in Cloud SQL in here, you’ll be able to easily get to SQL. And that’s where we are right now. Cloud SQL instances are fully managed, relational, MySQL postsql, and SQL Server databases. The biggest advantage of going for Cloud SQL is you don’t need to worry about replication or package patches or database management. All of them are handled by Google for you. Let’s go ahead and create an instance. Over here. You need to choose which database you’d want to create. MySQL or PostgreSQL or SQL Server. I’ll go with MySQL So let’s choose MySQL, and over here I would need to provide an instance ID and a password. So I’ll call this My first MySQL Cloud SQL instance and you can also populate a password.

I’ll choose the default version of MySQL which is suggested, and I’ll choose the default zone which is suggested over here as well. You can create a Cloud SQL database in a single zone or inside a specific region. You cannot create a global Cloud SQL database. I’ll choose single zone for now, and I’m going to go ahead and say Create instance. This would create our MySQL Cloud SQL instance. Let’s wait for the instance to be completely created and started up. It took about five minutes to create the instance and at the end of it I see this green mark and that means this instance is now ready. Oh, here. What we want to do is to create a database first. So we have created an instance and inside the instance let’s try and create a database. You can see that there are a few databases which are already present.

Let’s create our own database as well. I want to create a database in here and let’s call this to Dos and say Create. So this would create a database in here and we can create a number of tables inside that specific database. Let’s try and connect to this instance first. What I do is I’ll go to Overview. I would scroll down a little and you can see options to connect to this instance. What I would want to do is to connect using Cloud Shell. So let’s go ahead and connect using Cloud Shell. Once we say Connect using Cloud Shell, there would be a command which should be populated. In here. It says Gcloud, SQL, Connect. My first Cloud SQL instance. That’s the name of the instance and it uses the root user. Let’s press Enter and see if we are able to use that and connect.

So here I just pressed Enter and it’s asking me for authorization. Authorized cloud shell. I would say authorized. Let’s see if it would work. As you can see in here, it is not working. It says Permission denied. Cloud SQL Admin API has not been used in this project before or is it disabled? So the first thing that we need to do is to enable the cloud SQL admin API so you can click this link which is present in here. So we are now enabling the admin API for cloud SQL. Let’s go ahead and enable that. Let’s wait for it to be enabled. It should be a quick thing. The admin API is now enabled so I can close this window and go back and go back to our window where we executed the command earlier. And let’s try connecting to the database again. Fingers crossed. The first thing it does is allow connections from our IP address.

So our IP address will be whitelisted for five minutes so that we can connect to the cloud SQL database from cloud shell. Cool. Now it’s asking for password. So it’s in connecting to database with SQL users. So and so enter password. You need to enter the password that you have entered on the MySQL creation screen earlier. So I’m entering that password in here and I pressed enter and now I’m able to connect to the MySQL database over here. I can start with creating our tables. We earlier created a database. What is the name of the database that we created with? We created the database with the name. To do so I’ll say use to Dos. So we are now playing with the todos database. So I’ll say create table user and let’s say inside the user table I would want to have an ID which is integer.

Let’s say I would want to have a username, and let’s say the user name is PR of 30 and let’s add a parenthesis to close this. So we are creating a simple table. You can see that the table is now created. So if you do a describe user, what would you see? You can see that there is a table existing. You can see the fields, the types. If I want, I can even insert data into user, insert into user values. Let’s say one comma ranga semicolon. Again, that would create a row and I can say select star from user semicolon and this would fetch the data from the thing. So as you can see in here, it’s very, very easy to create a cloud SQL instance and create a database and create a table in it and populate it with data in the step. We got started with cloud SQL. Let’s see a little bit more about it in the next steps.

3. Step 01a – Demo – Playing with Cloud SQL

Let’s explore the cloud SQL database a little bit more. I’ve closed down the G cloud interface and what you would want to do in here is to look at all the things that are present in here. Let’s start with the overview screen. This is where you can see the monitoring information around the MySQL instance. You can see that we have this instance running for almost ten minutes now. And you can see the ship utilization is pretty low. We are not really having a lot of tables in there, so the CPUT utilization remains low. You can also look at other metrics. So storage, how much storage usage is there, how much memory usage is there and all that other stuff. And if you go a little below, this is where we were able to connect that specific instance. And you can also see all the configuration information about the specific database instance.

The database version is MySQL five seven. We have configured auto storage increase. As the amount of data increases, the database storage will also be increased automatically. You can see that by default automated backups are enabled. Point in time recovery is also enabled. We have created a Zonal instance, so it’s not really highly available. Regional instance where we have replication across zones are much more highly available. You can see that there is a service account which is assigned to this specific instance. And you can see all the operations and logs information in here. You can see the default maintenance preferences in here. In connections you can manage how you can connect to this MySQL instance. Do you want to have a public IP or a private IP? By default we are using a public IP and you can also configure the security.

Do you want to use SSL? If you want to use SSL, you can manage the server certificates in here. You can manage your database users. In users you can see that right now we only have the root account. If you want you can add more user accounts. If you go to databases, you can actually see all the databases which are created earlier. We created the Tous database. All the other ones are databases which are automatically created within MySQL. If you go to backups, you can look at all the backups that have been taken until now. You can see that there is a backup which is already created at the time of instance creation. And if you want you can restore from this specific backup. You can also create a backup as it is right now. So you can go in here and say create backup. You can also edit the backup settings.

You can go to Replicas and you can look at all the Replicas that I created until now. You can create read Replicas for your primary instance. So you can go ahead and say create read Replica and you’d be able to create a read Replica for the SQL instance and three replicas can be created in the same region or in a different region. I don’t really want to create a read replica, so I’ll say a cancel in here and let’s click in and go to the Cloud SQL instance and go over to operations. This is where you can actually look at all the log of operations that are being performed on the database instance. Now we looked at most of the default settings. Let’s see how to edit these details. If I go back to the overview and I click Edit, I’ll be able to see more details about the database. So if I go to the machine type, you’ll be able to see what is the machine type that we are making use of.

So this is the VM on which the database is being installed. If you want, you can actually vertically scale the database by increasing the amount of CPU that is being assigned to it. You can also look at the storage information. You can see that right now we are making use of an SSD, which is the recommended option. You can also configure the storage capacity and if you want, you can also enable automatic storage. Increases in the connectivity is where you can configure whether you want a public IP or a private IP. If you want your database to be restricted to a private network, you can have a private IP assigned. And this is where you can actually configure the backups. You can configure if you’d want to automate the backups. You can configure which window you’d want to take the backup in, and you can also configure if the backups have to be multiregional or regional.

Do you want to store your backups in multiple regions? That’s the question there. And how many automated backups to store. What we are seeing in here is seven backups. The other thing you can configure in here is the point in time recovery. Do you want to be able to recover to a specific point in time in the past? And you can also configure how many days in the past do you want to go back up to? How many days of logs do you want to retain? The next settings are related to maintenance. This is where you can configure if you have any preferred window. Typically you want to do your maintenance during a time period when there are very less users on your application. So you can choose a window like that and you can also set flags and labels. As you can see in here, cloud SQL really makes it very, very easy to manage your database.

You just need to configure a few settings and Google Cloud platform would be responsible for ensuring that your database is highly available and the data in it is highly durable. All that you need to worry about is creating your databases, creating your tables, and creating your applications and using the tables from there. Now, before we go into another database. Let’s go ahead and delete this. So I’ll go back to all instances, select this and go ahead and say delete. So go ahead in here and say delete. So my first Cloud SQL instance, I would want to go ahead and delete that. So let’s do that. My first Cloud SQL instance, delete this out. Cool. Our cloud SQL database is deleted. As we saw in this step, cloud SQL makes it very, very easy to manage relational databases in the cloud. If you don’t want a simple regional database, then Cloud SQL is the preferred option. I’ll see you in the.

4. Step 01b – Demo – Playing with Cloud SQL – 2

Come back. In this step, let’s look at some of the important features of Cloud SQL. Automatic Encryption so all your tables and backups of the tables. So different snapshots you take, all of them are automatically encrypted. You can also configure automated maintenance and updates. You can say this is the time in which the maintenance and the update should be done. Cloud SQL would ensure that the maintenance and the updates are done as per your schedule. Cloud SQL provides high availability and also failover. You can actually create a standby with automatic failover. So in addition to the master Cloud SQL database, you can create a standby database. And in case of failure of the master database, you can automatically failover to the standby database. If you want high availability and failover, it is very very important that you enable automated backups and binary logging.

Both of these features are important to ensure that you have high availability and failover. Along with the standby, you can also create read replicas for your read workloads. The different read replica options that are supported are cross zone in a different zone, cross region in a different region, and external. This external can be an on premise database as well. So this external is a non Cloud SQL database. Again, the prerequisites for creating a read replica is that automatic backups and binary logging are enabled. New versions of Cloud SQL also support automatic storage increase without downtime. So as the amount of data which is stored in the database grows, the database can grow with it point in time Recovery you can recover to a specific point in time.

Let’s say I would want to go back to the database as it was yesterday at a specific point in time. You can do that with point in time recovery. A prerequisite for point in time recovery is that binary logging should be enabled. In Cloud SQL, you can also schedule backups. You can take on demand backups and you can also schedule automated backups. Cloud SQL also supports migration from a number of other sources. Typically it is recommended to use the database migration service. If you want to migrate data to Cloud SQL. You can also export data from UI or Gcloud with following formats. The formats which are supported are SQL and CSV. SQL export is recommended if you are importing the data into other relational databases. In this step, we quickly look at some of the important features of Cloud SQL. I’ll see you in the next step.

5. Step 02 – Understanding Cloud SQL Features

Welcome back. In this step, let’s look at high availability features of Cloud SQL. You can set up cloud SQL in a high Availability configuration, which is ha. You can choose primary and secondary zones within a region. So within the same region, you can have a primary instance and a Standby instance. So you’ll have two instances, primary and secondary. Any changes that are done to the primary instance are automatically replicated synchronously to the Standby instance. If there is a Zonal failure, then the database would automatically fail over to the secondary instance. Remember that if the primary zone becomes available, failover does not revert back automatically.

A very, very important thing to remember is the High Availability setup cannot be used as a read Replica. You cannot directly connect to the Standby instance while the primary instance is running. At any point in time, you are either connected to the primary instance or if there is a failover, then you would be connected to the Standby instance. You cannot connect to both the instances at the same time. If there is a need for read Replica, you can go ahead and create a separate read Replica other than the Standby in this case. Next step, we’ll look at the high availability options in Cloud SQL. I’ll see you in the next step.

6. Step 03 – Understanding Cloud SQL High Availability Features

Back in this step. Let’s get started with Cloud Spanner. Cloud Spanner is a fully managed, mission critical, relational, globally distributed database with very high availability. The key things to remember in here are its a relational database so you can design your schema, you can use sequels and it’s a globally distributed database. It is distributed across multiple regions around the world and it provides you with very high availability. 99 point triple 9% cloud SQL provides you 99. 95% 99. 39% is very very high availability compared to Cloud SQL. If you have any mission critical relational workloads that you would want to run in Google Cloud cloud Spanner is the recommended option provides you with strong transactional consistency at global scale.

The amazing thing about Cloud Spanner is it can automatically distribute the data across multiple nodes so it can scale to petabytes of data with automatic sharding. Cloud Spanner scales horizontally for both reads and writes. With Cloud SQL you can create a read Replica if you want to scale your reads. However, with Cloud Spanner you can actually horizontally scale both reads and writes. All that you need to do with Cloud Spanner is configure the number of nodes. As we discussed in comparison, cloud SQL provides read Replicas but you cannot horizontally scale write operations with Cloud SQL.

With Cloud SQL you can either talk to the primary database or the standby database. You cannot talk to both of them at the same time. Cloud Spanner supports regional and multiregional configurations and it is very very expensive. You need to pay for nodes and you need to pay for the storage. You can use cloud console to export data from cloud spanner. Other option is also to use Data Flow to automate export. You can use Cloud Data Flow to automate the export from Cloud Spanner to other databases. Important thing to remember is that there is no G cloud export option. Either you should use Cloud Console or you need to use Cloud Dataflow.

• Category: Uncategorized