To complete this lab, you need:
Internet access
Access to a supported Internet browser:
Console: Products and Services > Compute Engine > VM instances
Click on [Create instance].
|
|
Name: | mydb-client |
Zone: | us-central1-f |
Machine type: | 1 vCPU (n1-standard-1) |
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]
|
|
Name: | infra-db |
Database version: | 5.7 |
Region: | us-central1 |
Zone: | us-central1-f |
Storage type: | SSD |
Storage capacity: | 10 GB |
Root password: | <a password that's easy to remember> |
In the Authorized networks section, click [+ Add new network]
|
|
Name: | mydb-client |
Network: | <IP address of VM>/32 |
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.
Console: Products and Services > SQL
Click on your database infra-db
Click on Access control and in the line beneath it, on the SSL tab:
In the SSL Connections section, click on [Allow only SSL connections].
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.
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:
©Google, Inc. or its affiliates. All rights reserved. Do not distribute.