💾 Cloud SQL Setup

Introduction

Google Cloud SQL is a fully managed relational database service that supports MySQL, PostgreSQL, and SQL Server. It handles backups, replication, and patching automatically, allowing you to focus on your application.

Key Learning Outcomes

By the end of this lesson, you'll understand:

• What Cloud SQL is and when to use it

• Creating Cloud SQL instances

• Connecting from applications

• User management and authentication

• Backup and restoration

• Scaling and performance tuning

• Monitoring and logging

What is Cloud SQL?

Cloud SQL is a fully managed service that:

• Supports MySQL 8.0, PostgreSQL 14+, and SQL Server

• Handles automated backups and point-in-time recovery

• Provides automatic failover and high availability

• Scales up automatically with your application

• Includes automatic security updates

• Integrates seamlessly with other GCP services

Creating a Cloud SQL Instance

Step 1: Create Instance via Console

# Or use gcloud CLI
gcloud sql instances create my-database \
  --database-version=MYSQL_8_0 \
  --region=us-central1 \
  --tier=db-f1-micro \
  --backup-start-time=03:00 \
  --enable-bin-log

Step 2: Create Database

# Create database
gcloud sql databases create myapp_db \
  --instance=my-database

# Create user
gcloud sql users create app_user \
  --instance=my-database \
  --password

Step 3: Create Tables

MySQL:

gcloud sql connect my-database \
  --user=root \
  --quiet

# Then in MySQL prompt:
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Connecting from Applications

Node.js Connection

package.json:

{
  "dependencies": {
    "mysql2": "^3.0.0",
    "dotenv": "^16.0.0"
  }
}

server.js:

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'your-instance-ip',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  database: 'myapp_db',
  connectionLimit: 10,
  waitForConnections: true
});

async function getUsers() {
  const connection = await pool.getConnection();
  try {
    const [rows] = await connection.execute('SELECT * FROM users');
    return rows;
  } finally {
    connection.release();
  }
}

async function createUser(name, email) {
  const connection = await pool.getConnection();
  try {
    const [result] = await connection.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    return result.insertId;
  } finally {
    connection.release();
  }
}

Python Connection

requirements.txt:

mysql-connector-python==8.0.33
python-dotenv==1.0.0

app.py:

import mysql.connector
from mysql.connector import pooling
import os

dbconfig = {
    "host": os.environ.get("DB_HOST"),
    "user": "app_user",
    "password": os.environ.get("DB_PASSWORD"),
    "database": "myapp_db"
}

pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=5, **dbconfig)

def get_users():
    conn = pool.get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM users")
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

def create_user(name, email):
    conn = pool.get_connection()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", (name, email))
    conn.commit()
    user_id = cursor.lastrowid
    cursor.close()
    conn.close()
    return user_id

Cloud SQL Proxy

Install and Use Proxy

# Download Cloud SQL Proxy
curl -o cloud-sql-proxy https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
chmod +x cloud-sql-proxy

# Run proxy
./cloud-sql-proxy my-project:us-central1:my-database &

# Connect via localhost:3306
mysql -h 127.0.0.1 -u app_user -p

Backups and Recovery

Create Manual Backup

# Create on-demand backup
gcloud sql backups create \
  --instance=my-database

# List backups
gcloud sql backups list --instance=my-database

# Restore from backup
gcloud sql backups restore BACKUP_ID \
  --backup-instance=my-database

Enable Automated Backups

gcloud sql instances patch my-database \
  --backup-start-time=03:00 \
  --transaction-log-retention-days=7

User Management

Create Database Users

# Create user with password
gcloud sql users create app_user \
  --instance=my-database \
  --password

# Specific permissions
gcloud sql users set-password app_user \
  --instance=my-database \
  --password

Scaling

Scale Storage

gcloud sql instances patch my-database \
  --storage-size=50GB

Change Machine Type

gcloud sql instances patch my-database \
  --tier=db-n1-standard-1

Monitoring

View Metrics

gcloud monitoring time-series list \
  --filter='resource.type=cloudsql_database'

Best Practices

• Use Cloud SQL Proxy for secure connections

• Enable automated backups and point-in-time recovery

• Use strong passwords and rotate regularly

• Set connection limits and timeouts

• Monitor CPU, memory, and disk usage

• Use read replicas for scaling read operations

Key Takeaways

• **Cloud SQL** is a fully managed relational database service

• **Support** for MySQL, PostgreSQL, and SQL Server

• **Connection pooling** improves performance and reduces overhead

• **Backups** are automatic and point-in-time recovery available

• **Cloud SQL Proxy** provides secure authenticated connections

• **Scaling** is simple with automatic and manual options

• **Monitoring** helps detect and prevent issues

Next Steps

Learn about Cloud SQL replication for high availability, or explore connecting to Cloud Run applications.