Best way to back up mysql database

John Almberg jalmberg at identry.com
Tue Sep 30 23:19:43 UTC 2008


> 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



More information about the freebsd-questions mailing list