I have a new requirement to purge MySQL dump files that are older than 30 days. The files use a naming convention of "all-mysql-YYYYMMDD-HHMM.dump". The files are located on SAN mounted file system, so restoration is not an issue, but the drive space is limited unfortunately and fills up quickly so it requires frequent human intervention.

Example of files names

  • all-mysql-20130324-2330.dump
  • all-mysql-20130325-2330.dump
  • all-mysql-20130326-2330.dump

My first thought was to use "find" inside a batch script with -mtime +30, however, the modification times cannot be guaranteed and some of the older archives could evade the purge date :)

I created the following BASH script, but I was hoping there was there was a cleaner way to perform this operation.





# Create YYYYMMDD datestamp for Today - $ARCHIVE_WINDOW_DAYS
ARCHIVE_WINDOW_IN_SECS=$(echo "$(date +%s) - (${ARCHIVE_WINDOW_DAYS} * 86400)" | bc)

for backup_file in $FILE_PREFIX*
    # Trim prefix, time portion of date stamp, and file extension
    # from $backup_file to allow numeric comparison against YYYYMMDD

    if [ ${PURGE_BEFORE_DATE} -gt ${backup_trimmed} ]
        rm $backup_file

  • 3
    Looks perfectly adequate to me, and I can't see a simpler way of doing the date conversion than the way you actually took. :) – tink Mar 27 '13 at 19:39
  • @tink - Thank you. Can't help to think there was a one liner solution for this. I was more concerned with other maintainers who live more in JavaLand than BASHland. Maybe the only concern is the "Year 2038" problem then :) – T.P. Mar 27 '13 at 20:28
  • 2
    Isn't logrotate a cleaner solution? – ott-- Mar 27 '13 at 20:34
  • 2
    For things like these there should also be a safeguard (do not delete old backups when for some reason there are no new ones). – frostschutz Mar 27 '13 at 20:49
  • @ott - That could be an option if it works good in userland. Unfortunately, we (app engineers) are not permitted any root nor su privileges, so if any spits out to syslog or requires any other superuser privs, we will be in the dark. It's a bummer but it's the reigning policy :( – T.P. Mar 27 '13 at 20:56

Another way to delete all except the last 30 files:

rm $(ls -r | tail -n +31)

Or here is a shorter version of the script in the original post:

cd /opt/backup/mysql/dumps
d=$(date -r $(($(date +%s)-30*86400)) +%Y%m%d)
for f in all-mysql-*; do
    [[ ${f#all-mysql-} < $d ]] && rm $f
  • Option 1 would not take into account interim backups that could incur more than 30 files, but option 2 was what I was hoping to achieve (a more concise script). However, the posted script refactor did produce an error, so I changed the date assignment line to d=$(date -d @$(($(date +%s)-30*86400)) +%Y%m%d) and it worked identically to the script in the original post. – T.P. Mar 28 '13 at 18:55
  • date -d didn't work on OS X, but -r also seems to have a different meaning in the gnu date. – Lri Mar 28 '13 at 19:14
  • Yup. Another GNU vs BSD gotcha :) – T.P. Mar 28 '13 at 19:24

If you want to delete all except for the last 30 files:

rm `echo " " all-mysql-*.dump | sed -r -e 's/( [^ ]+){0,30}$//'`

That would fulfill your requirement provided that there is one backup per day, and the naming scheme stays the way it is (i.e. alphabetical order = chronological order, no spaces in filenames).

You specifically requested a one-liner in one of your comments. Personally I like to write things out more. This one liner is a bit on the dangerous side (if the sed fails, everything is deleted).

  • Currently, there could be multiple backups (dumps) per day to refresh QA and local environments. Maybe spelling it out and playing safe is the best approach. – T.P. Mar 27 '13 at 20:50
  • You could up it to 40 or 50 files then (however many you can afford storage space). If you had a separate filesystem for those backups, you could even stat the free space and rm only the first backup when not enough space is available. – frostschutz Mar 27 '13 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.