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 \ [EMAIL PROTECTED]::~/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 \ / [EMAIL PROTECTED]::~/backups/myhostname/ umount /boot > /dev/null 2> /dev/null -- [EMAIL PROTECTED] mailing list
