From owner-freebsd-questions@FreeBSD.ORG Fri Jan 13 00:16:08 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 511EF16A41F for ; Fri, 13 Jan 2006 00:16:08 +0000 (GMT) (envelope-from siegfried.pietralla@eds.com) Received: from symler2.mail.eds.com (symler2.mail.eds.com [207.169.189.142]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1F06C43D4C for ; Fri, 13 Jan 2006 00:16:06 +0000 (GMT) (envelope-from siegfried.pietralla@eds.com) Received: from symlir1.mail.eds.com (symlir1.mail.eds.com [192.85.216.71]) by symler2.mail.eds.com (8.13.4/8.12.10) with ESMTP id k0D0G2g7003639; Fri, 13 Jan 2006 11:16:03 +1100 Received: from symlir1.mail.eds.com (localhost [127.0.0.1]) by symlir1.mail.eds.com (8.13.4/8.12.10) with ESMTP id k0D0F42J003043; Fri, 13 Jan 2006 11:15:04 +1100 Received: from aubwm050.apac.corp.eds.com ([134.251.159.12]) by symlir1.mail.eds.com (8.13.4/8.12.10) with ESMTP id k0D0F4nj003038; Fri, 13 Jan 2006 11:15:04 +1100 Received: from aubwm010.apac.corp.eds.com ([134.251.166.96]) by aubwm050.apac.corp.eds.com with Microsoft SMTPSVC(6.0.3790.211); Fri, 13 Jan 2006 11:15:05 +1100 Received: from aumem201.apac.corp.eds.com ([134.251.130.70]) by aubwm010.apac.corp.eds.com with Microsoft SMTPSVC(6.0.3790.211); Fri, 13 Jan 2006 11:15:04 +1100 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Date: Fri, 13 Jan 2006 11:15:02 +1100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Remote backups, reading from and writing to the same file Thread-Index: AcYXz78lHmBN5rktR7OARU1Xt7rnFQAAZQKg From: "Pietralla, Siegfried P" To: "Hans Nieser" , X-OriginalArrivalTime: 13 Jan 2006 00:15:04.0271 (UTC) FILETIME=[66DB0DF0:01C617D6] Cc: 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: Fri, 13 Jan 2006 00:16:08 -0000 > -----Original Message----- > From: owner-freebsd-questions@freebsd.org [mailto:owner-freebsd-questions@freebsd.org] On Behalf Of Hans Nieser > Sent: Friday, 13 January 2006 10:25 AM > To: freebsd-questions@freebsd.org > Subject: Remote backups, reading from and writing to the same file >=20 > Hi list, >=20 > For a while I have been doing remote backups from my little server at home=20 > (which hosts some personal websites and also serves as my testing=20 > webserver) by tarring everything I wanted to be backed up and piping it to=20 > another machine on my network with nc(1), for example: >=20 > On the recieving machine: nc -l 10000 > backup-`date +%Y-%m-%d`.tar.gz >=20 > On my server: tar -c -z --exclude /mnt* -f - / | nc -w 5 -o aphax 10000 >=20 > (Some excludes for tar(1) are left out for simplicity's sake) >=20 > Among the things being backed up are my mysql database tables. This made=20 > me wonder wether the backup could possibly get borked when mysql writes to=20 > any of the mysql tables while tar is reading from them. >=20 > Do I really have to use MySQL's tools to do a proper SQL dump or stop=20 > MySQL (and any other services that may write to files included in my=20 > backup) before doing a backup? Do any of the more involved remote-backup=20 > solutions have ways of working around this? Or is it simply not possible=20 > to write to a file while it is being read? hi hans, just some points to note in a general unix / db way ( not freebsd or mysql specific ) : tar ( and unix in general ) doesn't care if you're writing while you're reading, so the tar will 'work' - though I believe tar may get confused if you create new files while tar is running.=20 just copying a 'live' db file will generally not give you a recoverable backup. e.g. with 'oracle' you need to put files into backup mode before copying them which lets oracle maintain extra recovery information. with 'ingres' you use the ingres backup command which records before images along with the database files ( and incidentally prevents table creation ( i.e. new files ) while it backs up the db - usually with tar! ). so you really need to find out what 'hot' backup is supported by your db and run accordingly. or just shut down your db's before running your backups. a common way to manage database backups ( if you have the space ) is to use normal db backup methods to backup to local disk, then use the remote backup to backup the db backup ( and exclude the live db files since they're probably not usable anyway ). the number one rule for ALL backup regimes is - TEST YOUR RECOVERY METHOD - preferably regularly. a real recovery is not the time to find out what the shortcomings in your backup methodology are. regards, siegfried.