MySQL Replication Health/Status

mysql_repl_status.sh

#!/bin/bash
#
# Determine if master and slave mysql servers are in sync
# If not, report it via STDOUT and non-zero return code
# If are in sync, exit successfully
#
# Paramters:
#   -v   - verbose, show stats even if 100% synchronized

USER='YOUR_USER_NAME'
PASS='YOUR_PASSWORD'
MASTER_HOST='HOSTNAME_OR_IP'
SLAVE_HOST='HOSTNAME_OR_IP'


### Get and parse mysql status variables ### if [ "$1" == "-v" ]; then
  echo "Connecting to Master ..."
fi
MASTER_STATUS=$(mysql -u$USER -p$PASS -h$MASTER_HOST -NE -e 'show master status;' | awk '(NR==2 || NR==3) {print $0}')

if [ "$1" == "-v" ]; then
  echo "Connecting to Slave ..."
fi
SLAVE_STATUS=$(mysql -u$USER -p$PASS -h$SLAVE_HOST -NE -e 'show slave status;' | awk '(NR==7 || NR==8 || NR==12 || NR==13 || NR==20 || NR==34) {print $0}')

master_file=$(echo $MASTER_STATUS | cut --delimiter=' ' -f 1) master_pos=$(echo $MASTER_STATUS | cut --delimiter=' ' -f 2)

slave_file=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 1) slave_pos=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 2) slave_running=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 3) slave_io=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 4) slave_error=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 5) slave_sec_behind=$(echo $SLAVE_STATUS | cut --delimiter=' ' -f 6)

if [ ! $master_file ]; then
  echo "Unable to connect to master"
  exit 1
fi
if [ ! $slave_file ]; then
  echo "Unable to connect to slave"
  exit 1
fi

if [ $slave_error != 0 -o $slave_io != 'Yes' -o $slave_running != 'Yes' ]; then
  echo "SLAVE IS NOT RUNNING"
  echo "SLAVE STATUS: $slave_io | $slave_running | $slave_error"
  exit 1
elif [ "$1" == "-v" ]; then
  echo "SLAVE STATUS: $slave_io | $slave_running | $slave_error"
fi

### Calculate percentage and fail if either filename or position differ ### if [ $master_file != $slave_file ]; then
  echo "Master and Slave are out of sync by bin-log file"
  echo "master: $master_file - $master_pos"
  echo " slave: $slave_file - $slave_pos"
  exit 1
fi

let "sync_percent=($slave_pos / $master_pos) * 100"

if [ "$1" == "-v"  -o $sync_percent != '100' ]; then
  echo "master: $master_file - $master_pos"
  echo " slave: $slave_file - $slave_pos"
  echo "  sync: $sync_percent%"
fi

if [ $slave_sec_behind -gt 0 ]; then
  echo " slave behind (sec): $slave_sec_behind"
elif [ "$1" == "-v" ]; then
  echo " slave is tracking realtime"
fi

if [ $sync_percent -gt 100 ]; then
  exit 1
fi

exit 0