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
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to "[EMAIL PROTECTED]"
