Skip to content

Google Cloud SQL

Overview

Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server. This frees you from database administration tasks so that you have more time to manage your data.

SAD - System architechture design

Each Cloud SQL instance is powered by a virtual machine (VM) running on a host Google Cloud server.

Each VM operates the database program, such as MySQL Server, PostgreSQL, or SQL Server, and service agents that provide supporting services, such as logging and monitoring.

The high availability option also provides a standby VM in another zone (same region) with a configuration that's identical to the primary VM.

The database is stored on a scalable, durable network storage device called a persistent disk that attaches to the VM.

A static IP address sits in front of each VM to ensure that the IP address an application connects to persists throughout the lifetime of the Cloud SQL instance.

Cloud SQL instance overview:

flowchart LR
  subgraph sql[Cloud SQL instance]
    direction LR
    ip[Static IP]
    high_availability[HA standby]
    disk[Persistent Disk]
    %% Component
    subgraph vm[Original VM]
      direction LR
      db[Database]
      log[Logging]
      monitor[Monitoring]
      other[Other system]
    end
  end

  %% Flow
  app[Application] <--> ip <--> vm -.- high_availability
  vm <--> disk

Key terms

Cloud SQL instance

A Cloud SQL instance corresponds to one virtual machine (VM). The VM includes the database instance and accompanying software containers to keep the database instance up and running.

Database instance

A database instance is the set of software and files that operate the databases: MySQL, PostgreSQL or SQL Server.

Public and private IP

In Cloud SQL, public IP means that the instance is accessible through the public internet. In contrast, instances using only private IP are not accessible through the public internet, but are accessible through a Virtual Private Cloud (VPC). Cloud SQL instances can have both a public and a private IP address.

High availability

Cloud SQL instances using high availability (HA) provide greater reliability than non-HA instances.

HA in Cloud SQL works by having two synchronized instances: a primary instance and a standby instance. Each instance has exactly one VM. Each instance is in a different zone in the same region.

Failover

A failover is when Cloud SQL switches serving from the original primary instance to the standby instance.

Autofailover is a mechanism that automatically triggers failover when a Cloud SQL instance didn't issue a heartbeat in the previous interval.

Standby instance

The standby instance is used in high availability to replace the primary instance when failover occurs. The standby instance doesn't appear in the Google Cloud console. When failover occurs, connections to the primary instance are automatically transferred to the standby instance.

Clone

When you clone a Cloud SQL instance, you create a new instance that is a copy of the source instance, but is completely independent. After cloning is complete, changes to the source instance are not reflected in the clone, and changes in the clone are not reflected in the source instance.

Replication

Replication is the ability to create copies of a Cloud SQL instance or an on-premises database, and offload work to the copies. The main reason for using replication is to scale the use of data in a database without degrading performance on the primary instance.

Read replica

The read replica is an exact copy of the primary instance. Data and other changes on the primary instance are updated in almost real time on the read replica. Send your write transactions to the primary instance, and your read requests to the read replica. The read replica processes queries, read requests, and analytics traffic, thus reducing the load on the primary instance.

Source server

Replication copies transactions from a primary instance to one or more read replicas. The primary instance is also called the source server. The source server can be a Cloud SQL primary instance, or a server outside of Google Cloud, such as an on-premises server or a server running in a different cloud. If the source server is outside of Google Cloud, we call it Replication from an external server.

Cloud SQL Auth Proxy client

The Cloud SQL Auth Proxy client is open source software maintained by Cloud SQL. It connects to a companion process, the Cloud SQL Auth Proxy server, running on your Cloud SQL instance. You run the Cloud SQL Auth Proxy client on your own servers. The Cloud SQL Auth Proxy client can be used to establish a secure SSL/TLS connection to the database instance, and/or to avoid having to open the firewall. Authentication is done through Identity and Access Management (IAM).

Source Reference