Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Credit:

https://gist.github.com/roadst4r/2cb42793c0a7f9a7237a#file-mysql_repl_check-sh
https://gist.github.com/ssimpson89/7207165
https://yashwantkumarsingh.wordpress.com/2015/05/06/mysql-replication-monitoring-by-a-bash-script-with-xymon/

cat << 'EOL' >/etc/xymon/.sqlpwd 
[mysql] 
user=[USER] 
password=[PASSWORD]
cat <<'EOL'>/usr/lib/xymon/client/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=/etc/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 for SQL thread #####
SQL_IS_RUNNING=`echo "$MYSQL_STATUS" | grep "Slave_SQL_Running" | awk '{ print $2 }'`

if [ "$SQL_IS_RUNNING" != "Yes" ]
 then
     ERRORS="SQL thread for executing events in the relay log is not running (Slave_SQL_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
sudo chown xymon:xymon /usr/lib/xymon/client/ext/mysql_replication_check.sh 
sudo chmod 777 /usr/lib/xymon/client/ext/mysql_replication_check.sh  
cat << 'EOL' >/etc/xymon/clientlaunch.d/mysql_replication_check.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
/usr/lib/xymon/client/bin/xymoncmd /usr/lib/xymon/client/ext/mysql_replication_check.sh  

Once confirmed working lock down PWD file:

sudo chmod 600 /etc/xymon/.sqlpwd 
sudo chown xymon:xymon /etc/xymon/.sqlpwd  

Restart Service :

sudo service xymon-client restart  

XYMon Server :

sudo service xymon restart && sudo service apache2 restart 



  • No labels