0

I have a script, backup.sh, that creates a backup of a MySQL database and then SCPs it to a remote server. This works great, and I can run this once per day using crontab with no problems.

However, I want to only store the last 30 days of backups, otherwise after a few months I will have hundreds of redundant SQL files. I don't really know where to start with this. The only method I can think of is to have 31 subfolders, one for each of the days of the month, and then replace each file each day. That's slightly less than ideal though, and feels a little dirty.

Below is the contents of my backup.sh file, with some key info obviously removed:

#!/bin/bash
today=`date +%Y-%m-%d.%H%M%S`

mysqldump -u rails -p******** rails > backups/backup-$today.sql
sshpass -p '********' scp backups/backup-$today.sql m***d@******.co.uk:custom_backups/backup-$today.sql
DutchUncle
  • 1,265
  • 8
  • 16
mikemike
  • 131
  • 4
  • 1
    IMHO when you run a script daily, your timestamp should not be more granular than the date. A response of 1199185687 seconds when you ask me my age might be technically correct, but not very useful. - In your script you might simply add a line to remove the `old=$(date --date="30 days ago" +%Y-%m-%d)` – HBruijn Feb 03 '15 at 09:54

3 Answers3

1

Can you ssh on remote machine? If yes, you can write script remover.sh 

#!/bin/bash
sshpass -p '********' ssh m***d@******.co.uk: "ls -t custom_backups/backup-*.sql | sed -n -e '31,1000 s#^#custom_backups/#p' | xargs rm -f"

How it works?

ls -t custom_backups/backup-*.sql

List all files sorted by time - oldest last

| sed -n -e '31,1000 s#^#custom_backups/#p'

sed add directory prefix (custom_backups/) and print (thanks to p switch) only lines (filenames) from range 31 to 1000.

| xargs rm -f

xargs removes listed files. -f switch guarantee there will be no errors if list is empty ie. you have 30 or less backup files.

Script remove oldest files only if you have on remote machine at least 30 backups

mefju
  • 153
  • 5
0

There's plenty of example scriptlets out there that show how to do this, just websearch for "rotate backups" examples.

Also, you probably have at least a template script on the very machine you are trying to do this: most distros come with log-rotating capabilities, you can find inspiration in those scripts too :-)

So look in the crontab to see if any log-rotating is mentioned there, or check the documentation for your OS.

DutchUncle
  • 1,265
  • 8
  • 16
0

I use two solutions for this problem:

  1. create one backup for each day, as mentioned above. Problem is - that you can't have here older backups.
  2. create backups in directory, and then - using script - i keep (for example):
    • backups from last 30 days
    • backups from every sunday not older than 1 year
    • backups from every 1 day of month
    • everything else - remove.
undefine
  • 1,046
  • 9
  • 21