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?
Auto Restart Mysql
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Should be simple enough, create a shell script and add the script to cron, e.g.
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
## 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 &
fiSet the script to run via cron at a one hour interval:
Code: Select all
0 * * * * exec /path/to/mysql_restart.sh- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Auto Restart Mysql
Forgive me for asking but why would you want to do that ?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?
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.