1

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.

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
2
  • 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, 2010 at 9:14
  • 2
    Its the locks on the tables that causes the issue
    – topdog
    Aug 22, 2010 at 11:18
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .