Practitioner Level

Exporting and Importing Data between Cloud SQL Environments

Pricing
1.5 hours
  • 8 Learning Objectives

About this Hands-on Lab

In some cases, you will have the ability to use and store data from one Cloud SQL instance to another. In this hands-on lab, we will learn how to export data into Cloud Storage from one Cloud SQL instance and import date from Cloud Storage to another Cloud SQL instance.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Create 2 Cloud SQL Instances
  1. From the main console navigation, head over to SQL.
  2. Click on Create Instance.
  3. Choose MySQL.
  4. Name the instance instance-1
    Root pass: 12345
    Database Version: MySQL 5.7

    1. Scroll down to Zonal Availability and set to Single zone
    2. Under Customize your instance expand the options and within Machine type choose the Shared core type.
  5. Click Create.

For Instance-2:

  1. From the main console navigation, head over to SQL.
  2. Click on Create Instance.
  3. Choose MySQL.
  4. Name the instance instance-2
    Root pass: 12345
    Database Version: MySQL 5.7
    Scroll down to Zonal Availability and set to Single zone
    Under Customize your instance expand the options and within Machine type choose the Shared core type.
  5. Click Create.
Enable the Cloud SQL Admin API and Connect to Instance-1 using Cloud Shell
  1. From the main console navigation, head over to APIs and Services.
  2. Click on Enable APIs and Services.
  3. Then type in Cloud SQL Admin API.
  4. Click Enable.

Connect to the Cloud SQL instance

  1. From the SQL console, click on the patient-records-instance instance.
  2. Head to the Connect to the instance section.
  3. Click on Connect using the cloud shell
  4. Use the command pwd and copy your directory for further use.
  5. Enable local file upload to Cloud SQL by editing the mysql my.cnf file – run this one-liner to add the required setting: l sudo sh -c 'echo -e "[mysql]nlocal-infile=1" >> /etc/mysql/my.cnf'
  6. Now use the gcloud sql connect instance-1 --user=root --quiet command. Press Enter
  7. Put in the root password which is 12345 and press enter
  8. You are now logged into the MySQL instance.
Create a Table and Database on Instance-1
  1. Use the command CREATE DATABASE sneakerinfo;.
  2. Use the command USE sneakerinfo; to use the database to create a table.
  3. Use the command CREATE TABLE sneakerrelease (name VARCHAR(15), model VARCHAR(30), datereleased VARCHAR(30));
  4. Verify the table has been created by using command SHOW TABLES;
  5. Use the 3 dotted horizontal lined button on the top right of the cloud shell and click on More, then click on Upload file and upload the CSV file downloaded from the instructions.
  6. Use command LOAD DATA LOCAL INFILE '/home/your directory/SneakersandDates.csv' INTO TABLE sneakerrelease FIELDS TERMINATED BY ',';
Create a Storage bucket
  1. From the main console navigation, head over to Storage.
  2. Click on Create Bucket.
  3. Name your bucket sneakerinfo.
  4. Keep Configuration set to the defaults.
  5. Click Create.
  6. In the Cloud Storage console, click Create Folder
  7. Name the folder sneakerrelease.
Export data from Instance-1 to Cloud Storage
  1. From the SQL console, click on Export.
  2. Choose the Cloud Storage folder you want to export to.
  3. Choose CSV as the file type and use query *SELECT FROM sneakerrelease**
  4. Click on Advance Options and choose sneakerinfo database.
  5. Click on Export.
Connect to Instance-2
  1. From the SQL console, click on the Instance-2 instance.
  2. Head to the Connect to the instance section.
  3. Click on Connect using the cloud shell.
  4. Now use the gcloud sql connect instance-2 --user=root --quiet command. Press Enter
  5. Put in the root password, which is 12345, and press Enter
  6. You are now logged into the MySQL instance.
Create a Database and Table within Instance-2
  1. Use the command CREATE DATABASE sneakerinfo;.
  2. Use the command USE sneakerinfo; to use the database to create a table.
  3. Use the command CREATE TABLE sneakerrelease (name VARCHAR(15), model VARCHAR(30), datereleased VARCHAR(30));
  4. Verify the table has been created by using command SHOW TABLES;.
Import data to Instance 2
  1. From the SQL console, click on Import.
  2. Choose the Cloud Storage bucket sneakerinfo and folder sneakerrelease.
  3. From that folder, you will see the SQL Export file.
  4. Choose database sneakerinfo and type in sneakerrelease for the table and click on Import.

Verify the data has been imported:
Use command SELECT * FROM sneakerreleaseG;

Contact SalesSee Pricing

Additional Resources

You’ve been tasked to set up a test environment for your team to be able to export and import data from one Cloud SQL Instance to another. They want to make sure that there will not be any corruption of files for the structure of the data in this process.

To complete this task, you will:

  1. Create 2 SQL Instances.
  2. Create a database and table in the first instance.
  3. Create a storage bucket to export into and import from.
  4. Export data from the first instance in the storage bucket.
  5. Import data from the storage bucket into the second instance.

Here is the csv file to complete the task: https://github.com/BPalmerLA/googlecloud/blob/master/SneakersandDates

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Don't have an account?

Get Started
Who’s going to be learning?
MyselfMy Organization