Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : How to fix mysql backup issue

[schooglepets]

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. Is there a better way to do the backup so that this won't happen, whether it be a different method of backing up to NAS, or just not using NAS at all and using some other method.

[Lightwave]

One good way is using MySQL replication. Setup a master and slave server, then do database dumps against the slave.

[schooglepets]

Could you tell me again in english please?

[UNIXy]

The reason no one can connect to the database while the dump is in progress has to do with mysqldump taking large locks on your tables. So any transaction trying to take place sleeps until the lock is released. You could tell mysqldump to not acquire locks using the following flags / switches:

mysqldump -u root --skip-add-locks --skip-lock-tables dbname

The above will not cause new connections to stall but it will not generate consistent backups due to concurrency issues. The clean way to take backups without messing with locking is to do what Lightwave suggested. Replicate your current database (master) to another mysql instance (slave). Then point your backup scripts at the slave database instead of the master. The slave database can be locked and it won't affect the availability of your current websites / database.

Here's an intro on replication: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Best