Working with SQL in GCP

Creating a private CloudSQL instance

1. Go to google console

2. Select MySQL instance or whatever flavor you want.

3. 

   See the configurations which I chose for the cheapest alternative:

4. 

   NOTE: There are many factors and options to keep in mind. Some of the are whether we want it to be on public IP or on private IP. Do we want to delete the instance after our work?

5. It is also important to select the correct tier as selecting the wrong tier or options could cause underutilization of resources or overspending of money. For instance, for production environment, high availablility, disaster recivery, security and many other factors will come into play unlike a dev environment which we just created above.

Doing the same using Gcloud commands:

//enable the API before usage
gcloud services enable sqladmin.googleapis.com

//creating the server
gcloud sql instances create mysql-server --database-version=MYSQL_8_0 --tier=db-f1-micro --region=europe-west1

//listing the users asscoiated
gcloud sql users list --instance=mysql-server1

//setting the password
gcloud sql users set-password root --host % --instance=mysql-server1 --password pwadmin

//Connecting to the DB
gcloud sql connect mysql-server --user=root

//Resetting the password
gcloud sql users set-password root --host % --instance=mysql-server1 --password rootv2

Connecting to Instance and creating a DB

gcloud sql connect mysql-dev --user=root
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 89
Server version: 8.0.31-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> create database appdb;
Query OK, 1 row affected (0.19 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.19 sec)

mysql> \q
Bye

One can also see the DB using the console:

Accessing private cloud methods

Good way: Using a VM on Compute Engine and VPC

Bad way: Serverless services like Cloud Run, Cloud Function and App Engines and there are built in ways to do this.

Ugly way: Doing it from local computer using Bastion Host

Accessing Private cloud SQL from Compute Engine

Important Diagram

1. Creating a IA Proxy(makes application more secure)

2. Getting a Compute Engine VM

3. Creating a mysqlclient (or installing workbench in case of windows)

4. Finally create connection with the CloudSQL

---

Detailed Steps

Creating a VM: I created it using the minimum configurations. Use: https://cloud.google.com/compute/docs/instances/create-start-instance

gcloud compute --project=superb-cubist-423522-h6 firewall-rules create iap-access --direction=INGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp:22,tcp:2289 --source-ranges=35.235.240.0/20

Since it is a private IP, it will not have Internet connection. In this situation, we use something called NAT.

Use: https://cloud.google.com/nat/docs/set-up-manage-network-address-translation

Login to the VM:

sudo apt update
sudo apt-get install default-mysql-client

References

These are some very good documentations which I have referred while playing around GCP.

1. https://cloud.google.com/sql/docs/postgres/configure-private-ip#connect_from

2. https://medium.com/google-cloud/cloud-sql-with-private-ip-only-the-good-the-bad-and-the-ugly-de4ac23ce98a