From owner-freebsd-questions@FreeBSD.ORG Fri Jan 13 14:50:06 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 280DF16A41F for ; Fri, 13 Jan 2006 14:50:06 +0000 (GMT) (envelope-from njt@ayvali.org) Received: from sanddollar.geekisp.com (sanddollar.geekisp.com [204.89.131.97]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2882143D46 for ; Fri, 13 Jan 2006 14:50:05 +0000 (GMT) (envelope-from njt@ayvali.org) Received: (qmail 22552 invoked by uid 1003); 13 Jan 2006 14:50:04 -0000 Received: from clam.int.geekisp.com (HELO clam.geekisp.com) (192.168.4.38) by mail.geekisp.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 13 Jan 2006 14:50:04 -0000 Received: from clam.geekisp.com (njt@localhost.geekisp.com [127.0.0.1]) by clam.geekisp.com (8.13.3/8.12.11) with ESMTP id k0DEo4Yu018095; Fri, 13 Jan 2006 09:50:04 -0500 (EST) Received: (from njt@localhost) by clam.geekisp.com (8.13.3/8.13.3/Submit) id k0DEo3TY007615; Fri, 13 Jan 2006 09:50:03 -0500 (EST) X-Authentication-Warning: clam.geekisp.com: njt set sender to njt@ayvali.org using -f Date: Fri, 13 Jan 2006 09:50:03 -0500 From: "N.J. Thomas" To: Hans Nieser Message-ID: <20060113145003.GJ2451@ayvali.org> References: <43C6E55A.8020500@xs4all.nl> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43C6E55A.8020500@xs4all.nl> User-Agent: Mutt/1.5.9i Cc: freebsd-questions@freebsd.org Subject: mysql backups (was 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 Reply-To: freebsd-questions@freebsd.org List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 13 Jan 2006 14:50:06 -0000 * Hans Nieser [2006-01-13 00:25:14 +0100]: > 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. Yes. While MySQL is writing to the the database, it will put the files on the disk in an inconsistent state. If you happen to copy those files while they are in that state, MySQL will see a corrupted database. > 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? Here are some methods that people use that I am aware of: - Turn off the MySQL db the entire time you are backing up. No new software/hardware needed, but you incur db downtime. - Use replication: have a slave that is a copy of the master, whenever you want to back up, break the replication for a little while, copy the slave, and then resume the replication. No downtime, but you will need another box for this, so you have the cost of new hardware. - Use OS snapshotting. On Linux systems with LVM, it is possible to take an exact "snapshot" of the filesystem at any point in time without too much disk usage (assuming the lifetime that the snapshot exists is relatively short). So what you do in this case is write a script that tells MySQL to write lock the entire database and flush the cache, this takes a second or two and will bring the db files on disk to a consistent state. You then take a snapshot of the filesystem, and immediately resume MySQL when you have done that. Now, you just backup off of the snapshot, destroying it when you are done. No new hardware, but you will need a snapshot capable filesystem and write the script to do this. I'm not sure exactly what snapshotting features FreeBSD has...perhaps someone else could fill in this information. Also, you will have a short period of downtime during which the MySQL db is write locked. This may or may not be acceptable for you. hth, Thomas -- N.J. Thomas njt@ayvali.org Etiamsi occiderit me, in ipso sperabo