Google Cloud Platform: Cloud SQL service - Setup GCP SQL Service MYSQL Instance and access DB remotely

Cloud SQL is a VM that’s hosted on Google Compute Engine, managed by Google, running a version of the MySQL binary. In simple words "Cloud SQL is MySQL in a box that runs on top of Compute Engine."

You can change all of those settings in the Cloud Console, the Cloud SDK command-line tool, or the REST API.

All of the major cloud-hosting providers offer this sort of service—for example, Amazon has Relational Database Service (RDS), Azure has SQL Database service, and Google has Cloud SQL service. 

In this post we will spawn a MYSQL DB instance and access it remotely & perform CRUD operation.

Turning on a Cloud SQL instance

1. Go to Cloud Console cloud.google.com/console. From left side menu choose SQL or resources click on SQL.
   
   
   
   
   
2. Click on "CREATE INSTANCE" and choose Database engine as MYSQL
   
   
    
   
   
   
   
3.  Provide db instance name and root user password & click on CREATE INSTANCE.  Please note down password for future use.
   
   
   
   
   
4. Once DB ready, a green tick will appear (It will take a while to setup).
   
   
   
   
   
5. Validate DB instance detail and status from Cloud shell.
   

   nikhilranjan2712@cloudshell:~ (named-magnet-342122)$ gcloud sql instances list
   API [sqladmin.googleapis.com] not enabled on project [121905798954]. 
   Would you like to enable and retry (this will take a few minutes)? (y/N)?  y
   
   Enabling service [sqladmin.googleapis.com] on project [121905798954]...
   Operation "operations/acat.p2-121905798954-ad384648-3cbf-4bf7-a303-0f00a6d8477c" finished successfully.
   NAME: devinline-gcp-mysql
   DATABASE_VERSION: MYSQL_5_7
   LOCATION: us-central1-f
   TIER: db-custom-4-26624
   PRIMARY_ADDRESS: 34.133.78.26
   PRIVATE_ADDRESS: -
   STATUS: RUNNABLE
   

   
   

Connect MYSQL Instance from an external client

We can use either UI based SQL client "DBeaver" or MYSQL native CLI. I am using UI based client to access MYSQL instance and perform database operations. 

1. Make GCP MYSQL Instance accessible from outside:
   From left panel select "Connection" and in "Networking" tab, click on "Add Network" and provide specific IP address range or a generic CIDR range(0.0.0.0/0) accessible from everywhere.
   
    
   

    
   
2. Copy Public IP address from overview left panel and using root user connect to MYSQL instance from DBeaver client.
   
   
    
   

   
    
3. After successful connection, lets create a Database, two users(readonly and appuser).
   
   
   
   
   
SQL Commands:

CREATE DATABASE ORDERS;

CREATE USER readonly IDENTIFIED BY 'very-strong-readonly-password';

CREATE USER appuser IDENTIFIED BY 'very-strong-appuser-password';

GRANT ALL PRIVILEGES ON ORDERS.* TO appuser;

-- Reload the list of users and privileges
FLUSH PRIVILEGES;


FLUSH PRIVILEGES tells MySQL to reload the list of users and privileges, if we do not execute this command MySQL would know about the changes when it restarts.


4. Validate Database and User created in GCP dashboard.
   
   
   
   
   
   
   
   
5. Updating the access configuration for Cloud SQL: In order to make MYSQL instance safe and attacks from the whole world, remove external access or make it limited to certain IP range.
   
   
   
   Click on delete and save it. 

 

At the end if you are using free tier of Google credit, delete DB Instance too, it will save credit for long term.

 

--- ======= ----