MySQL Crashing

Learn to solve issues with MySQL crashing occasionally on your VPS. Follow step by step tutorial to learn and apply.

If you are having issues with MySQL crashing occasionally (or frequently) on your VPS, the most likely reason is it is hitting the open files limit imposed by MySQL default settings.

The default open files limit is 1024. This is fine for a few websites, but if you are hosting many websites this should be increased.

1. Login as root via SSH

2. Type: pico /etc/security/limits.conf

Add/Modify the lines so they read:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

Save: CTRL O

Quit: CTRL X

3. Type: pico /etc/security/limits.d/90-nproc.conf

Add/Modify the lines so they read:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240
root soft nproc unlimited

Save: CTRL O

Quit: CTRL X

4. Type: ulimit -Hn 1024000

5. Type: pico /etc/my.cnf

Insert the following:

open_files_limit = 1024000

If you have sections [mysqld] and [mysqld_safe] put the line under both headings.

Save: CTRL O

Quit: CTRL X

6. Type: service mysql restart