Best way to back up mysql database

[Jeremy Chadwick]

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?

> 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.

-- 
| Jeremy Chadwick                                jdc at parodius.com |
| Parodius Networking                       http://www.parodius.com/ |
| UNIX Systems Administrator                  Mountain View, CA, USA |
| Making life hard for others since 1977.              PGP: 4BD6C0CB |