Cloud SQL: Backups & Replicas
Cloud SQL is Google Cloud’s fully managed relational database service for MySQL, PostgreSQL, and SQL Server. For the Associate Cloud Engineer exam, understanding how to protect data (Backups) and scale performance/availability (Replicas) is critical for operational excellence and reliability.
The “Library” Analogy
Imagine you manage a Master Library (Your Primary Instance):
- Backups: This is like taking a photocopy of every book every night and putting them in a fireproof safe. If the library burns down, you can recreate it from the copies.
- Read Replicas: These are smaller “Satellite Libraries.” They contain the same books but are only for reading. This prevents the Master Library from getting too crowded with students just looking for information.
- High Availability (HA): This is having a “Standby Library” across town. If the Master Library closes due to a power outage, all visitors are instantly redirected to the Standby Library.
Core Concepts: Backups
Backups allow you to restore a Cloud SQL instance to a previous state. They are essential for disaster recovery and data integrity.
1. Automated vs. On-Demand
- Automated Backups: Occur daily during a backup window. By default, 7 backups are retained.
- On-Demand Backups: Created manually by the user. These persist until you explicitly delete them, unlike automated backups which are deleted when the instance is deleted.
2. Point-In-Time Recovery (PITR)
PITR allows you to recover data to a specific second. It requires Binary Logging (MySQL) or Write-Ahead Logging (PostgreSQL) to be enabled. This is a common exam topic regarding data loss prevention.
Core Concepts: Replicas
Replicas serve two primary purposes: offloading read traffic and providing geographic redundancy.
| Feature | Read Replica | Failover Replica (HA) |
|---|---|---|
| Primary Use | Scaling Read Workloads | High Availability / Disaster Recovery |
| Location | Same or Different Region | Different Zone (Same Region) |
| Write Access | No (Read-Only) | No (Becomes Read/Write after failover) |
| Cost | Full Instance Cost | Full Instance Cost |
Decision Matrix
IF you need to improve read performance for global users… THEN use Cross-Region Read Replicas.
IF you need to protect against a single Zone failure… THEN enable High Availability (Regional).
IF you need to restore data to 10:05 AM yesterday… THEN use Point-In-Time Recovery (PITR).
IF you need to test a schema change without affecting production… THEN Clone the instance from a backup.
ACE Exam Tips
- Binary Logging: Must be enabled for PITR and for creating Read Replicas in MySQL.
- Backup Storage: Backups are stored in GCS, but you don’t manage the bucket; Google does.
- Instance Deletion: If you delete a Cloud SQL instance, all automated backups are deleted. On-demand backups are kept.
- Promotion: A Read Replica can be “Promoted” to a standalone instance, but this action is irreversible.
- Maintenance: HA does not eliminate maintenance downtime, but it minimizes it.
Cloud SQL Architecture Visualized
Key Services
Cloud SQL Proxy: Securely connect to instances without whitelisting IP addresses.
IAM Authentication: Use Google accounts to login to the DB instead of static passwords.
Common Pitfalls
Performance Hit: Enabling HA and Backups adds slight latency due to synchronous replication and log writing.
Storage: Backups consume storage space and are billed at a lower rate than the instance.
Architecture Patterns
Multi-Region: Primary in US-East1, Read Replica in Europe-West1 for low-latency local reads.
HA: Primary in Zone A, Standby in Zone B within the same region.
Quick Facts
Max Storage: Up to 64 TB (depending on engine).
Auto-Resize: Storage can be set to automatically increase when near capacity.