Forum Moderators: open

Message Too Old, No Replies

Restart mysql automatically from CRON

         

Anyango

8:42 am on Jan 8, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hey Folks

Under high loads MYSQL is crashing away on some of my servers. can we do something like a cron job which will run every 5 minutes to see if mysql is stopped, if it is stopped it will restart mysql simple as that.

Not an expert in CGI or shell scripts anyway.

Cheers

physics

5:50 pm on Jan 9, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I have a job similar to this on my server. Basically you just need to write (or have one written) a Perl, PHP or shell or whatever script.
In your case I would say what you should do is try to connect to the database from within the script.
So to do this in PHP (which I'll use since you don't have to install any modules):

<?php
if (! $dbh = mysql_connect('localhost','your_db_user_name','your db pass') ){
mail('webmaster@example.com','MySQL Restarted',mysql_error());
exec('/etc/rc.d/init.d/mysqld restart');
}
?>

Call this check_restart_db.php

The code tries to connect to mysql, if there is an error then it emails it to the admin and restarts mysqld.

To have it run every 5 minutes add a line to cron like


*/5 * * * * /usr/bin/php /path/to/script/check_restart_db.php

You might also want to add a 'CHECK TABLE' command for your most important tables and repair and optimize them and email in the case that this happens.
While you're at it you should also set this up for the web server itself, i.e. if the home page can't be grabbed then restart the web server.
Finally, if you have a dedicated server or co-located server I recommend installing a watchdog timer of some kind. If the server becomes unresponsive the watchdog will restart it.

It's amazing how much time you can save by automating things like this.


Disclaimer: No warranty, your mileage may vary.