From owner-freebsd-questions@FreeBSD.ORG Tue Sep 30 22:57:36 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id E2A811065690 for ; Tue, 30 Sep 2008 22:57:36 +0000 (UTC) (envelope-from jdc@koitsu.dyndns.org) Received: from QMTA07.westchester.pa.mail.comcast.net (qmta07.westchester.pa.mail.comcast.net [76.96.62.64]) by mx1.freebsd.org (Postfix) with ESMTP id 8DE388FC12 for ; Tue, 30 Sep 2008 22:57:36 +0000 (UTC) (envelope-from jdc@koitsu.dyndns.org) Received: from OMTA01.westchester.pa.mail.comcast.net ([76.96.62.11]) by QMTA07.westchester.pa.mail.comcast.net with comcast id M4s71a0070EZKEL57As5kd; Tue, 30 Sep 2008 22:52:05 +0000 Received: from koitsu.dyndns.org ([67.180.253.227]) by OMTA01.westchester.pa.mail.comcast.net with comcast id MAs31a00M4v8bD73MAs4GZ; Tue, 30 Sep 2008 22:52:04 +0000 X-Authority-Analysis: v=1.0 c=1 a=QycZ5dHgAAAA:8 a=u1ErsWmT-NYEc_M5wpAA:9 a=8OBotIaEgJTryJt9z4AA:7 a=BojIchca3uHKxjVmKzQ4g8PGzx4A:4 a=EoioJ0NPDVgA:10 a=LY0hPdMaydYA:10 Received: by icarus.home.lan (Postfix, from userid 1000) id 91206C941A; Tue, 30 Sep 2008 15:52:03 -0700 (PDT) Date: Tue, 30 Sep 2008 15:52:03 -0700 From: Jeremy Chadwick To: John Almberg Message-ID: <20080930225203.GB58065@icarus.home.lan> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> User-Agent: Mutt/1.5.18 (2008-05-17) Cc: freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database 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: Tue, 30 Sep 2008 22:57:37 -0000 On Tue, Sep 30, 2008 at 06:18:35PM -0400, John Almberg wrote: > I just had a huge scare today... One of the websites on my server uses a > large Mysql database. Somehow, one of the tables got corrupted today. Do you know if the table corruption was a result of 1) a MySQL bug (and there are many), 2) filesystem corruption, or 3) disk "bit rot"? Did you repair the table using myisamchk (assuming it's a MyISAM table), or was the corruption in InnoDB? > I have been blithely backing up mysql with a simple cron script that ran > mysqldump every night. Simple, reliable, and I've never needed it. > > Today, when I realized the database was corrupted, I scrambled for my > backup, and realized that if I hadn't caught the problem today, tomorrow > my backup would have been overwritten, and I would have been... well, not > a happy camper. Others have recommended good solutions to you -- improve your cronjob to handle "rotations" of those mysqldumps, so that you have 1-2 weeks worth of data, that way you can sleep easier if you don't notice the problem for a day or two. There are programs out there (usually in ports) which can help you with this task. Also, just for the record: the fact you're doing a mysqldump is good. It's better than just blindly copying the database files using cp or rsync (there's no locking done in that case so you could risk backing up the tables in the middle of an INSERT); and the cp/rsync method won't work reliably if you're using InnoDB. -- | Jeremy Chadwick jdc at parodius.com | | Parodius Networking http://www.parodius.com/ | | UNIX Systems Administrator Mountain View, CA, USA | | Making life hard for others since 1977. PGP: 4BD6C0CB |