Skip to content

Instantly share code, notes, and snippets.

@berkayunal
Forked from JGaudette/mysql_repl_status.sh
Created May 17, 2018 18:08
Show Gist options
  • Save berkayunal/703acf163931cc3a9093b49f9fff48f2 to your computer and use it in GitHub Desktop.
Save berkayunal/703acf163931cc3a9093b49f9fff48f2 to your computer and use it in GitHub Desktop.
MySQL Replication Health/Status
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment