mysqldump: Got error: 23: "Out of resources when opening

[yehlu]

https://blog.hostonnet.com/mysql-out-of ... ening-file

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

1
2
3
# 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

1
2
3
# 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

1
vi /etc/my.cnf
Added open_files_limit under [mysqld_safe] section.

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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
# 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

1
2
service mysql stop
service mysql start
Related Kernel Parameters

To see maximum allowed file handlers on your system, run

1
cat /proc/sys/fs/file-max
To see current usage, run

1
cat /proc/sys/fs/file-nr
This will show

Total allocated file handles.
Number of currently used file handles.
Maximum file handles that can be allocated.
To change value of file-max, edit /etc/sysctl.conf

1
vi /etc/sysctl.conf
Add

1
fs.file-max=65536
Now run

1
sysctl -a
This will apply the changes to Kernel with out rebooting.