Latest posts by Jason Coltrin (see all)
- Windows 10 Fall Creators Update installation and features - Thu, Nov 2 2017
- Install Microsoft SQL Server on Ubuntu Linux - Thu, Jan 5 2017
- Use PowerShell with Google Cloud Platform - Thu, Dec 8 2016
In my last article we provided an overview of the Google Cloud Platform and all of the available products thereon. The Cloud SQL service that Google provides is a high performance, highly scalable, secure, fully managed, and integrated SQL solution that offers standard API access. Through Google's "Partner Ecosystem," you can further gain access to integrated tools such as BIME, Tableau, Looker, and Yellowfin. These tools can connect to your Cloud SQL instance to generate dashboards and reports, making your data easier to understand and share.
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.
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'.
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 the 'sqltestinstance' project that we named earlier, and then click CONTINUE:
In the Create a bucket tutorial dialog, follow the guide and click on the Storage menu in the main console:
Select the Storage console:
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'.
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.
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):
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.
Next, click Create instance:
Next we're asked to choose the instance type. We'll use the recommended Cloud SQL Second Generation > Choose Second Generation.
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 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.
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.
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.
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.
When we click Done, our instances will build for a small period of time and then we'll see our newly created instances.
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.
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.
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.