Create a Cloud SQL PostgreSQL/MySQL database

In this post , I will demonstrate how to create a PostgreSQL and MySQL databases in GCP CloudSQL and how to access the database via cli and directly logging into the VM as well .

1. Create a PostgreSQL database instance from gcp console .

While the instance is being created , check the connection string and database configuration with which the instance is being created .

Now the instance is created and , you can see the metric chart as well to see certain metric utilization in dashboard

Using cloud shell try to connect to the PostgreSQL instance

list the available databases and try to create and query tables.

Create a Cloud SQL MySQL database using the CLI

Enter the following command to set the password for the root account.

student_04_6b4053ac5678@cloudshell:~ (qwiklabs-gcp-04-088c8b6f3ac6)$ gcloud sql users set-password root –host=% –instance=mysql-db –password=mysqlgcp
Updating Cloud SQL user…done.

Enter the following command to connect to your Cloud SQL database using the CLI

student_04_6b4053ac5678@cloudshell:~ (qwiklabs-gcp-04-088c8b6f3ac6)$ gcloud sql connect mysql-db --user=root --quiet
 Allowlisting your IP for incoming connection for 5 minutes…done.                                                                                             
 Connecting to database with SQL user [root].Enter password: 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 100
 Server version: 8.0.26-google (Google)
 Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 mysql> 

Connect to the MySQL database from a virtual machine

To add a vm instance to access your existing mysql database , go to connection

under authorized network , add the public ip of VM you just created and save

1. Once the update is complete, return to the Cloud Shell, enter the following command to SSH into the test client:

student_04_6b4053ac5678@cloudshell:~ (qwiklabs-gcp-04-088c8b6f3ac6)$ gcloud compute ssh test-client --zone=us-central1-a
 WARNING: The private SSH key file for gcloud does not exist.
 WARNING: The public SSH key file for gcloud does not exist.
 WARNING: You do not have an SSH key for gcloud.
 WARNING: SSH keygen will be executed to generate a key.
 This tool needs to create the directory [/home/student_04_6b4053ac5678/.ssh] before being able to generate SSH keys.
 Do you want to continue (Y/n)?  Y
 Generating public/private rsa key pair.
 Enter passphrase (empty for no passphrase): 
 Enter same passphrase again: 
 Your identification has been saved in /home/student_04_6b4053ac5678/.ssh/google_compute_engine
 Your public key has been saved in /home/student_04_6b4053ac5678/.ssh/google_compute_engine.pub
 The key fingerprint is:
 SHA256:RBrOh2KugqtQeVQ7sOtIhvsw8KMpPJbKVcO7iFJ/HGM student_04_6b4053ac5678@cs-337360459117-default
 The key's randomart image is:
 +---[RSA 3072]----+
 |    . o .        |
 |     * *         |
 |    = B o        |
 | . =.o +         |
 |o = ++  S        |
 |o.=. E          | |O.o.+ o         |
 |=&o.o +          |
 |%oo. o           |
 +----[SHA256]-----+
 Warning: Permanently added 'compute.7297932402976210167' (ECDSA) to the list of known hosts.

1. To install the MySQL client software on this machine, run the following commands:

student-04-6b4053ac5678@test-client:~$ sudo apt-get update
 Get:1 http://packages.cloud.google.com/apt google-compute-engine-bullseye-stable InRelease [5146 B]
 Get:2 http://packages.cloud.google.com/apt cloud-sdk-bullseye InRelease [6403 B]                                                                  
 Get:3 http://security.debian.org/debian-security bullseye-security InRelease [48.4 kB]                                

student-04-6b4053ac5678@test-client:~$ sudo apt-get install -y default-mysql-client
 Reading package lists… Done
 Building dependency tree… Done
 Reading state information… Done

1. To log on to the database server, use the command below but change the IP address to your Cloud SQL database’s IP address. (You should have pasted this address in a text file earlier in the lab. If not, you can find it in the Cloud SQL console.)

student-04-6b4053ac5678@test-client:~$ 
 student-04-6b4053ac5678@test-client:~$ mysql --host=34.136.58.157 --user=root --mysqlgcp
 mysql: unknown option '--mysqlgcp'
 student-04-6b4053ac5678@test-client:~$ mysql --host=34.136.58.157 --user=root --password
 Enter password: 
 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MySQL connection id is 309
 Server version: 8.0.26-google (Google)
 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 MySQL [(none)]> Ctrl-C -- exit!

This is the example of mysql managed db fromc cloudsql, it has db called managegcpdb created . we will login from vm to the same db and check if that db exists or not

student_04_6b4053ac5678@cloudshell:~ (qwiklabs-gcp-04-088c8b6f3ac6)$ gcloud compute ssh test-client --zone=us-central1-a
 Linux test-client 5.10.0-21-cloud-amd64 #1 SMP Debian 5.10.162-1 (2023-01-21) x86_64
 The programs included with the Debian GNU/Linux system are free software;
 the exact distribution terms for each program are described in the
 individual files in /usr/share/doc/*/copyright.
 Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
 permitted by applicable law.
 Last login: Sun Jun 25 09:24:06 2023 from 34.142.137.89
 student-04-6b4053ac5678@test-client:~$ mysql --host=34.136.58.157 --user=root --password
 Enter password: 
 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MySQL connection id is 497
 Server version: 8.0.26-google (Google)
 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 MySQL [(none)]> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | managegcpdb        |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
 5 rows in set (0.004 sec)
 MySQL [(none)]>

 111 total views,  1 views today