Mysql Backup
 

Hi
I read some documents on HowtoForge site about mysql backup. recently I read 2 documents, 1 and 2 . In these documents was mentioned to consistent backup,one solution is "read lock and flush cache" and another is "replicated mysql ". Does not "mysqldump" built-in use "read lock and flush cache" and I have to apply manually ??
That means, I can use mysqldump only mysql is stop, is it true?

Thanks in advance

Check the documentation on mysql.com -- http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html. Table locking is an option when you call mysqldump. There is a lot of control there.

If you are trying to set up a rigorous and automated backup scheme, you might also take a look at ZRM for MySQL -- http://www.mysql.com/products/backup/zrm.html. You can get it open source, or you can get it with full service and support contracts.

I heard a programmer for Drizzle in a podcast. He said many enterprises use migrate LVM snapshots instead, so they are able to use the same procedure to backup the database as they do for everything else.

Snapshots are a good thing and improve your odds, but they do not guarantee consistency, especially with databases. You're basically operating with your fingers crossed. I've been using fssnap with ufsdump on Solaris for years. Even with fssnap, Sun's official line is that you should take a system down to single user to do backups. I've actually been bitten by this once or twice over the years.

A main advantage of snapshots is that you can reduce the time window that something has to be shut down or locked (quiesce is the official term). You can shut down an application, or lock and flush tables, do the snapshot, bring them back online, and then backup the snapshot. So, instead of being shutdown for the entire backup, you are shutdown only for the few seconds it takes to do the snapshot.

You should still use proper procedures for databases.