Page 1 of 1

Auto Restart Mysql

Posted: Fri Jun 08, 2007 1:25 pm
by John Cartwright
We'll we currently have a server that is running quite a few processes, and sometimes the mysql load will shoot through the roof. How would one go about creating a cron to auto restart mysql server?

I know this could be done using php and having it send a shell command, although that would involve giving php permission that it shouldn't have. Thoughts?

Posted: Fri Jun 08, 2007 1:55 pm
by bdlang
Should be simple enough, create a shell script and add the script to cron, e.g.

Code: Select all

## set some variables ##
MYSQL_USER=mysql
MYSQLD=/usr/local/mysql/bin/mysqld_safe
MYSQL_PID=/var/run/mysql.pid

## test for the mysqld_safe binary ##
if [ -x $MYSQLD ]; then
  ## test for the PID file, see if MySQL is running ##
  if [ -r $MYSQL_PID ]; then 
    kill `cat $MYSQL_PID | head -1`
    sleep 5
  fi

  ## start the server
  $MYSQLD --user=$MYSQL_USER --pid-file=$MYSQL_PID &
fi
Pretty basic script, change the paths to suit and the options as needed. Set the executable bit and try it.

Set the script to run via cron at a one hour interval:

Code: Select all

0 * * * *    exec /path/to/mysql_restart.sh

Posted: Fri Jun 08, 2007 1:59 pm
by John Cartwright
Thanks for the response!

Wouldn't that only check to see if the process exists?

Posted: Fri Jun 08, 2007 2:27 pm
by bdlang
No. The script a) tests for mysqld_safe, b) tests for the pid file and if found c) reads the PID and kills the process. Then it starts MySQL based on your options.

Re: Auto Restart Mysql

Posted: Fri Jun 22, 2007 1:11 pm
by ev0l
Jcart wrote:We'll we currently have a server that is running quite a few processes, and sometimes the mysql load will shoot through the roof. How would one go about creating a cron to auto restart mysql server?
Forgive me for asking but why would you want to do that ?

Do you know what is causing the MySQL load to "shoot through the roof". 'SHOW FULL PROCESSLIST' will show you what procesess is being ofensive and you can kill it with 'KILL PID' where PID is the process's id shown from 'SHOW FULL PROCESSLIST'.

MySQL is incredibly stable even under heavy loads. Sounds to me you have a query that is causing some problems and needs to be optimized.

Posted: Fri Jun 22, 2007 2:22 pm
by John Cartwright
Well I currently manage one of our servers that has about 4 high traffic websites, collecting about 2-3 million rows of data per day and performing a lot of statistical and data porting processes. I know exactly where the problem lies, although we have more important things to do than this fix problem at the moment. In the near future we will spread the load across several servers, including a dedicated mysql server and eventually rewrite the application causing the problem, however for the time being this is the most viable solution.