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 !!!!