When i take backup of a MySQL Database with large number of tables, i get following error

# mysqldump freeforums > freeforums.sql
mysqldump: Got error: 23: Out of resources when opening file './freeforums/casualcity_topics_watch.MYD' (Errcode: 24) when using LOCK TABLES
# 

When restoring this Database, i got similar error

# mysql freeforums < freeforums.sql
ERROR 23 (HY000) at line 25: Out of resources when opening file './freeforums/' (Errcode: 24 - Too many open files)
#

Solution

This was caused due to open_files_limit limit.

To fix, i edit /etc/my.cnf

vi /etc/my.cnf

Added open_files_limit under [mysqld_safe] section.

open_files_limit = 50000

Here is the content of /etc/my.cnf file with open_files_limit added

# cat  /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 50000

# 

Now you need to restart MySQL

service mysql stop
service mysql start

Related Kernel Parameters

To see maximum allowed file handlers on your system, run

cat /proc/sys/fs/file-max

To see current usage, run

cat /proc/sys/fs/file-nr

This will show

1. Total allocated file handles.
2. Number of currently used file handles.
3. Maximum file handles that can be allocated.

To change value of file-max, edit /etc/sysctl.conf

vi /etc/sysctl.conf

Add

fs.file-max=65536

Now run

sysctl -a

This will apply the changes to Kernel with out rebooting.