bash restart mysql when it gone run well in SSH part, but not in cronjob

Question

My system is centos 6.5,

I write a simple bash shell to check if mysql is crashed, restart service again. I put it in /home/myspace/mysql.sh chown root:root /home/myspace/mysql.sh then run every minute by crontab.

#!/bin/bash
mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select id from test limit 1"
if [ $? -eq 0 ]; then echo "";
else
 /usr/bin/sudo service mysql stop
 pkill mysql
 /usr/bin/sudo service mysql start
 echo "error $(date)" >> /home/myspace/restart_log.txt
fi

Now I have 2 questions.

1. Why in my code, if ... else ... not working? I mean the mysql server has no problem, it can execute "select id from test limit 1" and get result, but the script still run the code in else case.

2. in /var/log/sucure, it show root : sorry, you must have a tty to run sudo ; TTY=unknown ; PWD=/root ; USER=root ; COMMAND=/sbin/service mysql stop. root : sorry, you must have a tty to run sudo ; TTY=unknown ; PWD=/root ; USER=root ; COMMAND=/sbin/service mysql start. And the mysql server is down.

UPDATE: Now I tried the code @Anthon given:

#!/bin/bash
RESULT=`mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select browser from test limit 1"`
if [ $? -eq 0 ]; then 
   echo "mysql select ok"
else
  /usr/bin/echo "mysql select failed"
  sudo service mysql stop
  pkill mysql
  /usr/bin/sudo service mysql start
  echo "error $(date)" >> /home/myspace/restart_log.txt
fi

it works well in SSH part (return mysql select ok)

but not working in cronjob(every minute write error in /home/myspace/restart_log.txt and mysql server never start)

Is this problem caused by PATH? OR privileges? How to solve it? thanks.

solve Thanks for all, finally I solved it. @Sigi's method maybe good, but not working in my situation. @Anthon's answer write better than I, but still not working. @Emmanuel, given the answer is more nearer than other's. After a long time test, I shared final working code to all.

#!/bin/bash
PATH=/bin:/usr/bin:/sbin:/usr/local/bin
# cronjob need clear where is the path
# mysql under path /usr/local/bin
mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select id from test limit 1"
if [ $? -eq 0 ]; then echo "";
# mysql run well nothing to do, make a
else
 sudo /etc/init.d/mysql stop
 pkill /usr/local/bin/mysql
# It should be pkill /usr/local/bin/mysql, not pkill mysql, wrong write will cause below code not working
 sudo /etc/init.d/mysql start
 sudo /etc/init.d/httpd restart
# Some strange behave, after mysql restart, apache will die, so should add httpd restart 
 echo "error $(date)" >> /home/myspace/restart_log.txt
fi

Answer 1

Want you want to do is check if the database server process is still alive. MySQL provides a command for that: mysqladmin ping

I suggest you put the following into your system crontab (edit the file /etc/crontab):

* * * * *  root  /usr/bin/mysqladmin --host="localhost" --user="root" --password="password" ping || /usr/sbin/service mysql restart

This will ping the database every minute, and issue a "restart" if it does not respond.

It is not necessary to query an arbitrary table and test the result to determine if the database is alive.

Here is what the official documentation has to say about the ping subcommand:

ping
Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.

Even better would be to use a dedicated process watcher (such as monit, upstart or systemd) to keep the mysql process alive.

Answer 2

As your log indicates (point 2), the else part is being executed, but just exits in the first statement.

If you want to debug something that you suspect is not being executed, make sure you echo at the beginning of both paths (you can always remove these when things work). Your empty echo in the if part is not helpful, would it print something, you would have noticed that that is not printed, but better would be to have an echo "takeing else route" on the line after the else.

To get sudo to work, you probably have to edit your /etc/sudoers file and comment out requiredtty (from the man page):

requiretty      If set, sudo will only run when the user is logged in
                to a real tty.  When this flag is set, sudo can only be
                run from a login session and not via other means such
                as cron(8) or cgi-bin scripts.  This flag is off by
                default.

To get feedback your script should look like:

#!/bin/bash
mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select id from test limit 1"
if [ $? -eq 0 ]; then 
   echo "mysql select ok"
else
   echo "mysql select failed"
 /usr/bin/sudo service mysql stop
 pkill mysql
 /usr/bin/sudo service mysql start
 echo "error $(date)" >> /home/myspace/restart_log.txt
fi

Answer 3

Off-topic: Do you really want to restart the service every minute? When the services fails due to some other failure, trying to restart every minute is terrible. (Never delete the test-database...) (You might make a smarter script that checks for the last failur time and waits at least 1 hour before retrying and mailing an operator)
On-topic: When you can't depend on the returnvalue of mysql, you can try to select some string:

FLAG="$(mysql --host="localhost" --user="root" --password="password" --database="test" \
   --execute="select count(*) as MYSPECIALFLAG from test")"
if [[ ${FLAG} != *MYSPECIALFLAG* ]]; then
   my_restart_function
fi

Other question: sudo without terminal: Restarting the service is the responsibility of root. The system administrator running root would like to check his own crontab when something is restarting the service every minute. So convince your system administrator that he wants to run your magic script as root, and you do not have problems with sudo.

Answer 4

Force the PATH in your script, cron may not provides the same path as the one you have in a terminal session.

EDIT:
In a terminal get your PATH:

env | grep "^PATH="   
PATH=/sbin:/usr/sbin:/usr/local/sbin:...   (you will have different value

Cut and paste that line in your script just bellow the #!/bin/bash

#!/bin/bash

PATH=/sbin:/usr/sbin:/usr/local/sbin:     (put your path 
export PATH

(thats just for instance)

There may be other variable to add in your script; look for variable which names contain the 'MYSQL' string with env | grep MYSQL and set | grep MYSQL

Answer 5

MOST LIKELY - you need to specify the correct service, on CENTOS 6.5 it would be

service mysqld stop

not

service mysql stop

This would also be why pkill is not working, you would need pkill mysqld

But the real command to use is service mysqld restart and avoid using pkill altogether.

Other general notes when running through cron:

1) On CentOS 6.5, the path to echo is /bin/echo

2) If you run cron as root, do not run the commands through sudo (this will avoid tty/pty, environment, and permission problems sudo can introduce)

3) Specify the full path to pkill = /usr/bin/pkill on CentOS 6.5

4) Specify the path to mysql = /usr/bin/mysql on CentOS 6.5

As Emmanuel alludes to, there are probably not enough paths defined in PATH variable when run through cron. Generally, give the full path to all commands.