mysqldump or mysqlhotcopy to backup large MySQL database?

Question

I have some NAS storage to do backups to with my dedicated hosting provider. I setup automatic daily backups with WHM to backup the databases and accounts. The server is hosting one site. When it does the backup, it does a mysql dump, and essentially takes the site down for the whole time it is doing the backup because nobody can connect to the database while the dump is happening. The site is usually down for about 30 seconds to a minute (this may not seem like much, but for this kind of site it is a real problem).

Is there a better way to do the backup so that this won't happen (would mysqlhotcopy or Maatkit be better?), whether it be a different method of backing up to NAS, or just not using NAS at all and using some other method.

Answer 1

mysqlhotcopy is faster than mysqldump but doesn't do InnoDB.

To do backups, I use replication and do dumps on the slave. You get 0 downtime and no load spike during the backup process.

Answer 2

You wouldn't need another server, just put another install of MySQL in a sandbox on the same machine. Load may spike a little but you won't get any of the locking issues.