Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySql - replication monitoring tool [closed]

I have a master/slave MySql replication.

Im looking for a tool that will allow me to monitor the replication (see it has no error, check on the lag, etc.)

I prefer a visual tool that will allow all team members get visibility on the status and not a script tool.

any ideas?

like image 818
Ran Avatar asked Dec 15 '11 10:12

Ran


People also ask

Is MySQL enterprise monitor free?

As we stated, MySQL Enterprise Monitor is a part of the paid MySQL Enterprise Edition. For all users of the MySQL Community, MariaDB or Percona Server, MySQL Enterprise Edition is not available. ClusterControl provides access to monitoring of MySQL in its free Community version.


1 Answers

We are using the following bash script. You could do the same idea in php and web base the code.

#!/bin/sh
## Joel Chaney##
## [email protected]  (look at robots.txt) ##
## 2012-02-03  ##

repeat_alert_interval=30        # minutes for lock file life
lock_file=/tmp/slave_alert.lck  # location of lock file

[email protected]  # where to send alerts
SSTATUS=/tmp/sstatus            # location of sstatus file

### Code -- do not edit below ##
NODE=`uname -n`
## Check if alert is locked ##
function check_alert_lock () {
    if [ -f $lock_file ] ; then
        current_file=`find $lock_file -cmin -$repeat_alert_interval`
        if [ -n "$current_file" ] ; then
            # echo "Current lock file found"
            return 1
        else
            # echo "Expired lock file found"
            rm $lock_file
            return 0
        fi
    else
        touch $lock_file
    return 0
    fi
}

SLAVE=mysql

$SLAVE -e 'SHOW SLAVE STATUS\G' > $SSTATUS

function extract_value {
    FILENAME=$1
    VAR=$2
    grep -w $VAR $FILENAME | awk '{print $2}'
}

Master_Binlog=$(extract_value $SSTATUS Master_Log_File )
Master_Position=$(extract_value $SSTATUS Exec_Master_Log_Pos )
Master_Host=$(extract_value $SSTATUS Master_Host)
Master_Port=$(extract_value $SSTATUS Master_Port)
Master_Log_File=$(extract_value $SSTATUS Master_Log_File)
Read_Master_Log_Pos=$(extract_value $SSTATUS Read_Master_Log_Pos)
Slave_IO_Running=$(extract_value $SSTATUS Slave_IO_Running)
Slave_SQL_Running=$(extract_value $SSTATUS Slave_SQL_Running)
Slave_ERROR=$(extract_value $SSTATUS Last_Error)

ERROR_COUNT=0
if [ "$Master_Binlog" != "$Master_Log_File" ]
then
    ERRORS[$ERROR_COUNT]="master binlog ($Master_Binlog) and Master_Log_File         ($Master_Log_File) differ"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

POS_DIFFERENCE=$(echo ${Master_Position}-${Read_Master_Log_Pos}|bc)

if [ $POS_DIFFERENCE -gt 1000 ]
then
    ERRORS[$ERROR_COUNT]="The slave is lagging behind of $POS_DIFFERENCE"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_IO_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ "$Slave_SQL_Running" == "No" ]
then
    ERRORS[$ERROR_COUNT]="Replication (SQL) is stopped"
    ERROR_COUNT=$(($ERROR_COUNT+1))
fi

if [ $ERROR_COUNT -gt 0 ]
then
    if [ check_alert_lock == 0 ]
        then
          SUBJECT="${NODE}-ERRORS in replication"
          BODY=''
          CNT=0
          while [ "$CNT" != "$ERROR_COUNT" ]
          do
             BODY="$BODY ${ERRORS[$CNT]}"
             CNT=$(($CNT+1))
          done
          BODY=$BODY" \n${Slave_ERROR}"
          echo $BODY  | mail -s "$SUBJECT" $EMAIL
        fi
else
    echo "Replication OK"
fi
like image 131
Joel Chaney Avatar answered Sep 22 '22 00:09

Joel Chaney