Cron to test database is running and restart if necessary?

Hi all,

I've got a project for a client that is presenting me some interesting challenges. I'm using Google Maps Heatmap layer to show a ton of location data from a coordinate database of 5M+ points, filtered based on user criteria.

Sometimes, of course, there are just a ton of rows post filtering (100k-200k results), and I'm finding this is shutting down the mysql connection, causing the site to die. Easy enough to 'fix', I can just sudo service mysql restart, but it's a pain because I'm waiting for an error & fixing reactively.

I've since changed the way the database querying is working, limiting my row results to 40k in a loop & making as many calls as necessary, which has helped tremendously, but there are still some shutdowns depending on the user filtering.

So is there any place within Laravel or in my Forge setup that I can run a check to say something like (pseudocode, obviously):

if (mysql is not running)
{
    sudo service start mysql;
}

and have that run every minute? And if so, how would I go about doing that?

Thanks in advance for any help!

Shawn

willvincent

10 years ago

Best Answer

Level 54

SSH into your server.

Create this file:

mysql_ping.sh:

#!/bin/bash

#Checking whether MySQL is alive or not

if mysqladmin ping | grep -q "alive"; then
  echo "OK"
else
  service start mysql
fi

Then, do the following:

sudo -s
chown root mysql_ping.sh
chmod +x mysql_ping.sh
mv mysql_ping.sh /usr/sbin
crontab -e

In crontab, set that to run every second:

* * * * * * /usr/sbin/mysql_ping.sh > /dev/null 2>&1

save, exit, and exit out of the root user account. you can also log out of the server at this point as well.

This should live in root's crontab entry, so that it is actually able to start/stop the service. Not gonna be much help if it's sitting there waiting on someone to enter a sudo password.. ;)

So. What this will all accomplish is, every second the mysql server will be pinged, if it's alive nothing happens, if it does not respond, the service will be started.

shawnyv

10 years ago

Level 14

Thanks willvincent, much appreciated!

I'm getting an error indicating it requires a password - is it safe for me to include my database password in the mysql_ping.sh, or is there another way I should be handling this?

Thanks again for all your help thus far!

Shawn

willvincent

10 years ago

Level 54

if you set it's perms to 700, that should be secure enough since nobody but root would be able to even read the file then.

chmod 700 mysql_ping.sh

Please or to participate in this conversation.