3 of 4
added 48 characters in body
Jason C
  • 1.3k
  • 3
  • 12
  • 27

Removing duplicate files but ignoring an embedded timestamp in the file (*or* MySQL backups only when data changed)

I have a cron job creating gzipped MySQL backups every 2 hours (with mysqldump).

I'd like to not store duplicate backups when nothing has changed.

Right now I am testing the following script, which just scans the whole directory for duplicates (I can live with the increase in time as the directory grows, for now):


for a in *.sql.gz ; do
    gunzip -c $a | sed -e 's/^-- Dump completed.*//g' | gzip -c -9 > temp.gz
    touch -r $a temp.gz
    mv temp.gz $a

fdupes . -dN

This script works. The problem is, as the script implies, mysqldump inserts a comment line containing the timestamp in each file, and so unless I remove it, there will never be duplicates:

-- Dump completed on 2014-07-12 10:00:01

I want to find a solution that:

  • Does not involve modifying the dump files.
  • Leaves the timestamp in the file (there's the filesystem timestamp and files also include timestamp in name but I'd rather leave it in the contents of the file).
  • Includes comments in the dump files (i.e. not mysqldump --skip-comments).
  • Does not reinvent any wheels.

Is there some way I can do this? Ideas I've considered but haven't implemented:

  • Somehow doing all of this with a temporary copy of all the files in another directory and building up a list of files to delete in the original directory?
  • Writing a new tool similar to fdupes that gunzips files and ignores MySQL comment lines before hashing, leaving the original files untouched?

I am sure I am not the first person who has wanted to coalesce duplicate database backups, and I believe I'm falling into an XY trap here.

How can I either a) remove duplicate files in this directory with the above requirements or b) the XY part, effectively back up a MySQL database frequently only if the database has changed?

Jason C
  • 1.3k
  • 3
  • 12
  • 27