Create a Google Cloud Platform SQL instance

In the next two articles, we'll show you how to build a Google Cloud SQL Instance; this is a fully managed MySQL database service and infrastructure. Here we'll go step by step through the process of exporting an existing SQL database, setup a Google Cloud Project, and create a SQL instance. In the following article we'll import an existing SQL database, and then connect to the MySQL service via the Cloud Shell and troubleshoot some potential errors and problems.

Author
Recent Posts

Jason Coltrin

Jason Coltrin has been working in IT for more than 17 years. He holds an MCSE 2003 Security+ plus various Palo Alto and SonicWall firewall certifications. He also is an avid Linux administrator and currently works in the finance Industry.

Prepare your database for Cloud SQL

Before we get started building our Cloud SQL instance, we'll want to have a SQL database ready for import into the Cloud SQL Instance. For this exercise, you can export your existing MySQL database using phpmyadmin or mysql workbench. For example, with phpmyadmin, you can do this by going to the top level of your database, then clicking on the Export tab, changing the format to SQL and then clicking Go. In my example below, I had already imported a sample database, which we will use later, into my lab's MySQL server.

Exporting database with phpMyAdmin

Ultimately, what we want is a .sql file that contains our database, tables, and content. In the ensuing steps, you can follow along exactly by first downloading the "classicmodels" sample database named mysqlsampledatabase.sql directly from mysqltutorial.org here (53.06kB). You're not alone if you have a difficult time finding a good free sample SQL database. There are few tools out there to generate a free large SQL database, so if you find and/or generate one yourself, let us know.

Create the Cloud SQL Instance

If you don't already have a Google Cloud Platform account, first create a free account and then sign in. Next, click on "My first project" and then name your new project something like 'sqltestinstance'.

Create new project

Name first project

While following along, you may notice a tutorial pane on the right-hand side of your Google Cloud console. We will follow along with the tutorial for the first few steps, as it provides a bit more explanation about each component as we go. Next, we are prompted to configure the storage for our SQL instance. Click on Cloud Storage Quickstart > Select a different project:

Select different project

Select the 'sqltestinstance' project that we named earlier, and then click CONTINUE:

Select project sqltestinstance

In the Create a bucket tutorial dialog, follow the guide and click on the Storage menu in the main console:

Create a bucket

Select the Storage console:

Select Storage

Next, create and name your bucket. We want a bucket in order to upload the database file we will use to populate our Cloud SQL instance. Here you have a multitude of options including storage class and regional settings. We'll stick with the default settings and name our bucket 'sample-db-raw-classicmodels'.

Create and name bucket

Once we're inside our newly created bucket, we'll upload the file mysqlsampledatabase.sql which we downloaded earlier. Do this by clicking on the Upload Files link. Browse to the mysqlsampledatabase.sql file (that you downloaded or exported earlier) and click Open.

Upload files into bucket

When the file completes the upload, we'll see it listed in the bucket and we need to make sure that the file is not shared publicly (i.e., the box is unchecked):

Do not share publicly

We're now ready to spin up our SQL Instance. To do so, go to the top level menu, and then go to the Storage group and click SQL.

Select SQL storage

Next, click Create instance:

Create Cloud SQL instance

Next we're asked to choose the instance type. We'll use the recommended Cloud SQL Second Generation > Choose Second Generation.

Create Second Generation Cloud SQL instance

Now we need to name, or in this case provide, an Instance ID. Name the instance ID something distinct, such as classic-models-sql-instance. For the Database version, it's ok to leave the default as MySQL 5.7. It's also ok to leave the default Location set to us-central1 (Zone: Any).

Set instance ID version and location

Set the Machine type to db-n1-standard-1. Here we'll also find the other available options and choose appropriately if we have higher CPU and memory needs.

Select machine type options

Next we're asked to choose the Storage type. Accept the default SSD (Recommended), and the Storage capacity to the default 10GB, or change according to your workload.

Select instance storage type SSD

Next, enable High availability by placing a checkmark in Create failover replica checkbox. Keep the default ID for the replica and adjust backup schedules as needed.

Enable high availability failover replica

Because the SQL instance is a managed cloud instance, we need to set a Maintenance window for updates. You can keep the default settings, Any window / Production.

Additionally we want to allow certain network IPV4 addresses to access our instance, and we'll do this by supplying our home network with a Network CIDR notation address.

Set maintenance window and add network

When we click Done, our instances will build for a small period of time and then we'll see our newly created instances.

Select newly created SQL instance

Once our instance is up and running, we can view the instance details and properties. Notice that a second replica instance was also automatically created. We now have the ability to set Access control, Backups, Replicas, and Operations. Here we can also Stop and Restart our instance.

SQL instance details

SQL instance properties

Lastly, we can find our instance Properties. Keep in mind that the Instance connection name will be used later in our next article when we connect to the instance with the Cloud Console.

Subscribe to 4sysops newsletter!

Conclusion

As you can see, it's pretty simple to start a SQL instance on the Google Cloud Platform. We recommend you dig into all of the options available to become familiar with how to manage your SQL instance, perform backups and restores, and set up permissions for data security and integrity. In our next article here, we import a SQL database, connect to the instance with the Google Cloud shell, and lastly troubleshoot errors and run some simple MySQL commands.

Read the latest IT news and community updates!

Join our IT community and read articles without ads!

Do you want to write for 4sysops? We are looking for new authors.