Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a website running on a MySQL database backend which is backed up daily by the following command:

mysqldump -h server.com -uuser -ppass database > /home/directory/backup`date +"%Y%m%d"`.sql

I've noticed that as the database has got larger and the backup takes longer (maybe a few minutes) the site does not function correctly during the backup process.

Pages which require database loaded during the backup hang during the backup and fail to load until the backup is complete. Often they don't seem to timeout, until after the backup is complete with a MySql timeout 30 seconds error - even though more than 30 seconds has passed.

I guess the mysqldump command is locking access to some part of the database and preventing client connections from the webserver, but can't find any more information about this.

I'm running mysql 5 on the server and the tables are in ISAM format.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

You could use replication to replicate the database to another then backup the replica instead.

share|improve this answer
    
Thanks for the suggestion topdog - I may end up doing this if there's no other alternative, but would also be interested to know the reasoning behind what's currently happening and if there are any other easier solutions to implement. –  Loftx Aug 22 '10 at 9:14
2  
Its the locks on the tables that causes the issue –  topdog Aug 22 '10 at 11:18

For MyISAM tables you could also use mysqlhotcopy. However, this will not remove your problem, since this also uses table locks. Therefore, I would also recommend to look at replication.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.