Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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
share|improve this question
    
The question seems to be wrong. The if...else is obviously working. Your question is: Why does the mysql command not exit with code 0? You should make the second question a separate question as they are not related. –  Hauke Laging Apr 24 at 8:40
    
@Hauke Laging, thanks, so where is the problem? why the else part also working? –  cj333 Apr 24 at 8:43
    
You seem to be running the cronjob with root (considering your 2nd question). You don't need the sudo then. Just remove "/usr/bin/sudo" in front of the service calls. It should save you from the TTY problem of sudo. –  0xAF Apr 29 at 22:43
    
@0xAF, Now first i doubt crontab connect mysql is wrong, because even the server is run well, it still jump into else case, echo "error $(date)" >> /home/myspace/restart_log.txt. but it works directly in SSH part. thanks. –  cj333 Apr 30 at 6:23
    
try changing this line: RESULT=`mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select browser from test limit 1"`, to this: RESULT=`mysql --host="localhost" --user="root" --password="password" --database="test" --execute="select browser from test limit 1" 1>/tmp/mysql.debug 2>&1` and see the output in /tmp/mysql.debug –  0xAF Apr 30 at 10:08

5 Answers 5

up vote 1 down vote accepted
+50

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

share|improve this answer
    
so how to force it? how to set the right path? –  cj333 Apr 30 at 6:24

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.

share|improve this answer
    
maybe i made some mistake when i installed mysql, there is no mysqladmin in /usr/bin/, so i tried and write some bash script by myself. thanks. –  cj333 Apr 26 at 18:58
    
mysqladmin is part of the official distribution of MySQL. It really should be on your system, and if it isn't you should fix that. It might not be in /usr/bin though, could be some other place (depending on how you installed it). Change the paths in the cron entry accordingly. Don't try to implement your own workaround for this (chances are you end up with a sub-par solution). –  Sigi Apr 26 at 18:59
    
it is real not in my /usr/bin/ folder, so how to fix it? yum? or other process? –  cj333 Apr 27 at 8:02
    
It should be in your path. Do which mysqladmin to find out where. If you installed mysql with yum, then it's not going to be in /opt or /usr/local. Maybe CentOS has a separate package containing it. Check the list of packages related to mysql. –  Sigi Apr 27 at 16:37
    
I tired which mysqladmin, return /usr/bin/which: no mysqladmin in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin) and no mysqladmin in /usr/local or /opt –  cj333 Apr 27 at 17:46

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
share|improve this answer
    
thanks your explain, so how to modify my code for it run well? –  cj333 Apr 24 at 8:44
    
@cj333 change the existing echo as I indicated, add another in the else part and change the sudoers file. What is unclear about that? –  Anthon Apr 24 at 9:02
    
both my english and bash skill are not good, can you correnct my code and parse it in your Answer? Thanks. –  cj333 Apr 24 at 9:20
    
I updated my code in question. pls go on helping me. thanks. –  cj333 Apr 24 at 11:04
    
@cj333 did you change your /etc/sudoers file? –  Anthon Apr 24 at 13:33

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.

share|improve this answer
    
indeed, i only want to check mysql server every minute, only it runs not well then restart. not restart every minute. thanks. And sometimes, if no sudo, the service mysql stop..................................... it seems dead. –  cj333 Apr 24 at 11:10
1  
I understand you do not want to restart every minute, but that's what will be happening when the disk is full, the database is renamed or the database useraccount (root??) has changed it's password. –  Walter A Apr 24 at 12:04

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.