Re: [gentoo-user] Backups

[Steve Dommett <steve@st4vs.net>]

On Sunday 30 September 2007, Neil Bothwick wrote:
> Other data in /var/lib. For example, any databases kept in /var/lib/mysql.

Rather than backup MySQL's or Postgres' binary storage I prefer to use the 
relevant tool (mysqldump, pgdump[all]) to backup the database 
to /root/backups/ just prior to running the real backup job.  This has the 
advantage that you can generally restore onto any version of the database 
server, rather than the specific version that was running when the backup was 
taken.

I'm not 100% sure but I thought this method of dumping the database is the 
only way to guarantee consistency of the snapshot without having to stop the 
database server for the duration of the backup.  From what I understand when 
backing up a live database Postgres guarantees the consistency using MVCC but 
MySQL may be a little less careful if INSERTs/UPDATEs happen during the 
backup.  For keeping a hot-spare failover database server closely in sync 
with realtime changes I'd suggest Slony1.

Unless the database contains blobs I prefer to dump as plain text INSERTs and 
compress using gzip patched with --rsyncable support which leads me to...

After backing up the database I /always/ backup to a remote machine,  
sometimes two, using rdiff-backup.  It does point-in-time recovery and 
supports ACLs and xattr.  The backup is not in some odd file format; it's 
stored in a subfolder of your filesystem just like any other tree of files so 
it is instantly available.  A subfolder called rdiff-backup-data stores all 
the rollbacks and also all the acl/xattr/other metadata so the destination 
filesystem doesn't need to support them. There was even a GSoC project to 
provide a FUSE interface to mount the backup folder as it appeared at the 
time of some previous backup.  This is all /very/ nice for when used in 
combination with Linux-VServer.  With a little thought about XIDs you can 
start a backed-up VServer on your development box ;-)

This is the essence of what I run in a cron job on the machine needing to be 
backed up:
#!/bin/bash

#/root/backup_mysql.sh
#/root/backup_postgres.sh

mount /boot > /dev/null 2> /dev/null

rdiff-backup --force --remove-older-than 90d  \
 backups@backuphost::~/backups/myhostname/

echo "--------------------"

time rdiff-backup   \
 -v2 --print-statistics \
  --exclude /mnt \
  --exclude /media \
  --exclude /dev \
  --exclude /proc \
  --exclude /tmp \
  --exclude /var/tmp \
  --exclude /var/cache/squid/ \
  --exclude /var/cache/http-replicator/ \
  --exclude /var/lib/mysql/ \
  --exclude /var/lib/postgresql/data/base/ \
  --exclude /var/lib/postgresql/data/global/ \
  --exclude /var/lib/postgresql/data/pg_clog/ \
  --exclude /var/lib/postgresql/data/pg_subtrans/ \
  --exclude /var/lib/postgresql/data/pg_tblspc/ \
  --exclude /var/lib/postgresql/data/pg_xlog/ \
  --exclude /sys/ \
  --exclude /usr/portage/ \
  --exclude /usr/portage/distfiles \
 / backups@backuphost::~/backups/myhostname/

umount /boot > /dev/null 2> /dev/null
-- 
gentoo-user@gentoo.org mailing list