From mboxrd@z Thu Jan 1 00:00:00 1970 Received: from lists.gentoo.org ([140.105.134.102] helo=robin.gentoo.org) by finch.gentoo.org with esmtp (Exim 4.60) (envelope-from ) id 1Ic6nu-000246-PV for garchives@archives.gentoo.org; Sun, 30 Sep 2007 21:58:23 +0000 Received: from robin.gentoo.org (localhost [127.0.0.1]) by robin.gentoo.org (8.14.1/8.14.0) with SMTP id l8ULlhCj027917; Sun, 30 Sep 2007 21:47:43 GMT Received: from mail.st4vs.net (mail.st4vs.net [88.96.252.46]) by robin.gentoo.org (8.14.1/8.14.0) with ESMTP id l8ULeemD018890 for ; Sun, 30 Sep 2007 21:40:40 GMT Received: from localhost (localhost [127.0.0.1]) by mail.st4vs.net (Postfix) with ESMTP id 05DC5128CEA5 for ; Sun, 30 Sep 2007 22:40:40 +0100 (BST) X-Virus-Scanned: amavisd-new 2.4.1 (20060508) at st4vs.net Received: from mail.st4vs.net ([127.0.0.1]) by localhost (mail.st4vs.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id jwAS38y+vHLc for ; Sun, 30 Sep 2007 22:40:39 +0100 (BST) Received: from hotdog (unknown [88.96.252.45]) by mail.st4vs.net (Postfix) with ESMTP id A2695128CEA3 for ; Sun, 30 Sep 2007 22:40:39 +0100 (BST) From: Steve Dommett To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] Backups Date: Sun, 30 Sep 2007 22:40:37 +0100 User-Agent: KMail/1.9.7 References: <49bf44f10709291526s46c5c1ebrb34a7171215a3db2@mail.gmail.com> <49bf44f10709300836s790edbfbrac69e84bc36d1d66@mail.gmail.com> <20070930201504.572d8094@krikkit.digimed.co.uk> In-Reply-To: <20070930201504.572d8094@krikkit.digimed.co.uk> Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-6" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200709302240.38468.steve@st4vs.net> X-Archives-Salt: 7f012deb-c09b-4de5-bd52-ff10a630aedc X-Archives-Hash: c1010b1172bee5219815ab3f618df876 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