Cloud SQL supported engines (MySQL, PostgreSQL, SQL Server) in GCP - Commands & Configuration

Introduction

Cloud SQL lets you run databases in the cloud without managing servers. It supports three popular database engines: MySQL, PostgreSQL, and SQL Server. This helps you pick the right database type for your app easily.

When you want a managed MySQL database for a web app without handling backups or updates.

When your app needs PostgreSQL features like advanced data types or full-text search.

When you have an existing SQL Server database and want to move it to the cloud with minimal changes.

When you want automatic backups, scaling, and security for your database without manual setup.

When you want to connect your cloud database to other Google Cloud services easily.

Commands

This command creates a new Cloud SQL instance using MySQL version 8.0 in the us-central1 region with a small machine type.

Terminal

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

Expected Output

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/my-project/instances/my-mysql-instance].

→

--database-version - Specifies the database engine and version to use.

→

--tier - Defines the machine type for the instance.

→

--region - Sets the geographic location for the instance.

This command creates a new Cloud SQL instance using PostgreSQL version 14 in the us-central1 region with a small machine type.

Terminal

gcloud sql instances create my-postgres-instance --database-version=POSTGRES_14 --tier=db-f1-micro --region=us-central1

Expected Output

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/my-project/instances/my-postgres-instance].

→

--database-version - Specifies the database engine and version to use.

→

--tier - Defines the machine type for the instance.

→

--region - Sets the geographic location for the instance.

This command creates a new Cloud SQL instance using SQL Server 2019 Standard edition in the us-central1 region with a small machine type.

Terminal

gcloud sql instances create my-sqlserver-instance --database-version=SQLSERVER_2019_STANDARD --tier=db-f1-micro --region=us-central1

Expected Output

Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/my-project/instances/my-sqlserver-instance].

→

--database-version - Specifies the database engine and version to use.

→

--tier - Defines the machine type for the instance.

→

--region - Sets the geographic location for the instance.

This command lists all Cloud SQL instances in your project to verify the created databases.

Terminal

gcloud sql instances list

Expected Output

NAME                 DATABASE_VERSION       REGION        GCE_ZONE           STATUS

my-mysql-instance    MYSQL_8_0              us-central1  us-central1-a      RUNNABLE

my-postgres-instance POSTGRES_14            us-central1  us-central1-a      RUNNABLE

my-sqlserver-instance SQLSERVER_2019_STANDARD us-central1  us-central1-a      RUNNABLE

Key Concept

If you remember nothing else, remember: Cloud SQL supports MySQL, PostgreSQL, and SQL Server engines, each created by specifying the database version during instance creation.

Common Mistakes

Using an unsupported database version string in the create command.

The command fails because Cloud SQL only accepts specific version names for each engine.

Use exact supported version strings like MYSQL_8_0, POSTGRES_14, or SQLSERVER_2019_STANDARD.

Not specifying the region flag when creating the instance.

The instance may be created in a default region that is far from your users, causing latency.

Always specify the --region flag to place the instance close to your app or users.

Trying to create multiple instances with the same name.

Cloud SQL requires unique instance names; duplicate names cause errors.

Use unique instance names for each database instance.

Summary

Create Cloud SQL instances by specifying the database engine and version with the --database-version flag.

Use the --region flag to choose where your database runs for better performance.

List your instances to check their status and confirm creation.

Practice

1. Which database engines are supported by Google Cloud SQL?

easy

A. SQLite, Cassandra, and MariaDB

B. Oracle, MongoDB, and Redis

C. Firebase, Bigtable, and Spanner

D. MySQL, PostgreSQL, and SQL Server

Solution

1. Step 1: Recall supported engines in Cloud SQL

   Google Cloud SQL supports three main database engines: MySQL, PostgreSQL, and SQL Server.

2. Step 2: Compare options with supported engines

   Only MySQL, PostgreSQL, and SQL Server lists these three engines correctly; others list unsupported or different services.

3. Final Answer:

   MySQL, PostgreSQL, and SQL Server -> Option D

4. Quick Check:

   Cloud SQL engines = MySQL, PostgreSQL, SQL Server [OK]

Hint: Remember Cloud SQL supports only these three engines [OK]

Common Mistakes:

• Confusing Cloud SQL with other GCP database services
• Including unsupported engines like Oracle or MongoDB
• Mixing NoSQL and SQL engines

2. Which of the following is the correct way to specify the database engine when creating a Cloud SQL instance using gcloud CLI?

easy

A. gcloud sql instances create my-instance --database-version=MYSQL_8_0

B. gcloud sql create instance my-instance --engine=PostgreSQL

C. gcloud sql instances new my-instance --db-engine=SQLSERVER_2019

D. gcloud sql instance create my-instance --version=MySQL8

Solution

1. Step 1: Identify correct gcloud command syntax

   The correct command uses 'gcloud sql instances create' and the flag '--database-version' with uppercase engine and version format.

2. Step 2: Match options with correct syntax

   gcloud sql instances create my-instance --database-version=MYSQL_8_0 matches the correct syntax: 'gcloud sql instances create my-instance --database-version=MYSQL_8_0'. Others use incorrect flags or commands.

3. Final Answer:

   gcloud sql instances create my-instance --database-version=MYSQL_8_0 -> Option A

4. Quick Check:

   Correct gcloud syntax = gcloud sql instances create my-instance --database-version=MYSQL_8_0 [OK]

Hint: Use '--database-version' flag with uppercase engine name [OK]

Common Mistakes:

• Using wrong command verbs like 'create instance' instead of 'instances create'
• Using incorrect flags like '--engine' or '--db-engine'
• Incorrect engine version format or casing

3. Given this snippet to create a Cloud SQL instance with PostgreSQL engine:

gcloud sql instances create pg-instance --database-version=POSTGRES_14 --tier=db-f1-micro --region=us-central1

What will be the engine version of the created instance?

medium

A. SQL Server 2019

B. MySQL 8.0

C. PostgreSQL 14

D. PostgreSQL 9.6

Solution

1. Step 1: Analyze the '--database-version' flag

   The flag '--database-version=POSTGRES_14' specifies PostgreSQL version 14.

2. Step 2: Confirm engine and version from command

   The instance will use PostgreSQL engine version 14 as indicated.

3. Final Answer:

   PostgreSQL 14 -> Option C

4. Quick Check:

   Database version flag = POSTGRES_14 means PostgreSQL 14 [OK]

Hint: Look at '--database-version' value for engine and version [OK]

Common Mistakes:

• Assuming default engine if not read carefully
• Confusing PostgreSQL versions with MySQL or SQL Server
• Ignoring case sensitivity in version strings

4. You tried to create a Cloud SQL instance with this command:

gcloud sql instances create sqlserver-instance --database-version=SQLSERVER_2017

But it failed. What is the likely reason?

medium

A. Missing required region flag

B. SQL Server 2017 is not supported by Cloud SQL

C. Incorrect command syntax for instance creation

D. SQL Server requires a different CLI tool

Solution

1. Step 1: Check required flags for Cloud SQL instance creation

   The --region flag (and --tier) is required; both are missing here.

2. Step 2: Validate other aspects

   SQL Server 2017 is supported via SQLSERVER_2017, command syntax is correct, CLI tool is correct.

3. Final Answer:

   Missing required region flag -> Option A

4. Quick Check:

   Missing --region causes failure [OK]

Hint: Always include --region (and --tier) when creating Cloud SQL instances [OK]

Common Mistakes:

• Assuming SQL Server 2017 is unsupported
• Thinking region flag is optional
• Believing a different CLI tool is needed

5. You need a Cloud SQL instance for an app that requires strong Windows authentication support and integration with Microsoft tools. Which engine should you choose and why?

hard

A. MySQL, because it is the most popular and easy to use

B. SQL Server, because it supports Windows authentication and Microsoft integration

C. Any engine, because Cloud SQL handles authentication uniformly

D. PostgreSQL, because it supports advanced JSON features

Solution

1. Step 1: Identify app requirements

   The app needs strong Windows authentication and Microsoft tool integration.

2. Step 2: Match engine capabilities

   SQL Server supports Windows authentication and integrates well with Microsoft tools, unlike MySQL or PostgreSQL.

3. Step 3: Confirm Cloud SQL engine choice

   Choosing SQL Server engine meets the app's specific needs best.

4. Final Answer:

   SQL Server, because it supports Windows authentication and Microsoft integration -> Option B

5. Quick Check:

   Windows auth needs SQL Server engine [OK]

Hint: Choose SQL Server for Windows authentication needs [OK]

Common Mistakes:

• Picking MySQL or PostgreSQL ignoring Windows auth needs
• Assuming Cloud SQL abstracts all authentication differences
• Not considering app-specific integration requirements