A simple way to automate On-demand Backups in Cloud SQL

Cloud SQL offers two types of backups: on-demand and automated. While enabled automated backups occur once a day and cannot be scheduled for shorter intervals, you can create the on-demand ones at any time. This article will show you how to automate the on-demand backups to be created every X hours in the simplest way possible. For that, I will be using the Cloud Scheduler.

I: Create a Custom Role that will allow creating Cloud SQL backups

You need the following permission to create Cloud SQL backups:

• cloudsql.backupRuns.create

This permission should be granted to the service account that will be used by Cloud Scheduler as identity.

You could simply grant the service account one of the predefined roles, such as Cloud SQL Editor or Cloud SQL Admin, however, following the principle of least privilege, I encourage you to create a custom role instead. Here is how you can do it:

1. In Cloud Console, navigate to IAM & Admin -> Roles and click “CREATE ROLE”.
2. Fill in the required fields and add the `cloudsql.backupRuns.create` permission.

In the Cloud Console it will look like this:

II: Create a Service Account for your Cloud Scheduler job

Create a service account giving it the role from the previous step:

III: Create a Cloud Scheduler job

1. Define the job and its frequency. I have configured it to run every 6 hours:

2. Select HTTP as the target type and provide the following URL:

https://sqladmin.googleapis.com/sql/v1beta4/projects/{project}/instances/{instance}/backupRuns

Remember to replace {project} with your project ID, and {instance} with your Cloud SQL instance ID. Make sure that the selected HTTP method is POST.

3. For Auth header select the “Add OAuth token” option.

4. Specify the service account you have created in Step II.

5. Click “CREATE”.

Conclusion

That’s it! Now your Cloud SQL instance will have the backups created every X hours (in my particular example — every 6 hours), and you can modify the frequency easily.

Nevertheless, you should keep in mind the following:

On-demand backups are not automatically deleted the way automated backups are. They persist until you delete them or until their instance is deleted. Because they are not automatically deleted, on-demand backups can have a long-term effect on your billing charges if you do not delete them.

Reference: https://cloud.google.com/sql/docs/mysql/backup-recovery/backups#on-demand-backups

However, you can address this issue by configuring Object Lifecycle Management on the GCS bucket storing your backups.