In this post I will show you how to import a SQL database into a SQL instance, demonstrate how to connect to your SQL instance with the Google Cloud shell, and show how to run some basic commands against your SQL server. We will also troubleshoot errors connecting to the SQL instance with the Google Cloud Shell.

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:

Import database into SQL

Import database into SQL

Choose the bucket labeled sample-db-raw-classicmodels, and then select mySQLsampledatabase.sql.

Select bucket and sample database SQL file

Select bucket and sample database SQL file

Next select the Format of import: SQL, and then click Import.

SQL import format

SQL import format

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.

Activate Google Cloud Shell

Activate Google Cloud Shell

Next, review the Google Cloud Shell features and click Start Cloud Shell.

Google Cloud Shell overview

Google Cloud Shell overview

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.

Google Cloud Shell started

Google Cloud Shell started

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.

SQL instance access control users

SQL instance access control users

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:

You can also list the instances available by issuing the command:

This command will show our instances and IPs:

Gcloud SQL instances list

Gcloud SQL instances list

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:

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:

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:

Connection to MySQL complete

Connection to MySQL complete

We're in! We can now issue MySQL commands. To check that our classicmodels database has been imported, we can issue the command:

This will display all databases in MySQL:

MySQL – show databases

MySQL – show databases

Next, to use our classicmodels database and display the tables, issue the following commands:

MySQL – use database and show tables

MySQL – use database and show tables

Lastly, we can run a basic query, for example to display all customers who have the last name 'King':

MySQL – run basic query

MySQL – run basic query

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.

Read 4sysops without ads by becoming a member!

Your question was not answered? Ask in the forum!

0
Share
0 Comments

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2020

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account