mysqldump: Got error: 23: "Out of resources when opening
發表於 : 2016-05-23 23:01:38
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.
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.