If you want to keep your site/application live, you can dump mysql tables online like so:
mysqldump --single-transaction --quick -hhostname -uuser -ppassword database > /path/to/destination-sqlbackup.sql
–compress = as it says, enabled compression on the connection to the mysql server. This will always speed up bulk loads 10x vs. using uncompressed connections.
–single transaction = Issue a BEGIN SQL statement before dumping data from server
–quick = This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
-p password = password
Importing your dump is pretty easy too:
Inserting your backup back to the Mysql server is simple too:
cat destination-sqlbackup.sql | mysql -hhostname -uuser -ppassword database
enabling compression by default via
add compress to your config like so each section needs it to enable default:
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.
#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"