Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces

If you get the following error while creating a backup:

Error while backing up database USER_NAME
mysqldump error output: mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `NAME_accesslog`': Out of resources when opening file '/tmp/#sql_390d_0.MYD' (Errcode: 24 - Too many open files) (23)

150707  0:15:49 [ERROR] Error in accept: Too many open files

 

Some solutions:

1) Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh

ulimit -HSn 1024
ulimit -HSn 32768
ulimit -HSn 1024000

which should force it to try and set it as high as it can go.  If one of the value is too high, delete that line, or lower the value.

2)  Check /etc/my.cnf.  Under the [mysqld] section, if you have a open_files_limit variable, increase it, eg:

open_files_limit=32768

then restart mysqld.

3) Confirm in mysql itself by checking the open_files_limit variable to ensure it's increased, by running the SQL query:

show global variables like 'open%';

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Mysql Command To get all email addresses From Database

If you have access to all tables (i.e. as root), you can dump all tables and grep email...