MySQL Backups with Subversion
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:
- Unmanned, regular backups.
- Easy recovery in case of emergency.
The end result: sql-backup.sh. 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?
- Unmanned, regular backups? The SQL dump and Subversion checkins are completed automatically by crond every morning.
- 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.
- 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