From owner-freebsd-questions@FreeBSD.ORG Thu Jan 12 23:37:58 2006 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id F00F016A41F for ; Thu, 12 Jan 2006 23:37:58 +0000 (GMT) (envelope-from freebsd@philip.pjkh.com) Received: from wolf.pjkh.com (wolf.pjkh.com [66.228.196.74]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4C35343D49 for ; Thu, 12 Jan 2006 23:37:58 +0000 (GMT) (envelope-from freebsd@philip.pjkh.com) Received: from localhost (localhost [127.0.0.1]) by wolf.pjkh.com (Postfix) with ESMTP id DF35E172D3; Thu, 12 Jan 2006 15:37:53 -0800 (PST) Received: from wolf.pjkh.com ([127.0.0.1]) by localhost (wolf.pjkh.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 54003-06; Thu, 12 Jan 2006 15:37:53 -0800 (PST) Received: by wolf.pjkh.com (Postfix, from userid 1000) id 69097172A7; Thu, 12 Jan 2006 15:37:53 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by wolf.pjkh.com (Postfix) with ESMTP id 647F9172A5; Thu, 12 Jan 2006 15:37:53 -0800 (PST) Date: Thu, 12 Jan 2006 15:37:53 -0800 (PST) From: Philip Hallstrom To: Hans Nieser In-Reply-To: <43C6E55A.8020500@xs4all.nl> Message-ID: <20060112153427.S54310@wolf.pjkh.com> References: <43C6E55A.8020500@xs4all.nl> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: amavisd-new at pjkh.com Cc: freebsd-questions@freebsd.org Subject: Re: Remote backups, reading from and writing to the same file X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 12 Jan 2006 23:37:59 -0000 > For a while I have been doing remote backups from my little server at home > (which hosts some personal websites and also serves as my testing webserver) > by tarring everything I wanted to be backed up and piping it to another > machine on my network with nc(1), for example: > > On the recieving machine: nc -l 10000 > backup-`date +%Y-%m-%d`.tar.gz > > On my server: tar -c -z --exclude /mnt* -f - / | nc -w 5 -o aphax 10000 > > (Some excludes for tar(1) are left out for simplicity's sake) > > Among the things being backed up are my mysql database tables. This made me > wonder wether the backup could possibly get borked when mysql writes to any > of the mysql tables while tar is reading from them. > > Do I really have to use MySQL's tools to do a proper SQL dump or stop MySQL > (and any other services that may write to files included in my backup) before > doing a backup? Do any of the more involved remote-backup solutions have ways > of working around this? Or is it simply not possible to write to a file while > it is being read? The short answer is yes. The medium answer is I would if I were you :-) The long answer (at least to the extent I know it) is... You might be able to take a snapshot of the filesystem mysql's files are on and back those up as they'd be consistent to themselves. But everything I've read about backing up a database suggests that doing a proper backup is the way to go. If you really don't want to do that you might also be able to use one of the various LOCK commands in Mysql to block all writes until you've copied them over. But really a mysqldump ... | gzip > file should result in a very very small file. And you could pipe that over the network (or even start mysqldump on your backup machine) if you didn't want the temp file issue. You might also consider rsync. That would only copy files that have changed. Might be handy if bandwidth is an issue. You can set it up to keep backup copies of files that have changed as well. And it can run over ssh. -philip