[lug] mysqldump versus snapshot versus..

[dio2002 at indra.com]

> The mysqldump command is what I use, however, locking the tables on a
> production machine is a no-no, so there are two ways around this.  If you
> have only one machine and this is a small operation, change the mysql
> table type to innodb (can be done with an ALTER TABLE command) - gives you
> larger tables, no locking issues, but you lose the ability to do a quick
> SELECT COUNT(*) operation.

thanks, i wasn't aware of that.

> The other way is to use replication to
> replicate the DB to a "read-only" machine where you can perform long
> operations on.

although it could be a performance consideration and not necessarily all
that practical for a variety of reasons, can you replicate dbs on the SAME
machine.  this would be purely for automated backups without having to
bring the prod database down.

> Or, you can just use replication as your backup - this allows for
> "instantaneous" restores.  If your DB dies (HD failure or something), just

unless you happen to drop your databases by accident.  in which case you
would lose all data in the replicated db too. :-)  you should still be
backing up these dbs traditionally.

> I think that if you use LVM snapshots, you'll also have to lock the tables
> before and unlock after the snapshot, but it may be so fast, that
> locking/unlocking may not be an issue for you.

you nailed it.  LVM DOES require table lock on mysql backup.  the benefit
is that it apparently is a lot faster which only keeps your prod db down
for a very short time.

fwiw, a lot of the info i googled and read on backup of mysql db's NEVER
EVER mentions that you need to lock the db tables.  which means if a lot
of people are using these techniques then i guess there are a lot of
potentially inaccurate backup dump files out there?

i have also used phpmyadmin to mysqldump dbs as well and i don't believe
it locks tables either.  how many people are using that thing for backup? 
i guess potential for corruption only really matters if someone is
actually inserting or updating and not just selecting on the db at the
time of the write.

i'll read more on mysql page.  thanks for the info.