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.

A scheduled backup using mysqldump on one of our databases is causing Too many connections. The database is of both InnoDB and MyISAM tables with size of around 500Mb. The Too many connections appears for about 2-3 minutes

We understand that mysqldump locks the tables and causes all other queries and connections to pile up and jam the mysql server.

We need frequent backups and we cannot afford server downtime or putting websites in maintenance mode while doing it. Our websites are global and traffic is high all the time so its hard to find a moment for backups.

How can we avoid downtime during backups?
Is there maybe a way to use mysqldump in way that it will not lock all tables at the same time?
Is there an alternative to backing up with mysqldump?

share|improve this question
    
2-3 minutes for 500MB sounds like you are dumping directly over the network. You might get a shorter lock time if you dump to your local disk(s) and then transfer over the network after the tables are unlocked. You might also get worse lock times if you are using slow disks or the contention causes lots of little delays. @faker's suggestion of XtraBackup will only lock for the MyISAM tables which will also help. –  Ladadadada Jun 13 '12 at 13:07
    
And XtraBackup will only get a write lock on MyISAM tables instead of a read lock like mysqldump uses (IIRC) –  faker Jun 13 '12 at 13:18
add comment

4 Answers

up vote 2 down vote accepted

At first, try to backup table by table or DB by DB - do not lock everything.

Increase max_connections, try to use connection limit otherwise: http://dev.mysql.com/doc/refman/5.5/en/user-resources.html

I'm not sure it will take too much of memory for increased max_connections.

If you are using MyISAM - there is no other way for backup. With InnoDB or XTraDB - no problems. http://www.innodb.com/doc/hot_backup/manual.html

share|improve this answer
    
Xtrabackup, mysqlhotcopy, MySQL Enterprise Backup and file system snapshots are all possible while using MyISAM –  faker Jun 13 '12 at 16:10
    
Thank you, GioMac, we did some experiments with max_connections and it seems its a good solution although maybe just a temporary one. DB by Db and Xtrabackup are our next candidates –  vbachev Jun 14 '12 at 10:12
add comment

1) Setup a second server, replicate your DB to it, backup from there
This might not be a good solution if you have lots of changes on your master server, since replication on the slave is single threaded.

2) Use http://www.percona.com/doc/percona-xtrabackup/

Or as always, consult the official documentation which outlines other alternatives:
http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

share|improve this answer
1  
+1 for replication –  m0ntassar Jun 13 '12 at 12:56
    
Thank you, faker! We will research and test xtrabackup as you suggested. A second server would not give us the best results since the data is very dynamic and changes often. –  vbachev Jun 14 '12 at 10:06
add comment

Is there an alternative to backing up with mysqldump?

How do you plan on using them? If you want quick and cheap incremental backups, I'd just use rsync (or rsnapshot which uses rsync to keep multiple concurrent copies without occupying too much extra disk space) to backup the /var/lib/mysql (or wherever the datafiles may be).

I am assuming you won't really use them to replicate/parse or otherwise interpret, but just in case your server(s) fail you want to do (essentially) a full-disk restore. This also makes such a restore incredibly fast (but rigid, you restore everything or nothing really) since its a standard copy operation. At best, you would use this as often as you want, with another backup mechanism (say a mysqldump at a off-peak time) to ensure you're never without an updated parsable dump.

share|improve this answer
3  
If MySQL is running while you rsync the files, you might end up with a corrupted backup –  faker Jun 13 '12 at 11:49
    
A snapshot-aware filesystem is a much better way to manage this sort of backup. You can lock all tables, create the snapshot and unlock in a few seconds. –  Ladadadada Jun 13 '12 at 13:00
add comment

Your mix of myisam and innodb-tables complicates matters somewhat. If you can convert the myisam-tables to innodb-tables you could run mysqldump inside a transaction. This way you would get a consistent backup without the need for a table/database lock.

Problems may be:

  • Performance: Innodb could be slower than myisam for you application
  • Features: If you use features only present with myisam
share|improve this answer
add comment

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.