Cloud SQL
Start course
1h 48m

There are a lot of different options, across a variety of cloud platforms that are well suited for running specific workloads, such as web applications. Things such as Google App Engine, AWS Elastic Beanstalk, Azure App Services: Web Apps, among others.

However, there are still plenty of times where we need to set up our own infrastructure. And so cloud vendors offer IaaS (infrastructure as a service) options. Google provides us with Compute Engine which allows us to create virtual machines, custom images, snapshots, networks, auto-scalers and load balancers.

If we're going to create and implement an application on the Google Cloud Platform system operations, then understanding these services are going to help us to create highly available, highly scalable applications.

All the major cloud providers offer the ability to set up virtual machines, networks, auto-scalers, and load balancers. Where the Google Cloud is different is in the speed of creating and starting up virtual machine instances. As well as the massively scalable software-based, global load balancer; which doesn't require pre-warming. Google also offers per-minute billing for VM instances, after the first 10 minutes.

So Google has a lot to offer. And if you're looking to learn more about the Google Cloud systems operations, then this may be the course for you.

What exactly will we cover in this course?

Course Objectives: Google Cloud Platform system operations 

By the end of this course, you'll know:

How to use Compute Engine to create virtual machines
How to create disk snapshots
How to create images
How to create instance templates and groups
How to create networks
How to use the auto-scaler and load balancer
Intended Audience

This is an intermediate level course because it assumes:

You have at least a basic understanding of the cloud
You’re at least familiar with general IT concepts

What You'll Learn

Summary A review of the course

Lecture What you'll learn
Intro What will be covered in this course
Getting Started An introduction to the Google Cloud Platform
Networking How to create and secure Cloud Networks
Disks and Images An overview of disk types and images
Authorization and IAM How to authenticate and authorise users
Disk Snapshots How to use snapshots for point-in-time backups
Cloud Storage Overview A refresher on Cloud Storage
Instance Groups How to manage instances with managed and unmanaged groups
Cloud SQL Overview A quick primer on how to use Cloud SQL
Startup and Shutdown Scripts Using startup scripts to provision machines at boot time
Autoscaling How to automatically add and remove instances
Load Balancing How to balance traffic across instances
Putting It All Together A demo of how to use some of the services we've learned about



Welcome back. In this lesson we'll talk about Cloud SQL. We'll start with an overview, and we'll follow it up with administration. And then we'll compare the difference between Cloud SQL and MySQL. And we'll talk about how to connect to SQL instances from Compute Engine.

Cloud SQL is a Google-managed version of MySQL. It allows us all the benefits of using MySQL databases without having to deal with the time-consuming management tasks or security patches. Like most systems in the cloud, Cloud SQL needs to be scalable. And so it offers vertical scaling for reads and writes, and horizontal scaling for reads.

Cloud SQL has evolved since its first release, and so now we have two versions. We have the first and second generations. The first generation had some limits on the size, and that didn't work for a lot of applications, so the second generation changed that. The second generation offers more storage, higher performance, and lower cost in most cases. The rough numbers suggest that the second generation has something like seven times the throughput and 20 times the storage capacity.

I mentioned a few times that Cloud SQL is a Google-managed version of SQL, but what does Google actually manage? That would be stuff such as patches and updates to MySQL, daily backups, cross-zone replication, allowing for higher durability, that means that should the primary zone become unavailable, the secondary zone will automatically take over and start handling requests. And Google will also handle instance pausing. This is a feature of the first generation instances, and one that you can optionally disable. It allows you to have the instance stopped if there are no requests for a period of time. This can make running the databases less expensive, though the second generation instances offer a sustained-use discount which should compensate.

So, Google will manage a lot of the time-consuming stuff for us, however there are still things that we'll need to manage. That's things such as user management, which covers creating users, assigning permissions, and setting passwords; SSL configuration; data import and export; configuring external read replicas, which will increase our read performance. And then the typical SQL management tasks, such as schema migrations and schema management.

The first generation version of Cloud SQL has a limit of 500 gigabytes. The second generation also has limits, however those are related to the machine type that you use. Though even the smallest instance supports over 3,000 gigabytes of storage. Cloud SQL is basically just a managed version of MySQL. However, it doesn't allow the use of any statement or function that allows for file access. So there are some differences there.

Let's jump into the console and create a SQL instance, and then show how to connect to it. We'll start on the Cloud SQL page. We don't have any existing databases, so we start with the option to create an instance. And then it wants to know whether we'll be using the first or second generation. We'll use the second generation. It's the better option in most all cases.

And now we're presented with a form. We need to fill out the details here. We'll need a name. And we're going to leave it as MySQL version 5.7. I'm going to set the region, this is just the one that's close to where I live. I'll use the eastern region. And we also need to change the machine type. Since this is just a demo, I'll use the small instance. We'll leave the drive as an SSD for the higher IOPS. And we have some options for high availability. You can see that we can easily add a failover replica, which will add a read-only copy of our database that will be promoted to the primary database in case the current primary fails. By default the backup and logging are enabled. This is an automatic backup that's kicked off for us. And the binary logging is used for replicating data to read-only copies. We'll leave the rest as the defaults, and let's just click on the Create button. Okay, it's going to take a minute, so we're going to fast-forward to when it's complete.

And if we click into the instance we have a dashboard for viewing and editing the instance. On the Overview tab there's some general info. And if we click the Access Control we can add networks that'll be able to access the instance. For the sake of an easy demo, I'm just going to open this up to the world. And we can use the Backups tab to create manual backups, or change our backup window. And then we have a Replicas tab, and this is where we'd create read-only or failover replicas.

Okay, and back on the Overview page we have some quick links that show you how to connect to SQL from different places. All right, let's actually try and connect. We'll start with opening up Cloud Shell. And it's just going to take a moment. Okay, there we are. We can now connect with any MySQL client. And this will work because we've opened it up to the world. So we're going to paste in the password here. Great, and we're connected. And now if we run Show Databases we can list off the databases that exist. And we just get the default databases back.

Now for connecting we can also use the gcloud command-line tool to connect. And it will add the instance to the SQL instances network whitelist. And that will allow us to connect without needing to first open up the ports. Once we've connected in with the gcloud command-line tool, it's just a standard MySQL client. There's really nothing else after that. Now after you have an instance up and running, it's pretty much the same as any other MySQL instance. So there's really not much else to say about it. So, that's going to wrap up our lesson here.

In our next lesson, we're going to cover metadata. So if you're ready to keep going then let's get started with the next lesson.

About the Author
Learning Paths

Ben Lambert is a software engineer and was previously the lead author for DevOps and Microsoft Azure training content at Cloud Academy. His courses and learning paths covered Cloud Ecosystem technologies such as DC/OS, configuration management tools, and containers. As a software engineer, Ben’s experience includes building highly available web and mobile apps. When he’s not building software, he’s hiking, camping, or creating video games.