Date: Tue, 30 Sep 2008 19:19:40 -0400 From: John Almberg <jalmberg@identry.com> To: Jeremy Chadwick <koitsu@FreeBSD.org> Cc: freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database Message-ID: <BE6B6D87-D81A-4B14-B793-6625E4CF576C@identry.com> In-Reply-To: <20080930225203.GB58065@icarus.home.lan> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> <20080930225203.GB58065@icarus.home.lan>
next in thread | previous in thread | raw e-mail | index | archive | help
> 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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?BE6B6D87-D81A-4B14-B793-6625E4CF576C>