How To Upload Database To Google Cloud

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

Author
Recent Posts

Jason Coltrin has been working in Information technology for more than 17 years. He holds an MCSE 2003 Security+ plus various Palo Alto and SonicWall firewall certifications. He also is an gorging Linux administrator and currently works in the finance Industry.

In my previous article, I provided an overview of the Google Cloud Platform, explained how to create a Deject 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 lot to focus on your data and non have to build, monitor, update, upgrade, and troubleshoot a fully scalable, enterprise-ready SQL datacenter. Follow the steps and yous can run SQL on the Google Cloud Platform without having to set your own SQL infrastructure.

Import SQL Database from Storage Bucket ^

If y'all oasis't already exported your database and created a new SQL case on the Google Deject Platform, you may desire to get back over those steps in the outset part of the commodity serial here. With the creation of our instance out of the way, we tin now put our instance to work by importing the mySQLsampledatabase.sql file nosotros already uploaded into our storage bucket. Under Case details, click the Import link:

Import database into SQL

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

Select bucket and sample database SQL file

Select saucepan and sample database SQL file

Next select the Format important: SQL, and so 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 stride, you may need to become back to the export of your data and format the consign to remove problematic information. In the commencement article, yous 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 carte and advanced options.

Apply the Google Cloud Crush (console) to connect to the SQL Instance ^

Although there are a multitude of tools bachelor to us for managing a MySQL database, we'll offset with the Google Cloud Shell to connect to our database, show tables, and run a basic query. To become started, click on the Activate Google Cloud Shell icon in the upper right-hand corner of your browser.

Activate Google Cloud Shell

Actuate Google Cloud Shell

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

Google Cloud Shell overview

Google Cloud Crush overview

If this is the get-go you lot've seen the Google Cloud Shell, you lot may besides appreciate the integration of the shell into the browser and the way it looks and feels. Exist sure to read the file named README-cloudshell.txt in the default directory.

Google Cloud Shell started

Google Cloud Beat started

Before we can log into our MySQL server as root, we volition start need to fix the Root password. Nosotros tin can do this by going to SQL > Instances > classic-models-SQL-instance > Access Control > Users > Modify root countersign.

SQL instance access control users

SQL example access control users

In example you jumped ahead and have already attempted to connect to your SQL case, yous may accept had a trouble connecting to the instance with ane of the post-obit errors:

jasoncoltrin@SQLtestinstance-149405:~$ gcloud beta SQL connect SQLtestinstance-149405:us-central1:classic-models-SQL-example --user=root

ERROR: (gcloud.beta.SQL.connect) Instance names cannot contain the ':' grapheme. If you meant to signal theproject for [archetype-models-SQL-instance], utilize only 'classic-models-SQL-example' for the argument, and either add'--project SQLtestinstance-149405:united states of america-central1' to the command line or starting time run $ gcloud config prepare project SQLtestinstance-149405:us-central1

Remember that when you open the Google Deject Beat, you are initially in a different VM, which may start in a different projection. To ensure yous're in the correct project, you can switch to your SQL instance project with something similar to the following control:

gcloud config set project SQLtestinstance-149405:us-central1

Y'all tin also listing the instances bachelor by issuing the command:

Gcloud SQL instances list

This control volition prove our instances and IPs:

Gcloud SQL instances list

Gcloud SQL instances listing

At present that we've continued to our right 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 archetype-models-SQL-instance --user=root

The shell should brandish something nearly whitelisting your IP and so prompt y'all for the root password that we set before (be careful – you merely have about 1 minute to enter the countersign).

If we cannot connect, we will probably receive 1 of the following two errors:

          ERROR:          (gcloud.beta.SQL.connect) You do not have permission to access example [104.197.170.224]: The client is non authorized to make this request  Mistake 2003 (HY000): Tin't connect to MySQL server on '104.197.170.224' (110)

These errors are typically associated with inaccurate setting of your external IP accost in Access Control > Authorized Networks. Double-check the authorized networks and attempt over again. I've obtained the best results by using "What'southward my IP address?" in Google, then take the IP information technology provides and authorize that network with a /32 CIDR.

If everything goes correctly, you should now see the MySQL prompt:

Connection to MySQL complete

Connexion to MySQL complete

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

mySQL> bear witness databases;

This will brandish all databases in MySQL:

MySQL – show databases

Next, to utilise our classicmodels database and display the tables, issue the post-obit commands:

mySQL> Employ classicmodels; mySQL> Evidence TABLES;

MySQL – use database and show tables

MySQL – use database and prove tables

Lastly, we can run a basic query, for example to display all customers who take the final name 'Rex':

mySQL> SELECT * FROM customers WHERE contactLastName='King';

MySQL – run basic query

From here, we tin exit and and so use the data and the server in any number of different ways such as Google'south Partner Ecosystem, our own app, a PHP website, or SQL Workbench. Brand sure you lot enable SSL certificates if y'all'll exist accessing your database from exterior of the Google Cloud Platform.

Cloud SQL example conclusion ^

In this article, we described the Google Cloud SQL case, we went through the steps to prepare a loftier-availability SQL instance, imported a SQL database, connected to the MySQL service via the Cloud Shell, and lastly ran a few MySQL commands. Hopefully y'all'll capeesh the power, flexibility, and efficiency of running SQL in the Google Cloud Platform. With the toll benefits and security features built into this solution, Google'southward SQL example 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 y'all'd similar to run into additional posts about viewing and managing your SQL database with third-party tools, let us know in the comments.