Can not start mysql server on Ubuntu 14.04

Question

when I visited my site I saw that there was no connection with mysql server. I logged in to my vps. I tried to restart mysql but then i saw this

root@vps138972:~# sudo /etc/init.d/mysql restart * Stopping MySQL database server mysqld [ OK ] * Starting MySQL database server mysqld
[ OK ] * Checking for tables which need an upgrade, are corrupt or were not closed cleanly.

root@vps138972:~# ERROR 23 (HY000) at line 1: Out of resources when opening file
'./admin_naturello/oc_profile_description.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_return.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_return_action.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_return_history.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_return_reason.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_return_status.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_review.MYD' (Errcode: 24) ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_revolution_slider.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_setting.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_stock_status.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_store.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_tax_class.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_tax_rate.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_tax_rate_to_customer_group.MYD' (Errcode: 24) ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_tax_rule.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_url_alias.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_user.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_user_group.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_voucher.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_voucher_history.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_voucher_theme.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_voucher_theme_description.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_weight_class.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_weight_class_description.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_zone.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_naturell
o/oc_zone_to_geo_zone.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_presta/c
atalogsearch_fulltext.MYD' (Errcode: 24)

ERROR 23 (HY000) at line 1: Out of resources when opening file './admin_presta/o
auth_nonce.MYD' (Errcode: 24)

Answer 1

MYSQL: Out of resources when opening file... (Errcode: 24)

The mysql error: Out of resources when opening file... (Errcode: 24) indicates that the number of files that msyql is permitted to open has been exceeded. This limit is controlled by the variable open_files_limit. You can read this in phpMyAdmin (or the MySQL command line utility) with the statement:

SHOW VARIABLES LIKE 'open%'

To set this variable to a higher number, edit the /etc/my.cnf file and add the lines:

[mysqld]
open_files_limit = 5000

Then be sure to restart mysql with: sudo /etc/init.d/mysql restart

Remember to use the server administrative account and sudo when you edit the file. The choice of editor is up to you. Now, showing the variable should show the number you choose.

Note that 5000 shown above is an example. A good rule of thumb is to take the current number of files and add 1000 to it. If this doesn't help, add some more. This number affects tha amount of memory that MySQL uses, so setting it to a very high number is not a good idea.

Sources: secure hens-teeth, secure vexxhost