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 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 stackoverflow.com Feb 26 '10 at 15:57

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

add comment

10 Answers

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? –  Patrick Feb 26 '10 at 9:45
    
Unsure what version of mysql you are using, but take a look at mysqlhotcopy, dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html –  AliGibbs Feb 26 '10 at 9:54
add comment

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
add comment

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

share|improve this answer
add comment

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
add comment

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:
forums.mysql.com/read.php?35,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: dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html.

If none of the alternatives above are an option for you why not:
1. SELECT INTO ... OUTFILE
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
add comment

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

-q avoids locking the tables during the dump

share|improve this answer
1  
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
add comment

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
add comment

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

just update your my.cnf

open_files_limit=20000

[& 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
add comment

try using gzip to zip your sql files for backup http://www.gzip.org/

and perhaps extending your virtual memory...

share|improve this answer
add comment

make the owner contact the hosting company for a quick backup

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.