Cloudsql Pocketbook

Posted on by uplatzblog

Cloud SQL (GCP) Pocket Book — Uplatz

50 deep-dive flashcards • Wide layout • Fewer scrolls • 20+ Interview Q&A • MySQL / PostgreSQL / SQL Server • GCP-native security & access

Section 1 — Introduction to Cloud SQL

1) What is Cloud SQL?

Cloud SQL is Google Cloud’s fully managed relational database service for MySQL, PostgreSQL, and SQL Server. It handles backups, patching, failover, replication, monitoring, and maintenance, letting developers focus on application logic.

# Example: Creating MySQL instance via gcloud CLI
gcloud sql instances create my-instance \
  --database-version=MYSQL_8_0 \
  --cpu=2 --memory=4GB \
  --region=us-central1

2) Cloud SQL vs Other GCP Databases

Cloud SQL is ideal for structured relational data. Compared to BigQuery (analytics), Firestore (NoSQL), and Spanner (global SQL), Cloud SQL is optimized for transactional workloads with minimal operational overhead.

Pick Cloud SQL when migrating legacy relational apps or using ORMs like Sequelize, Hibernate, or Prisma.

3) Supported Engines & Versions

Cloud SQL supports:

  • MySQL: 5.6, 5.7, 8.0
  • PostgreSQL: 9.6 to 15+
  • SQL Server: 2017 Standard/Enterprise, 2019 Express

Choose the engine based on app compatibility, ecosystem, and performance needs.

4) Connecting to Cloud SQL

You can connect using:

  • Private IP (recommended for VPC security)
  • Public IP with SSL or Cloud SQL Auth Proxy

Use the Auth Proxy for secure IAM-based auth in local/dev environments.

./cloud-sql-proxy --instances=my-instance=tcp:3306

5) Instance Configuration

Customize your instance with:

  • vCPU and memory size
  • Region and zone placement
  • Disk type (SSD or HDD) and size
  • High availability (HA) or single zone

Scale up or down without downtime (for HA-enabled instances).

6) Backups, Failover & Maintenance

Enable automated backups and point-in-time recovery (PITR) to guard against data loss. Maintenance windows can be configured for version upgrades and patching.

Failover: When HA is enabled, Cloud SQL auto-promotes standby instance in case of failure.

7) Cloud SQL IAM & Access Control

Use GCP IAM for:

  • Instance-level access
  • Connection-level control via IAM DB AuthN
  • Role-based management of backups, users, and billing
gcloud projects add-iam-policy-binding my-project \
  --member=user:dev@example.com \
  --role=roles/cloudsql.editor

8) Connecting from GCE, Cloud Run, or GKE

Use the Cloud SQL connection name in the appropriate env var or connection string. For GKE, use a sidecar container with Cloud SQL Auth Proxy. For Cloud Run, specify --add-cloudsql-instances at deploy time.

gcloud run deploy my-app \
  --add-cloudsql-instances=my-project:us-central1:my-instance

9) Monitoring with Cloud Logging & Metrics

Cloud SQL exposes logs (error, slow query, general) and metrics (CPU, memory, disk) to Cloud Monitoring. Set up alerts on thresholds like replica lag or CPU usage to maintain performance.

# Enable slow query logs
gcloud sql instances patch my-instance \
  --database-flags=log_output=FILE,slow_query_log=on

10) Q&A — “When should I choose Cloud SQL over Spanner?”

Answer: Use Cloud SQL when you need a familiar, fully-managed relational DB (e.g., MySQL/PostgreSQL) with vertical scaling. Choose Spanner when you require global horizontal scaling, strong consistency across regions, and millisecond failover. Cloud SQL is simpler; Spanner is built for massive-scale, multi-region use cases.