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
check this forum thread for solutions:
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.
Check /etc/my.cnf. Under the [mysqld] section, if you have a open_files_limit variable, increase it, eg:
then restart mysqld.
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%';