MySql - ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files)

MySql - ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files


We are getting above error while showing database in mysql console.


mysql> show databases;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files)
mysql>

This means already max file are open as per limit of Mysql. Let's check all open file using command lsof -u mysql


[root@14 ~]# lsof -u mysql

mysqld    54214 mysql 1017u   REG              253,0     36184  82904684 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000829W.MYD
mysqld    54214 mysql 1018u   REG              253,0      4096  82888505 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000PXHT.MYI
mysqld    54214 mysql 1019u   REG              253,0     23732  82888506 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000PXHT.MYD
mysqld    54214 mysql 1020u   REG              253,0      8192  82888508 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000RTU1.MYI
mysqld    54214 mysql 1021u   REG              253,0     63076  82888509 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000RTU1.MYD
mysqld    54214 mysql 1022u   REG              253,0      7168  82888511 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000S6EC.MYI
mysqld    54214 mysql 1023u   REG              253,0     51392  82904672 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000S6EC.MYD

In above query i have pasted some output, Let's count total number of open file using command.   lsof -u mysql | wc -l

[root@14 ~]# lsof -u mysql | wc -l
1075
[root@14 ~]#

In above example you can see total 1075 files are open, this is the reason we are getting error.
Let's fix it.

There are two option to fix this issue -

1- Restart MySql service.

2- Append open-files-limit = 2048 this line in my.cnf file and restart mysql.

Method 1-

[root@14 ~]# systemctl restart mysqld
[root@14 ~]# lsof -u mysql | wc -l
78
[root@14 ~]#

After MySql Restart only 78 files are open, Now i can see all the Databases and continue my work.

[root@14 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        |
| cand                      |
| dsa_AWR                   |
| dsa_Analytics             |
| dsa_CallMeasurement       |
| dsa_FormSubmit            |
| dsa_reporting             |
| mysql                     |
+---------------------------+
28 rows in set (0.00 sec)

mysql>

Method 2 - Add 
open-files-limit = 2048 end of my.cnf file.


[root@14 ~]# vi /etc/my.cnf

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
open-files-limit = 2048

Save and Exit from file.
 
[root@14 ~]# systemctl restart mysqld


That's ALL

!!!! Cheers !!!!