GCP Cloud SQL

Cloud SQL is GCP's fully managed relational database service. It supports MySQL, PostgreSQL, and SQL Server. "Fully managed" means Google handles the database installation, patches, backups, replication, and failover — the focus stays on the application, not on database maintenance.

A relational database stores data in tables with rows and columns, just like a spreadsheet. Cloud SQL is the right choice when the application needs structured data, transactions, and standard SQL queries — like an e-commerce store managing products, orders, and customers.

Why Use Cloud SQL Instead of a Self-Managed Database?

Cloud SQL Instance Architecture

Application (App Engine / Compute Engine / Cloud Run)
        │
        │ Connection via Cloud SQL Auth Proxy or Private IP
        ▼
┌───────────────────────────────────┐
│  Cloud SQL Instance               │
│                                   │
│  ┌────────────────┐               │
│  │  Primary DB    │               │
│  │  (Read/Write)  │               │
│  └───────┬────────┘               │
│          │ Replication            │
│  ┌───────▼────────┐               │
│  │  Standby DB    │ (HA only)     │
│  │  (Failover)    │               │
│  └────────────────┘               │
│                                   │
│  ┌────────────────┐               │
│  │  Read Replica  │ (optional)    │
│  │  (Read only)   │               │
│  └────────────────┘               │
└───────────────────────────────────┘

Creating a Cloud SQL Instance

Via the Console

1. Go to SQL → Create Instance
2. Choose a database engine (MySQL, PostgreSQL, or SQL Server)
3. Enter an instance ID (example: my-mysql-db)
4. Set a root password
5. Choose a region (same region as the application for low latency)
6. Select a machine type (example: db-f1-micro for development)
7. Click Create Instance — this takes 2–5 minutes

Via Cloud Shell

gcloud sql instances create my-mysql-db \
  --database-version=MYSQL_8_0 \
  --tier=db-f1-micro \
  --region=us-central1

Connecting to Cloud SQL

Cloud SQL Auth Proxy (Recommended)

The Cloud SQL Auth Proxy is a small program that creates a secure, encrypted tunnel between an application and the Cloud SQL instance. It handles authentication automatically using IAM, so database passwords are not the only layer of security.

Application
    │
    │ Connects to 127.0.0.1:3306 (local)
    ▼
Cloud SQL Auth Proxy (running locally)
    │
    │ Encrypted tunnel (TLS)
    ▼
Cloud SQL Instance (in GCP)

Starting the proxy:

./cloud-sql-proxy my-project:us-central1:my-mysql-db

Private IP Connection

For applications running inside the same VPC as Cloud SQL, private IP is the most secure and performant option. The database is not exposed to the internet at all.

Creating a Database and Table

# Connect to the instance from Cloud Shell
gcloud sql connect my-mysql-db --user=root

# Inside the MySQL prompt:
CREATE DATABASE store;
USE store;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  stock INT
);

INSERT INTO products (name, price, stock)
VALUES ('Laptop', 49999.00, 10),
       ('Mouse', 999.00, 50);

SELECT * FROM products;

Output:

+----+--------+----------+-------+
| id | name   | price    | stock |
+----+--------+----------+-------+
|  1 | Laptop | 49999.00 |    10 |
|  2 | Mouse  |   999.00 |    50 |
+----+--------+----------+-------+

Backups and Recovery

Automated Backups

Cloud SQL takes daily backups automatically. The backup window (time of day) can be configured. By default, 7 days of backups are retained.

Point-in-Time Recovery (PITR)

PITR allows restoring a database to any specific second within the retention window. This is useful for recovering from accidental data deletion or corruption.

Example: Data accidentally deleted at 2:47 PM
→ Restore to 2:46 PM using PITR
→ Database returns to its state one minute before the deletion

High Availability (HA)

Enabling HA creates a standby instance in a different zone within the same region. If the primary instance fails, Cloud SQL automatically promotes the standby (failover) within 60 seconds — without any changes to the application's connection string.

Zone: us-central1-a        Zone: us-central1-b
┌─────────────────┐        ┌─────────────────┐
│  Primary DB     │  ────▶ │  Standby DB     │
│  (Active)       │        │  (Passive)      │
└─────────────────┘        └─────────────────┘
       │
  If primary fails:
       ▼
Standby promoted → application reconnects automatically

Read Replicas

A read replica is a copy of the primary database that handles only SELECT queries. Applications that need to run heavy reports or analytics queries should use the replica, keeping the primary database fast for write operations.

Application Writes: INSERT, UPDATE, DELETE → Primary Instance
Application Reads:  SELECT queries         → Read Replica

Key Takeaways

• Cloud SQL is a fully managed MySQL, PostgreSQL, or SQL Server service.
• Google handles backups, patches, replication, and failover automatically.
• Use the Cloud SQL Auth Proxy or Private IP for secure connections.
• High Availability provides automatic failover across zones with no downtime.
• Read Replicas offload heavy read queries from the primary instance.
• Point-in-Time Recovery restores data to any specific second in the retention window.