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

freebsd-questions@freebsd.org mailing list
To unsubscribe, send any mail to "[EMAIL PROTECTED]"

Reply via email to