apocryph.org Notes to my future self

13Nov/050

Backing up MySql, Drupal, Subversion using qdb.pl

I’ve been diligently backing up bonzo using qdb.pl, my homegrown backup script. bonzo is a very important machine, as he stores my entire digital image archive, my Subversion repository, MySql databases containing Drupal and Gallery2 data, and 24-hour evaluation copies of large software packages. However, I realized somewhat belatedly that my MySql databases were not being backed up as part of my backup regime.

I’ve decided on a two-layer backup approach to backing up MySql:

  • First, I’ll backup the MySql ISAM files in /var/db/mysql. Since I only use MyISAM and not the more advanced MySql storage options, this will be sufficient most of the time. However, in the off chance that a backup runs in the middle of a MySql transaction, the ISAM files may be in an inconsistent state. Which leads me to the second layer…
  • Second, I have also opted to dump the contents of my MySql databases using the mysqldump utility, which simply generates SQL which will recreate a given MySQL database, complete with DDL and DML.

Since I’ll be running mysqldump as a cron job, I’ll have to specify the password on the command line. To ensure transactional integrity, I’ll want to lock all the tables in all the databases during the backup. So, the command line will be:

mysqldump -uroot -pguess --all-databases --lock-all-tables > /usr/local/backup/mysqldump.sql

mysqldump.sql then contains all the DROP, CREATE, INSERT, etc statements required to reconstruct all of my databases.

I feel much better now…

Next I need to backup my Subversion repository. I use fsfs, which is the odd name the svn team uses to describe their use of the standard file system as a means to store the repository contents. Earlier svn versions defaulted to Berkeley DB, which was all fine and good until you ran out of disk space or corrupted your bdb files, at which point you were screwed to the wall.

With fsfs, it’s much more likely that a simple file system backup will safely preserve the repository. However, as bulk backups are hardly atmomic, there is still the outside chance that something will go amiss and yield a broken backup. Thus, I’ve opted for a two-layer approach similar to that described above for MySQL: backup the raw files, and dump the contents to a text file for backup as well.

With svn, the repository is in /usr/local/apocryph_svn, and is already backed up. So I just need to implement the repository dump.

As it turns out, the svnadmin tool has a dump option, which works just like mysqldump. So, this command line would do the trick:

svnadmin dump /usr/local/apocryph_svn > /usr/local/backup/svndump

I’ve put this and the MySQL backup steps in before_backup.sh, which the backup.sh script calls before invoking qdb.pl.

Too easy.

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.