ssh tunnel failover script 1

[roycrom]

Hi,

I have a Red Hat cluster with two nodes running mysql, one Primary and one standby.

I also have a web server that has an ssh tunnel to the primary mysql node.

If the primary node fails, I need the tunnel to fail over to the secondary node.

I have scripted something together that does something similar to the following:

Ping the primary node, if it responds check that the tunnel is up to that node.
If the tunnel is up, use "mysqladmin status" to check the service responds, if tunnel is not up, start it then check mysql service.
If I couldn't ping it, ping the secondary.  If its up check the tunnel, if its there, check the service, if tunnel not there start it and check the mysql service.

This is my actual script, I run it from cron every minute.

#!/bin/bash

# Script to determine whichof the DB cluster nodes is up and running
# MySQL so that we can make sure the SSH Tunnel is attached to the
# correct cluster node.

# Set up variables as usual.
MYPRIM="primarynode"
MYSEC="secondarynode"
PINGPRIM="/bin/ping -c1 -w1 $MYPRIM"
PINGSEC="/bin/ping -c1 -w1 $MYSEC"
MYSQLCMD="/usr/local/packages/mysql/bin/mysql"
GREPTUNNEL="/bin/ps -ef"
PRIMTUNNEL="/lcc/bin/sshtunnel_mysql_primary"
SECTUNNEL="/lcc/bin/sshtunnel_mysql_secondary"
echo `date`


CHECKSECTUNNEL() {
# OK, this bit should only run if MYSEC responded to a ping.
        LINES=`$GREPTUNNEL | grep $MYSEC  | grep 3306 | grep -v grep | wc -l`
        if [ $LINES = 1 ]; then
                echo "  OK, the tunnel is up."
                CHECKSECMYSQL
        else
                echo "  OK, So I could ping it but the tunnel isn't up!"
                echo "  Let's start it then see if we can access mysql"
                $PRIMTUNNEL stop
                $SECTUNNEL stop
                $SECTUNNEL start
                CHECKSECTUNNEL
        fi
}

CHECKPRIMTUNNEL() {
# OK, this bit should only run if MYPRIM responded to a ping.
        LINES=`$GREPTUNNEL | grep $MYPRIM | grep 3306 | grep -v grep | wc -l`
        if [ $LINES = 1 ]; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
        else
                echo "  OK, So I could ping it but the tunnel isn't up!"
                echo "  Let's start it then see if we can access mysql"
                $PRIMTUNNEL stop
                $SECTUNNEL stop
                $PRIMTUNNEL start
                CHECKPRIMTUNNEL
        fi
}


# We are going to ping the DB servers to check they are up.
CHECKSECONDARY() {
        $PINGSEC >> /dev/null 2>&1
        RESULT=$?
        if [ $RESULT = 0 ]; then
                echo "  $MYSEC is responding to pings."
                CHECKSECTUNNEL
        else
                echo "  Well I can't ping $MYSEC either."
                echo "  I am emailing now to tell everyone."
                ( echo "From: mysqltunnelchecker@webserver"
                  echo "To: me@myaddress"
                  echo "Bcc: him@hisaddress"
                  echo "Subject: MYSQL SSH TUNNEL FAILURE!"
                  echo
                  echo "The script checkmysqltunnel.sh on the webservers"
                  echo "has detected that no tunnel is running,"
                  echo "therefore mysql connectivity from the webservers"
                  echo "will not work."
                  echo ""
                  echo "Manual intervention is required."
                ) | sendmail -oi -t
        fi
}

CHECKPRIMARY() {
        $PINGPRIM >> /dev/null 2>&1
        RESULT=$?
        if [ $RESULT = 0 ]; then
                echo "  $MYPRIM is responding to pings."
                CHECKPRIMTUNNEL
        else
                echo "  Well I can't ping $MYPRIM so I will try $MYSEC."
                CHECKSECONDARY
        fi
}

# We will use mysqladmin to get a status of the MySQL service.
CHECKPRIMMYSQL() {
/usr/local/packages/mysql/bin/mysqladmin status -h 127.0.0.1 -utunnelcheck >> /dev/null 2>&1
MYSQLUP=$?
if [ $MYSQLUP = 0 ]; then
        echo "  MYSQL service is running correctly."
else
        echo "  Cannot connect to MYSQL."
        $PRIMTUNNEL stop
        $SECTUNNEL stop
        CHECKSECONDARY
fi
echo ""
}
CHECKSECMYSQL() {
/usr/local/packages/mysql/bin/mysqladmin status -h 127.0.0.1 -utunnelcheck >> /dev/null 2>&1
MYSQLUP=$?
if [ $MYSQLUP = 0 ]; then
        echo "  MYSQL service is running correctly."
else
        echo "  Cannot connect to MYSQL."
        $PRIMTUNNEL stop
        $SECTUNNEL stop
fi
echo ""
}
# Lets do this in functions so we can decide which bits go in and out easier.
# If both alive we want to use the Primary Node as the one we connect to
# so lets check MySQL is up and running there eh? use mysqladmin?
CHECKPRIMARY

This isn't the most sophisticated script, it works, but I'm sure there must be a better way to do this. There is a chance that this script could get into a bad loop.

I have learnt shell scripting as and when I have needed so I have no real advanced techniques.

I was wondering if anyone had something similar that could suggest a better way of going about this.

------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom

 

[roycrom]

So I take it everyone thinks my script is perfect and requires no modding!! :

------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom

 

[columb]

One place which could be tidied (but it doesn't affect the logic or make it a better script) is

$PINGSEC >> /dev/null 2>&1
RESULT=$?
if [ $RESULT = 0 ]; then
 ...
 ...
fi

can be written

if $PINGSEC >> /dev/null 2>&1; then
  ...
  ...
fi

Similarly the bit which goes

LINES=`$GREPTUNNEL | grep $MYPRIM | grep 3306 | grep -v grep | wc -l`
        if [ $LINES = 1 ]; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
        else

could be shortened to

if [ $($GREPTUNNEL | grep $MYPRIM | grep 3306 | grep -v grep | wc -l) -eq 1]; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
else

Note that I've replaced `command` with $(command). This is slightly more efficient.

Also, in that bit, you don't need the 'grep -v grep', the two other greps will sort that out. so it becomes

if [ $($GREPTUNNEL | grep $MYPRIM | grep 3306 |  wc -l) -eq 1]; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
else

Finally, as a style point, i'm not a fan of replacing 'ps -ef' by $GREPTUNNEL - I don't want to have to refer back everytime to see what it is - and, to be brutal, GREPTUNNEL isn't a particularly descriptive variable name. So now we have

if [ $(ps -ef | grep $MYPRIM | grep 3306 |  wc -l) -eq 1]; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
else

and, the condition is binary, isn't it. I mean, either there's a process or there isn't so we can dispense with the call to 'wc' and put

if $ps -ef | grep $MYPRIM | grep -q 3306; then
                echo "  OK, the tunnel is up."
                CHECKPRIMMYSQL
else

Note the grep -q which loses the output and removes the need for '>/dev/null'



That's more than enough for now.

Ceci n'est pas une signature
Columb Healy

 

[roycrom]

Columb,

many thanks for your reply,

Some nice tips there which will help me tidy up my code to make it more readable and do away with superfluous checks.

I hhave taken all your points on board and thank you for being BRUTAL! Sometimes that's the only way I get the message ;-)

------------------------------------------
Somethings come from nothing, nothing seems to come from somethings - SFA - Guerilla

roycrom