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 previous article, I provided an overview of the Google Cloud Platform, explained how to create a Cloud SQL Instance, and demonstrated the setup of a new project that will run a database in the cloud.
Running SQL on the Google Cloud Platform allows you to focus on your data and not have to build, monitor, update, upgrade, and troubleshoot a fully scalable, enterprise-ready SQL datacenter. Follow the steps and you can run SQL on the Google Cloud Platform without having to set up your own SQL infrastructure.
Import SQL Database from Storage Bucket ^
If you haven't already exported your database and created a new SQL instance on the Google Cloud Platform, you may want to go back over those steps in the first part of the article series here. With the creation of our instance out of the way, we can now put our instance to work by importing the mySQLsampledatabase.sql file we already uploaded into our storage bucket. Under Instance details, click the Import link:
Choose the bucket labeled sample-db-raw-classicmodels, and then select mySQLsampledatabase.sql.
Next select the Format of import: SQL, and then click Import.
With the .sql file imported successfully, the instance is essentially hosting and running the SQL database and is ready to be managed remotely. If you have trouble with this step, you may need to go back to the export of your data and format the export to remove problematic data. In the first article, you may have noticed a Custom Export method available in phpMyAdmin. A custom export allows you to define how you want your data formatted. Google provides information about the accepted formats under the help menu and advanced options.
Use the Google Cloud Shell (console) to connect to the SQL Instance ^
Although there are a multitude of tools available to us for managing a MySQL database, we'll start with the Google Cloud Shell to connect to our database, show tables, and run a basic query. To get started, click on the Activate Google Cloud Shell icon in the upper right-hand corner of your browser.
Next, review the Google Cloud Shell features and click Start Cloud Shell.
If this is the first you've seen the Google Cloud Shell, you may also appreciate the integration of the shell into the browser and the way it looks and feels. Be sure to read the file named README-cloudshell.txt in the default directory.
Before we can log into our MySQL server as root, we will first need to set the Root password. We can do this by going to SQL > Instances > classic-models-SQL-instance > Access Control > Users > Change root password.
In case you jumped ahead and have already attempted to connect to your SQL instance, you may have had a problem connecting to the instance with one of the following errors:
jasoncoltrin@SQLtestinstance-149405:~$ gcloud beta SQL connect SQLtestinstance-149405:us-central1:classic-models-SQL-instance --user=root
ERROR: (gcloud.beta.SQL.connect) Instance names cannot contain the ':' character. If you meant to indicate theproject for [classic-models-SQL-instance], use only 'classic-models-SQL-instance' for the argument, and either add'--project SQLtestinstance-149405:us-central1' to the command line or first run $ gcloud config set project SQLtestinstance-149405:us-central1
Remember that when you open the Google Cloud Shell, you are initially in a different VM, which may start in a different project. To ensure you're in the correct project, you can switch to your SQL instance project with something similar to the following command:
gcloud config set project SQLtestinstance-149405:us-central1
You can also list the instances available by issuing the command:
Gcloud SQL instances list
This command will show our instances and IPs:
Now that we've connected to our correct project in the gcloud shell, we want to connect to our SQL-instance MySQL server. To connect, issue the following command:
gcloud beta SQL connect classic-models-SQL-instance --user=root
The shell should display something about whitelisting your IP and then prompt you for the root password that we set earlier (be careful – you only have about 1 minute to enter the password).
If we cannot connect, we will probably receive one of the following two errors:
<strong>ERROR:</strong> (gcloud.beta.SQL.connect) You do not have permission to access instance [188.8.131.52]: The client is not authorized to make this request
ERROR 2003 (HY000): Can't connect to MySQL server on '184.108.40.206' (110)
These errors are typically associated with inaccurate setting of your external IP address in Access Control > Authorized Networks. Double-check the authorized networks and try again. I've obtained the best results by using "What's my IP address?" in Google, then take the IP it provides and authorize that network with a /32 CIDR.
If everything goes correctly, you should now see the MySQL prompt:
We're in! We can now issue MySQL commands. To check that our classicmodels database has been imported, we can issue the command:
mySQL> show databases;
This will display all databases in MySQL:
Next, to use our classicmodels database and display the tables, issue the following commands:
mySQL> USE classicmodels;
mySQL> SHOW TABLES;
Lastly, we can run a basic query, for example to display all customers who have the last name 'King':
mySQL> SELECT * FROM customers WHERE contactLastName='King';
From here, we can exit and then use the data and the server in any number of different ways such as Google's Partner Ecosystem, our own app, a PHP website, or SQL Workbench. Make sure you enable SSL certificates if you'll be accessing your database from outside of the Google Cloud Platform.
Cloud SQL instance conclusion ^
In this article, we described the Google Cloud SQL instance, we went through the steps to set up a high-availability SQL instance, imported a SQL database, connected to the MySQL service via the Cloud Shell, and lastly ran a few MySQL commands. Hopefully you'll appreciate the power, flexibility, and efficiency of running SQL in the Google Cloud Platform. With the price benefits and security features built into this solution, Google's SQL instance could make getting an enterprise-grade SQL solution a lot easier to obtain, and a lot less expensive than building your own hardware solution on premise. If you'd like to see additional posts about viewing and managing your SQL database with third-party tools, let us know in the comments.