How NOT to test that mysqld is alive

ohh those good old times when were so beginners.. This article makes me remember my own mistakes 🙂

or
if [`ps -aux | grep mysqld | grep -v grep | wc -l` -lt 1]

Remove the grep from your output before you count it.

“Just use mysqld_safe, huh?” 🙂

How about “mysqld status”?

Correct I just went through the /etc/rc.d/init.d/functions for “status”.

When I first read your article my initial though was , the number needs to be 2. As @mark wrote, where is mysqld_safe
It bothers me when distro’s think they can do stuff better.

It is sometimes of benefit to have multiple checks to determine or triangulate issues. Other checks include mysqladmin ping, netstat for 3306, however anything that checks for pid’s, ports, sockets etc are bound to having to support the variable nature etc.

mysqld_safe is designed to undertake exactly the situation your script attempts to describe. Seems the most logical solution is to implement mysqld_safe.

Need to know the socket (or any variable for that matter)? In bash:

my_print_defaults mysqld | grep socket | cut -d “=” -f 2

Wrap in $(…) and you can include it in any alias / script.

Another way to check if it’s up:

mysqladmin -u user -ppass version

The user could be one w/ only USAGE granted.

My $.02
G

I do phone technical support. I never even considered this possibility. I will keep this in mind. Thanks for the knowledge drop.

BTW, I like Tim Jinkerson’s way of doing this. It’s probably the easiest way to do it.

if you really must use grep for finding a process, try

| grep [m]ysqld

tangent:

To address the general problem of grepping theough ps’s output and grep finding itself, there are a couple of options:

* Switch to “pgrep”

* Make use of a clever trick: Grep supports regexes. If you switch the first letter of what you’re grepping for with a regex’s character class (by surrounding it with [ ]), it’ll still match what you want, but not the grep pattern itself: example: “grep [m]ysqld”

The best thing to to is to make a regular expression that does not match itself.

ps x |grep fo[o]

The brackets of the character class containing the single letter are metacharacters that contribute to how the grep matches, and while the text that it matches is exactly the same as with no character class, it ensures that the characters that make it up do not match.

You can also use “ps -C mysqld > /dev/null” and test $?.

You could always use:

if [`ps -aux | grep [mM]ysqld | wc -l` -lt 1]
then blah blah blah
fi

While there are better ways of doing this that have been noted, there are a couple ways they could’ve made their chosen method work better.

A workaround I’ve used in the past is to replace “grep mysqld” with “egrep [m]ysqld”, so that grep doesn’t find itself in the ps list.

Also, a lot of headache could have been saved if they made some type of log entry saying that the script was restarting the service instead of just issuing the command. This would also help finding trends.

I have always used “mysqladmin ping” to test whether the service is alive and check for exit status of 0

mysqladmin ping
if [ $? -ne 0 ]
then
/etc/init.d/mysqld start
else
echo “All is well”
fi

It all sounded very technical, your description of piped processes running simultaneously but in my experience earlier ones always feed into later ones. The issue was more likely a logic error somewhere or simply not testing for the right number. A cut and paste of the actual faulty script would have been good. On my Linux box a normal mysql install runs 2 processes and the grep process would take the minimum count to 3. So they should perhaps have been testing for less than 3 and not 2. However my main point is that I seriously doubt the accuracy… Read more »

Instead of connecting and running a query you can use mysqladmin ping then check the exit status (zero for alive, non-zero for dead.) That’s the “official” way to check it, won’t have the overhead and the potential problems of running a query, and will avoid false positives from other processes with names that include mysql.

If really using pgrep, at least do it correctly: pgrep “^mysql$” (or whatever the process is named, taking care of quoting any special meaning characters.)

Really the best way is the Query, When you are really old you will realize that even when a process is hung it still shows up in ps listings. Do the query because it confirms the process is handling requests and not overloaded.

I wrote a little C program & an article on how to do exactly this at my last company..

http://knowledgehub.zeus.com/articles/2006/02/27/custom_monitors_for_mysql_in_c

Might be of help to anyone who as read this.

Cheers.

Please, everyone, stop using these hacks. At their very best, they are inelegant and have race conditions. Instead, use something like daemontools or runit. They (a) automatically restart a daemon that dies, and (b) provide race-free reporting of PID and uptime without a kludge like “ps | grep”.

Never check for pid files. Never ever ever. It is entirely possible for a process to die abruptly (say, after getting a SIGKILL, after a kernel panic, power outage, the list is endless) and not have the opportunity to remove its pidfile. The single best test mechanism is to directly query the daemon with a short, low-intensity test.

Hell, or just do `ps aux | grep -v grep |grep mysqld | wc -l`

This is craziness. 1. Why is a company without a Sys Admin running Gentoo (not your fault – but somebody needs to tell them that the scenarios is worse than less than ideal). Ubuntu really is a far superior solution for them. 2. MySQL doesn’t just ‘go down’. I understand hacks are necessary sometimes, but auto-restarts usually cause more problems than they solve. Automatic failover (i.e. master-slave switch) is a better solution if maintaining uptime without intervention is necessary. 3. An uptime script should use app-specific code like ‘SELECT * FROM table1 LIMIT 1’ to confirm that the database isn’t… Read more »

ps -Cmysqld –no-header

Will return exactly one line per running ‘mysqld’ process. No grep required.

This is how I do it.

sqlsocket=`grep socket /etc/my.cnf | awk -F = ‘{ print $2 } ‘`

count=0;
echo -n “Checking MySQL”

while [ ! -S “${sqlsocket}” ]; do
sleep 1;
echo -n “.”
count=$((count+1));
[ $count -gt 5 ] &done

Your n00bness comes oozing out right through your proposed solutions. If you are going to tolerate a system where a SQL daemon is going offline, which you should not in the first place, the first solution is the correct one, with a modification: grep for the full path to mysqld. This is a superior technique to any of the other techniques you mentioned. Why? Let me enumerate: pgrep will tell you if any mysqld is running on your system, not necessarily the one you want running. same problem with looking for sockets. popping in to do a SELECT in mysql,… Read more »

Salami,

I tried your suggestion but got the following error

mysql> SELECT something;
ERROR 1054 (42S22): Unknown column ‘something’ in ‘field list’

Please help!!

Ok, obviously this is a silly way to check if a process is running. A better way to do this, although still silly, is just to run a cron job every mnute that does /etc/init.d/mysql start

If mysql is already running, nothing will happen, But if it’s not, it will start, unlses there’s an actual problem with mysql.

@schlomi I completely accept that there was something wrong with their script. However in my experience piped processes accept ALL of the stdin. They don’t somehow ‘lose’ input and return varying results. I have seen processes and grep being listed in different order but I have never seen any of the processes or grep NOT being listed whether they are executed simultaneously or not. I suggest that they simply tested for -lt 2 and it should have been -lt 3. No matter what everyone says about how it was a n00b error, everyone makes mistakes when writing scripts and the… Read more »

pgrep seems like the best idea, but a very quick way to fix the original is just to use `ps -e` since it won’t list process arguments.

How about:

ps ax | grep mysql[d]

and do the necessary wc logic

@shlomi. Ok. I see what you see now. However I only see it in the context of a statement within backticks.
Your laptop example shows a consistent 3 processes on mine. (Opensuse 11.0) I.e. ps aux | grep mysqld shows 2 mysqld processes and a grep process every time. An if statement with a backtick test statement though, drops the grep statement at random.
I’ve never seen that before as I’ve always filtered the grep process in scripts. (… and never tried to implemented such a stupid restart policy.) I stand corrected.

Here is the corrected Startup Script say
#start.sh
while [ 1 ];
if [ `ps aux | grep mysql | grep -v mysql | wc -l` -lt 1 ]
then /etc/init.d/mysql restart
…

Also add a cronjob using command:
crontab -e
* * * * * start.sh

Mihai @ 22: ‘mysqladmin ping’ wins 🙂
You win a … uh, something.

Everyone else discussing ps and various perverse grep misadventures, please don’t do it. All it takes is someone running
‘mysqldump’ or ‘man mysqld’ or ‘vi /tmp/why_is_mysqld_restarting.txt’ to generate a false positive.

pidfiles are also unreliable if the server has died unexpectedly.

Use ‘mysqladmin ping’. It goes away, parses the mysql config files for you, connects to the mysqld.sock and checks that mysqld is actually working.

looks like some sysadmin wannabe does not even know how to use awk and regular expressions, any kid should see the holes in that solution

for procf in /proc/[0-9]*; do
if [ “$(cat $procf/exename)” = “/usr/bin/mysqld” ]; then
echo “process is running”
fi
done