5

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.

10 Answers 10

1

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.

1

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.

1

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 ;-)

  • 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
0

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

0

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.

0

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 :) )

0

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

-q avoids locking the tables during the dump

  • 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
0

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.

-1

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

and perhaps extending your virtual memory...

-2

make the owner contact the hosting company for a quick backup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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