Import a database to a Google Cloud SQL instance

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.

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.

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

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

Select bucket and sample database SQL file

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

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

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

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

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

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:

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:

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:

ERROR: (gcloud.beta.SQL.connect) You do not have permission to access instance [104.197.170.224]: The client is not authorized to make this request

ERROR 2003 (HY000): Can't connect to MySQL server on '104.197.170.224' (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:

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:

mySQL> show databases;

This will display all databases in MySQL:

MySQL – show databases

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

mySQL> USE classicmodels;
mySQL> 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> SELECT * FROM customers WHERE contactLastName='King';

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