MySQL Backups with Subversion

Annika Backstrom
in misc, on 2 September 2004. It is tagged and #Scripting.

My uncle's website was the first site I had near-total administrative control over. ("Near" meaning I didn't have control over the hardware itself.) Part of administration is keeping regular backups. I chose a daily tarball of a mysqldump, archived locally and scp'd to a remote machine using a passworded private/public key set. (I leave ssh-agent running and connect to the same instance in each shell and script.)

That's all well and good, but there's room for improvement. Having recently installed Subversion, I finally sat down to version-control my backups. The goals:

  1. Unmanned, regular backups.
  2. Easy recovery in case of emergency.
  3. Simplicity.

The end result: This is rough code, poorly documented, an intial revision so I can see some of the quirks and drawbacks. How did I do on my goals?

  1. Unmanned, regular backups? The SQL dump and Subversion checkins are completed automatically by crond every morning.
  2. Easy recovery in case of emergency? A simple checkout of the sql-backup trunk, and I've got a series of .sql files ready for importing.
  3. Simplicity? I don't have to dig through a filesystem tree for the most recent tarball, I just checkout the tree or update my local copy.

The data is version controlled, so I can recall a database snapshot from any date. Subversion only transmits the differences between revisions, so the network load is much reduced. The script is flexible enough that installation on a new machine is nearly effortless. The only deficiency so far: deleted databases remain in the repository. At some point I will tweak the script to svn rm .sql files whose databases have been dropped.