Best way to back up mysql database
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
> 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,
>> 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
> 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)
> 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
More information about the freebsd-questions