Google Cloud SQL Backup removal

I take backups using the Google Cloud API for Google Cloud SQL, and never implemented a trim process because we want longer term backups.

I use the following bash one-liners to clean our backups up when needed. The following command will list all backups existing

grep -v = inverted grep, find all lines without DELETED

tail -n +2 = Show all lines but the header row

cut -f -d ' ' = cut the first field, fields separated by spaces

> outfile = redirect output and save to file named 'outfile'

gcloud beta sql backups list --instance $instancename | grep -v DELETED | tail -n +2 | cut -f1 -d ' ' > outfile

vim outfile and delete the backup lines you want to keep.

cat outfile | while read line; do `/opt/google-cloud-sdk/bin/gcloud beta sql backups delete $line --instance development --quiet --async`; done

This saves me hours of cleanup time, I'd rather delete them every couple weeks to a month for my use case.

bash script:

just enter instancename, and number of backups to keep. There is a 30 second sleep built in to confirm you want to delete, just ctrl-c before 30 seconds to cancel. It's ready to go as a cronscript.


#logfile trim cron scripts for cleaning google cloud SQL backups.
#this file can be run daily or weekly, it just keeps the bill down.

# Assign variables
backups="+40" # snapshots to keep, Approx. 30 days for me with auto snaps turned on as well.  
outfile="/tmp/sql-trim-output-file.tmp" # temp file for process use.  
sql_instance="hotlinesng" # this is the master instance name on cloud SQL.  
gcloud_location="/opt/google-cloud-sdk/bin/gcloud" # gcloud executable location.

# gcloud needs to be installed and in a path accessable by the user. This user or account must have the appropriate permissions.
# gcloud beta sql backups list (lists backups) of $sql_instance | skips deleted backups because you cant delete them twice
# the tail command skips $dayskip amount | the cut command cuts the first column (f1) with a file delimited by spaces.
# it then save the gcloud clensed output to a file we can ingest into the next step.
#$gcloud_location beta sql backups list --instance $sql_instance | grep -v DELETED | tail -n +$dayskip | cut -f1 -d " " > $outfile
$gcloud_location beta sql backups list --instance $sql_instance | grep -v DELETED | grep -v UNKNOWN_STATUS | grep -v OVERDUE | tail -n $backups > $outfile

echo "deleting the following snapshots in 30 seconds:"  
cat $outfile && sleep 30

# we cat the $outfile into a while loop, that loops over the file created by first step line by line until we're done with the file. |
# we use the quiet option to not ask you each line, and async to not wait for each command to completely erase.
cat $outfile | cut -f1 -d " " | while read line; do `/opt/google-cloud-sdk/bin/gcloud beta sql backups delete $line --instance $sql_instance --quiet --async`; done

# delete file in
rm -f $outfile && echo "" && echo "temp file deleted successfully"