Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a wordpress MU database with something like 10,000+ tables for various user's blogs. I need to upgrade wordpress MU to newest version, but want to backup the DB before hand.

PHPMyAdmin fails to even load the page when i click export. Ive tried going into the server (windows) and using dos command line:

mysqldump -u USERNAME -p PASSWORD> BACKUP.sql

but it hangs for a minute and gives me the error:

error 23: out of resources when opinging file '.\USERNAME\wp_1037_links.MYD' (Errorcode: 24) when using LOCK Tables

What am i doing wrong, or should i be doing? Is PHPMyAdmin right for something this size? Is there a better way of doing this than the two methods i tried?

**Note that this is not my site, so any suggestions as to the setup of the DB ill have to run by the owner. Im just here for WP related crap, this is kind of out of scope for what i was brought on to do.

share|improve this question

migrated from Feb 26 '10 at 15:57

This question came from our site for professional and enthusiast programmers.

10 Answers 10

Try the following: mysqldump --lock-all-table -u USERNAME -p PASSWORD > BACKUP.sql

Be aware that if this is live, it may stop the sites functioning.

share|improve this answer
Yeah it is live, any way to do this without disrupting the site? – mrpatg Feb 26 '10 at 9:45
Unsure what version of mysql you are using, but take a look at mysqlhotcopy, – AliGibbs Feb 26 '10 at 9:54

The error indicates you are running out of resources on the server; the reason is probably due to locking: mysqldump implicitly locks tables while reading them. If there is a lot of activity on the database, MySQL will need to keep a copy of the older data available for the mysqldump transaction; for large enough tables, this can be a real issue.

If you're not too concerned with data consistency, you can use --skip-lock-tables (-q actually won't work, it only prevents output buffering. This may be useful, but will not solve your problem) and --single-transaction.

share|improve this answer

I had the same problem and I figured its the open_files_limit variable for my.cnf

just update your my.cnf


[& restart]

the value should be 2 * number of tables you have in you DB. If that doesn't work, try 3* number of tables. If that also doesn't , don't go incrementing, as that's not the problem in your case. Search for solution again ;-)

share|improve this answer
To test if this is the issue compare the following values: "show global status like 'open_files';" and "show variables like 'open_files_limit';" If the value open_files is close to the value open_files_limit, you need to increase the open_files_limit. – racyclist Jun 8 '10 at 15:56

you could try mysql administrator, but it seem starnge that mysqldump throws such an error.

share|improve this answer

error 23: out of resources when opening file '.\USERNAME\wp_1037_links.MYD'

This sounds more like a general RAM or hard disk problem on your server. Is that a virtual machine? Check your hard disk quota first. It could be that mySQL is running out of space even trying to access the table - in that case even a compressed backup won't help.

share|improve this answer

The error you are experiencing is:
shell>perror 23
Error code 23: Too many open files in system
shell>perror 24
Error code 24: Too many open files
More info here:,7639,7639#msg-7639

But as you probably already know mysqldump is not the only way to perform a mysql backup and it's not the best way either.
I am assuming you're using Innodb as the storage engine, so I would recommend using innodb's 'hot' backup utility : ibbackup.
( it can also perform backup for myisam but it has no advantages over mysqlhotcopy)
But for real Mysql database protection you could recommend activating the binary logs on the mysql server (they say it only decreases performance by 1%) so you could do point-in-time restores as shown here:

If none of the alternatives above are an option for you why not:
2. considering your provider's environment you could do filesystem snapshots.
3. or (I know I'm pushing it) Mysql replication slaves :)
4. if using myisam just flush tables with read lock and copy the files
5. Request upgrade of mysql server to 6.0 (when it reaches GA) and perform backups like this:
mysql> BACKUP DATABASE 'db-name' to 'c:\backups\whynotswitchtounix.bkp';
(notice the subliminal message :) )

share|improve this answer

mysqldump -q -u username -ppassword database > file.sql

-q avoids locking the tables during the dump

share|improve this answer
That's not actually correct: -q (short for --quick) tells the client not to buffer the response, which for large dumps is definitely recommended but has nothing to do with table locks. – Tomer Gabel Apr 13 '10 at 8:19

Indeed, InnoDB Hot Backup utility is a great tool, but you may prefer a free tool recently released by Percona: XtraBackup. It performs online backup of InnoDB and XtraDB tables and can create full and incremental backups.

share|improve this answer

try using gzip to zip your sql files for backup

and perhaps extending your virtual memory...

share|improve this answer

make the owner contact the hosting company for a quick backup

share|improve this answer

Your Answer


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.