This was a really strange one and might be peculiar to debian servers. I was trying to change a couple of mysql default settings, so after editing the config file, I restarted mysql on a webserver and it didn’t come back up again. There were a bunch of errors in the /var/log/mysql.log saying “errno: 24 – Too many open files”. And in fact mysql was so busy pumping these errors out, that it didn’t have time to do any databasing on the files it was able to open.
After a bit of experimentation (read ‘panic’), I discovered that rebooting the server started mysql up fine, but doing a ‘service mysql restart’ produced the file errors, so I rebooted and left it running while I investigated. So the problem lies with the way the system and mysql agree on the number of open files they need. In the system its set in /etc/security/limits.conf , whereas in mysql its set in /etc/mysql/my.cnf via the open_files_limit directive. NB, your install of mysql might use a different config file. To check what your current open_files_limit value is with mysqladmin …
> mysqladmin variables | grep open open_files_limit | 1024
OK well there’s the problem, 1024 is the default value, and a quick count of the files in mysql’s data directory gave me about 2500 files. The simplest solution seemed to be to set it higher in my.cnf so I put in ‘open_files_limit = 16000’, but after a ‘service mysql restart’ and even a reboot, I still got the errors.
Now I needed to attack the /etc/security/limits.conf file. I put these lines towards the end.
* hard nofile 32000 * soft nofile 32000
After a reboot, dammit, mysql is still using open_files_limit of 1024. But ulimit -a shows that the system is now using the 32,000 value.
So another search around the internet … and we find a bunch of posts telling us that /etc/security/limits.conf gets loaded AFTER mysql starts at boot, so the correct limits for mysql are not set. The solution is, for debian at least, is to find /etc/pam.d/common-auth, and add this line to the bottom.
session required pam_limits.so
And that’s it, after a ‘service mysql restart‘ or a reboot, mysql’s open_files value is now set to 32000, which is the value in limits.conf rather than my.cnf, which it still chooses to ignore.