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.
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.
#!/bin/bash #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"