- 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.
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.
