trouble backing up large mysql database

Solution 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.

Solution 2:

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.

Solution 3:

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