I’ve been diligently backing up
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
MyISAMand 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
mysqldumputility, 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
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.
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