What you need

To complete this lab, you need:

Internet access

Access to a supported Internet browser:

• The latest version of Google Chrome, Firefox, or Microsoft Edge
• Microsoft Internet Explorer 11+
• Safari 8+ (Safari private mode is not supported)

What you do

• Create a Cloud SQL instance
• Create a VM to serve as a database client and install software
• Restrict access to the Cloud SQL instance to a single IP address
• Download sample GCP billing data in *.csv format and load that into the database
• Configure the Cloud SQL instance and the client to use SSL encryption

What you learn

• How to setup and use Cloud SQL with a client
• How to control access through access management (MySQL user and pwd)
• How to control authorization using authorized IP addresses
• How to cause database communications to be encrypted

Step 1 Create a VM to serve as the database client

Console: Products and Services > Compute Engine > VM instances

Click on [Create instance].

When it is operational, copy it's External IP address to be used in the next section.

Console: Products and Services > SQL

Click on [Create instance].

Click on [Choose Second Generation]

In the Authorized networks section, click [+ Add new network]

The /32 in CIDR notation means "this exact address of a single VM".

There are several ways to connect to a Cloud SQL server.

Click [Done].

Click [Create] to create the database instance.

Console: Products and Services > Compute Engine > VM instances

SSH to the mydb-client VM.

Update the packages:

$ sudo apt-get update

Install mysql-client:

$ sudo apt-get install mysql-client

Y

Verify database access

Start the mysql client and verify that the local database server is working.

$ mysql --host=<IP of CloudSQL instance> -u root -p
$ <password>
mysql> show databases;

exit;

Download a sample Google Cloud billing data *.csv file.

curl -O https://storage.googleapis.com/gcp-infrastructure-class-billing-export-examples/Example-billing-export.csv

Create a database named resources.

$ mysql --host=<CloudSQL_IP> -u root -p
$ <password>
mysql> create database resources;
mysql> use resources;

Create the table named billing in the resources database.

mysql> 

CREATE TABLE billing
(
Account_ID varchar(255),
Line_Item varchar(255),
Start_Time varchar(255),
End_Time varchar(255),
Project varchar(255),
Measurement1 varchar(255),
Measurement1_Total_Consumption bigint(255),
Measurement1_Units varchar(255),
Cost varchar(255),
Currency int,
Project_Number varchar(255),
Project_ID varchar(255),
Project_Name varchar(255),
Project_Labels varchar(255),
Description varchar(255)
);

Verify that the table was created properly.

mysql> show tables;
mysql> describe billing;

exit;

You will now use the command mysqlimport to import the example billing data that you downloaded into the table in the database you created. However, mysqlimport requires that the *.csv file have the same name as the table.

Change the name of the data file.

$ mv

Example-billing-export.csv billing.csv

Load the data into the table.

$ mysqlimport --host=<CloudSQL_IP> --ignore-lines=1 --fields-terminated-by=, resources --verbose --local -u root -p  billing.csv

And verify that the data is now in the database.

$ mysql --host=<CloudSQL_IP> -u root -p
$ <password>

mysql> use resources;

mysql> describe billing;

mysql> SELECT Measurement1, Cost from billing;

The method you have used to connect to the CloudSQL database is fast to setup.

So far you have provided access management, in the form of a user ID and password. And you have provided authorization in the form of the single authorized IP address from the mydb-client VM.

But this setup does not afford privacy because the communications are being sent over an unencrypted connection over the Internet.

Step 1 View the details of your Cloud SQL database

Console: Products and Services > SQL

Click on your database infra-db

Step 2 Modify encryption settings

Click on Access control and in the line beneath it, on the SSL tab:

Step 3 Restrict access to SSL

In the SSL Connections section, click on [Allow only SSL connections].

Step 4 Create a client certificate

In the Client Certificates section, click on [Create a Client Certificate]

On the popup menu, give it the name mydb-client and click [Add].

This will update the security settings on the instance.

It will then provide you with the security file in a dialog.

Step 5 Create the client files

You will need to create the client files on the mydb-client VM.

Create each file using a text editor on the VM. Copy and paste the contents from the dialog.

Create these files on the VM

• client-key.pem
• client-cert.pem
• server-ca.pem

Step 6 Access the database over a secure link

$ mysql -uroot -p -h <CloudSQL_IP> \
    --ssl-ca=server-ca.pem \
    --ssl-cert=client-cert.pem \
    --ssl-key=client-key.pem

There are more methods to securely connect to a Cloud SQL instance.

You can read about them here:

https://cloud.google.com/sql/docs/mysql/connect-admin-ip

• Delete the mydb-client VM
• Delete the infra-db Cloud SQL instance

©Google, Inc. or its affiliates. All rights reserved. Do not distribute.