Auto Restart Mysql

XML, Perl, Python, and other languages can be discussed here, even if it isn't PHP (We might forgive you).

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Auto Restart Mysql

Post 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?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Thanks for the response!

Wouldn't that only check to see if the process exists?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
ev0l
Forum Commoner
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Re: Auto Restart Mysql

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
Post Reply