Creating and managing on-demand and automatic backups

MySQL   |  PostgreSQL

Note: All data on an instance, including backups, is permanently lost when that instance is deleted. To preserve your data, export it to Cloud Storage before you delete it.

This page describes how to schedule and disable automatic backups, create and manage on-demand backups, and view backups for your Cloud SQL instance.

For more information about how backups work, see Overview of backups.

Creating an on-demand backup

To create an on-demand backup:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

2. Click the instance to open its Overview page.
3. Click the Backups tab.
4. Click Create backup.
5. On the Create backup page, add a description, if needed, and click Create.

gcloud

Create the backup:

gcloud sql backups create --async --instance [INSTANCE_NAME]

You can provide a description of the backup using the --description parameter.

cURL

Create the backup:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Length: 0' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns

Scheduling automated backups

To schedule automated backups for an instance:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

2. Select the instance for which you want to configure backups.
3. Click Edit.
4. In the Backups section, check Enable automated backups, and choose a backup window.
5. Click Save.

gcloud

1. Edit the instance to specify a backup start time:

   gcloud sql instances patch [INSTANCE_NAME] --backup-start-time [HH:MM]

   The `backup-start-time` parameter is specified in 24-hour time, in the UTC±00 time zone, and specifies the start of a 4-hour backup window. Backups can start any time during the backup window.
2. Confirm your changes:

   gcloud sql instances describe [INSTANCE_NAME]

   Under backupConfiguration you should see enabled: true and the time that you specified.

cURL

Patch the instance to schedule automated backups:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"settings" :
             {"backupConfiguration" : {"startTime": "[HH:MM]", "enabled": true}}}' \
     -X PATCH \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]

You can enable binary logging at the same time by setting binaryLogEnabled to true within the backupConfiguration object.

Disabling automated backups

To disable automated backups for an instance:

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

2. Select the instance for which you want to disable backups.
3. Click Edit.
4. In the Backups section, uncheck Enable daily automated backups.
5. Click Save.

gcloud

Edit the instance to disable backups:

gcloud sql instances patch [INSTANCE_NAME] --no-backup

cURL

Patch the instance to disable backups:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 

     --header 'Content-Type: application/json' 

     --data '{"settings" : {"backupConfiguration" : {"enabled": false, "binaryLogEnabled": false}}}' 

     -X PATCH 

     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]

Setting and viewing a custom location for backups

Currently, you can only use the API to set custom locations for backups.

To set the backup location for automated backups, use the instances settings.backupConfiguration.location parameter:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"name":"[INSTANCE_NAME]", "region":"[REGION]",
              "settings": {"backupConfiguration":
               {"binaryLogEnabled":true, "enabled":true, "location": "[BACKUP_REGION]"}}}' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances

You can also run the previous command with PATCH to update the backup location on an existing instance.

To set the backup location for on-demand backups, use the backupRuns location parameter:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"location":  "[PATH]"}' \
     -X POST \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns

To see the locations of the backups taken for an instance, use backupRuns:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     -X GET \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns

In the previous examples:

• REGION is the region for the original instance.
• BACKUP_REGION is the region where you want Cloud SQL to store the backup. For a complete list of valid region values, see Instance locations.

Viewing backups

The GCP Console does not show skipped or failed backups in the backup history. To see skipped backups, use the gcloud command line tool or the API.

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

2. Click the instance to open its Overview page.
3. At the bottom right corner, the list of Recent backups is displayed.

gcloud

1. List the backups of the instance:

   gcloud sql backups list --instance [INSTANCE_NAME]
   

   You can filter and control the results using the standard list parameters. For a complete list, see the gcloud sql backups list command reference page.

2. To list the details of one backup, use the ID from the output of the backups list command.

   gcloud sql backups describe [BACKUP_ID] --instance [INSTANCE_NAME]
   

cURL

List the backups for the instance:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     -X GET \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns

You can also use the APIs Explorer on the BackupRuns:list page to send the REST API request.

Deleting a backup

You can delete automated and on-demand backups.

Deleting a backup might not free up as much space as the size of the backup. This is because backups are incremental, so deleting an older backup might transfer some of its contents to a more recent backup to preserve the integrity of the newer backup.

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

   Go to the Cloud SQL Instances page

2. Click the instance to open its Overview page.
3. Click the Backups tab.
   The list of existing backups is displayed.
4. Click more actions for the backup you want to delete.
5. Select Delete.
6. In the Delete backup window, type Delete into the text box and click Delete.

cURL

1. List the backups to obtain the ID for the backup you want to delete:

   List the backups for the instance:

   gcloud auth application-default login
   ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
   curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 
   
        -X GET 
   
        https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns
   

2. Delete the backup:

   curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 
   
        -X DELETE 
   
        https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns/[BACKUP_ID]
   

What's next

• Learn more about backing up your data.

• Learn more about restoring data.

• See how to restore your data from a backup.