Auto-restart mysql when it dies

I have a rackspace server that I have been renting to run my personal projects upon. Since I am cheap, it has 256Mb of RAM and honestly can't handle alot. Every once in a while, when there is a sharp uptick in traffic, the server decides to start killing processes and it seems that mysqld is a popular one for it to kill. I try to visit my site and am greeted with the message that there was an error establishing the database connection. Inspection of the logs reveals that mysqld was killed due to lack of memory.

Since I am still as poor as I was yesterday and don't want to upgrade my rackspace VM's RAM, is there a way I can tell it to automagically restart mysqld when it dies?

I have a thought to use something like crontab, but alas, I don't know exactly what to do there either. I guess I am product of the "Linux on your desktop" generation since I can do most things on my desktop and laptop (which run Linux almost exclusively), but still lack a lot of server administration skills for Linux.

The server runs CentOS 6.3

Solution 1:

This is not a clean solution, it would obviously be better to avoid the problem in the first place. Anyway, I am not sure how CentOS manages services but I think it uses service. If so, you can check if the mysql service is running with

/sbin/service mysql status

This command will exit successfully if mysql is running and return a non 0 exit status if i is not. You can therefore start the service if it is not running with this command:

/sbin/service mysql status || service mysql start

You can add this line to /etc/crontab to launch thes command every minute:

* * * * * /sbin/service mysql status || service mysql start

Solution 2:

This is a little disturbing.

mysqld is always restarted by mysqld_safe because there is an infinite loop in the bottom of mysqld_safe to check for abnormal shutdowns. If the error is too severe, not even mysqld_safe would not be able to restart mysqld on subsequent tries.

Given that situation that mysqld_safe is designed for, it may not be a good idea to force mysqld to start if mysqld_safe will reject it anyway.

You need to locate the error log in my.cnf it will be under

[mysqld]
log-error=log-filename

or

[mysqld_safe]
log-error=log-filename

Read the text file (probably by running tail -30 log-filename) and find the source of the mysqld processing shutting down.

Solution 3:

In a brute force attempt to keep things up and running on a low-memory VPS, I used a modification of terdom's answer to check and restart MySQL.

/sbin/service mysqld status || service mysqld restart

I needed to change mysql to mysqld to get it to work. Without it I would get the error, "ERROR! MySQL is running but PID file could not be found".

On my CentOS 7.2 system, /sbin/service redirects to /bin/systemctl status, so the following command is faster to execute.

/bin/systemctl status  mysqld.service || /bin/systemctl start  mysqld.service

I ended up adding the following line to the system's root crontab. It checks every minute if MySQL is running and redirect stdout to null. Starting the service won't output anything unless something goes wrong, so there's no need to add the null redirect on the last command.

* * * * * /bin/systemctl status mysqld.service > /dev/null || /bin/systemctl start  mysqld.service

The double pipe || means OR and will execute the 2nd command if the first command fails somehow. (Returns an exit code greater than zero.)

It's like saying, "Run the 1st command, or, if the 1st command fails somehow, run the 2nd command".

This is unlike the double ampersand && which is like saying, "Run the 1st command, and, only if the 1st command was successful, run the 2nd command".

Solution 4:

The following is from jonnyreeves.co.uk :

And the culprit is php-fpm! A quick google found another Wordpress customer suffering from similar symptoms; the advice was to tweak the php-fpm pool configuration (/etc/php-fpm.d/www.conf) and tweak the pm configuration. The main change was to move from pm = dynamic to pm = ondemand with a pm.max_children value of 5 (based on observing ~5% memory usage per worker). After changing the configuration I restarted all services and checked the memory usage.

service php-fpm restart
service nginx restart
service mariadb restart

After restarting the memory usage was dramatically lower.