This and other servers I control run MySQL for various purposes, and like the good administrator I am, I regularly store back-ups for disaster recovery.

MySQL 4, the version I run, has a utility called mysqldump which will take a snapshot of a database's structure and table contents. It does this while the database is running, allowing for minimal intrusion; it does lock the tables as they're being backed up, but this merely stalls actions upon them, not stopping actions.

The files are simple SQL scripts that will recreate the tables and insert the data. Some effort has been made in the script to provide indexes, too. I've only had to use the scripts when upgrading the server and the upgrade doesn't take the old database files and convert them neatly.

I've had for some time a daily script that runs to perform the dump on all of the databases on the server. Each database's script is then compressed and renamed to keep a few days' copies in case something was corrupted or data was lost between copies. Then the compressed file is encrypted and e-mailed to a GMail account I set up for just this purpose (thanks for the off-site space, GMail!).

After letting this go for quite some time, I have a rather full mailbox. I visit it every few days or so and delete the really old ones (keeping the last hundred messages. It got me thinking, though, that there are some databases that don't change very often. A casual observation showed me that some of the databases hadn't changed in the week's worth of copies I maintained on the server. It seemed a waste of my server's time and space, and the e-mail server's time and space, and my GMail time and space.

I altered my script to dump the data, then compare that with the previous dump. If they're the same, it discards the new dump and moves on. If they differ, it rolls the archives, encrypts the new dump and e-mails that one. I also upped the script to run twice a day; for the databases with no changes, this won't mean anything, and for those changing frequently, this will mean better resolution to catch "oops" data changes.