MySQL Replication Script For Xymon

Write MySQL user and password into a hidden file on MySQL slave server.

cat << 'EOL' >/home/xymon/.sqlpwd 
[mysql] 
user=root 
password=abc12345xxx
EOL

Configure MySQL replication health scripts in /home/xymon/ext/mysql_replication_check.sh

cat <<'EOL'>/home/xymon/ext/mysql_replication_check.sh
#!/bin/bash

COLUMN=sql_repl
COLOR=green
SERVER=$(hostname -f)
MSG="Checking SQL Slave Replication Status on $SERVER"
MYSQL="/usr/bin/mysql --defaults-extra-file=/home/xymon/.sqlpwd"

##### Get Slave Status #####
MYSQL_STATUS=`echo "show slave status\G" | $MYSQL | sed -e 's/^[[:space:]]*//g' 2>&1`

##### Check For Last Error #####
LAST_ERRNO=`echo "$MYSQL_STATUS" | grep "Last_Errno" | awk '{ print $2 }'`

if [ "$LAST_ERRNO" != 0 ]
 then
    ERROR="Error when processing relay log (Last_Errno)"
fi

##### Check if IO thread is running #####
IO_IS_RUNNING=`echo "$MYSQL_STATUS" | grep "Slave_IO_Running" | awk '{ print $2 }'`

if [ "$IO_IS_RUNNING" != "Yes" ]
 then
    ERRORS="I/O thread for reading the master's binary log is not running (Slave_IO_Running)"
fi

##### Check Replication Delay #####
SECONDS_BEHIND_MASTER=`echo "$MYSQL_STATUS" | grep "Seconds_Behind_Master" | awk '{ print $2 }'`

if [ "$SECONDS_BEHIND_MASTER" == "NULL" ]
 then
    ERRORS="The Slave is reporting 'NULL' (Seconds_Behind_Master)"
elif [ "$SECONDS_BEHIND_MASTER" -gt 60 ]
 then
    ERRORS="The Slave is at least 60 seconds behind the master (Seconds_Behind_Master)"
fi

##### XYMon Status #####
cmd="$($MYSQL -e 'show slave status\G' | sed -n '1!p' | sed -e 's/^[[:space:]]*//g' )"

if [ -n "$ERRORS" ]

 then
    COLOR=red
    MSG="An error has been detected on $SERVER involving the MySQL Replication
    ""
    "${ERRORS}"
    "${cmd}"
    "
else
    MSG="Mysql Replication for $SERVER is Running Well
    ""
    "${cmd}"
    "
fi

# Leave the rest of script alone
# Tell Xymon about it
$XYMON $XYMSRV "status $MACHINE.$COLUMN $COLOR date

${MSG}
"

exit 0
EOL

Changes /home/xymon/ext/mysql_replication_check.sh to xymon owner and group and also changes permisson to 755

sudo chown xymon:xymon /home/xymon/ext/mysql_replication_check.sh 
sudo chmod 755 /home/xymon/ext/mysql_replication_check.sh

Write in /home/xymon/etc/clientlaunch.cfg for sql_repl to run on client it self

cat << 'EOL' >/home/xymon/etc/clientlaunch.cfg
[sql_repl] 
ENVFILE $XYMONCLIENTHOME/etc/xymonclient.cfg 
CMD $XYMONCLIENTHOME/ext/mysql_replication_check.sh 
LOGFILE $XYMONCLIENTHOME/logs/mysql_replication_check.log 
INTERVAL 15m 
EOL

Run this command to test , see if there any problem, normally problem will be only mysql password file or either you set up in a wrong server (Remember must be setup this scritps in slave mysql server)

/home/xymon/bin/xymoncmd /home/xymon/ext/mysql_replication_check.sh

Lock down mysql password file permisson to 600

chmod 600 /home/xymon/.sqlpwd

Changes owner and group to xymon for mysql password file

chown xymon.xymon /home/xymon/.sqlpwd

Restart Xymon and Test