From owner-freebsd-questions@FreeBSD.ORG Tue Sep 30 23:19:43 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 50A841065690 for ; Tue, 30 Sep 2008 23:19:43 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: from mx1.identry.com (on.identry.com [66.111.0.194]) by mx1.freebsd.org (Postfix) with ESMTP id 06D968FC12 for ; Tue, 30 Sep 2008 23:19:42 +0000 (UTC) (envelope-from jalmberg@identry.com) Received: (qmail 90762 invoked by uid 89); 30 Sep 2008 23:19:42 -0000 Received: from unknown (HELO ?192.168.1.110?) (jalmberg@75.127.142.66) by mx1.identry.com with ESMTPA; 30 Sep 2008 23:19:41 -0000 In-Reply-To: <20080930225203.GB58065@icarus.home.lan> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <20080930225203.GB58065@icarus.home.lan> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: John Almberg Date: Tue, 30 Sep 2008 19:19:40 -0400 To: Jeremy Chadwick X-Mailer: Apple Mail (2.752.3) 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 23:19:43 -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? 'Corrupted' is the wrong word. I believe it was a software error that destroyed a self-referential relationship within the table. The 'parent_id' field was altered incorrectly. So, it was not a MySQL error, per se. >> 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. Okay, so I've written a ruby script that will give me one month's worth of backups to a remote server. Each backup looks like 'all.mysql.12.txt', where the number is the day of the week. I'm using scp to copy the backup to a backup server, so I don't lose the backups if the whole server tanks. A month's worth of backups might be overkill, but I have plenty of room on the backup server. Whew... that added a few grey hairs to my collection. Time for a beer and a few slaps upside the head! Thanks to everyone who confirmed a script and mysqldump are an adequate solution. -- John