Created
March 8, 2016 01:01
-
-
Save lyoshenka/cf97578cf6339c1f6bb9 to your computer and use it in GitHub Desktop.
Check that MySQL slave is running and no errors are present. Optionally send an email if errors are detected.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
set -euo pipefail | |
### CONFIG | |
EMAIL_ADDR="" # email this address if errors detected | |
MAX_SECONDS_BEHIND=5 # max seconds that slave can be behind master | |
# mysql credentials | |
MYSQL_USER="" | |
export MYSQL_PWD="" | |
MYSQL_HOST="localhost" | |
MYSQL_PORT=3306 | |
### CODE | |
SLAVE_IO='null' | |
SLAVE_SQL='null' | |
LAST_ERRNO='null' | |
LAST_SQL_ERRNO='null' | |
LAST_IO_ERRNO='null' | |
SECONDS_BEHIND='99999' | |
STATUS="$(mysql --user="$MYSQL_USER" --host="$MYSQL_HOST" --port="$MYSQL_PORT" --vertical --execute='show slave status')" | |
if [ -z "$STATUS" ]; then | |
echo "ERROR: this server is not a MySQL slave" | |
exit 1 | |
fi | |
while read -r LINE; do | |
case "$LINE" in | |
'Slave_IO_Running: '*) | |
SLAVE_IO=${LINE##*: } | |
;; | |
'Slave_SQL_Running: '*) | |
SLAVE_SQL=${LINE##*: } | |
;; | |
'Last_Errno: '*) | |
LAST_ERRNO=${LINE##*: } | |
;; | |
'Last_SQL_Errno: '*) | |
LAST_SQL_ERRNO=${LINE##*: } | |
;; | |
'Last_IO_Errno: '*) | |
LAST_IO_ERRNO=${LINE##*: } | |
;; | |
'Seconds_Behind_Master: '*) | |
SECONDS_BEHIND=${LINE##*: } | |
;; | |
esac | |
done <<< "$STATUS" | |
ERRORS=() | |
if [ "$LAST_ERRNO" != "0" ]; then | |
ERRORS+=("ERROR: LAST_ERRNO is $LAST_ERRNO, should be 0") | |
fi | |
if [ "$LAST_SQL_ERRNO" != "0" ]; then | |
ERRORS+=("ERROR: LAST_SQL_ERRNO is $LAST_SQL_ERRNO, should be 0") | |
fi | |
if [ "$LAST_IO_ERRNO" != "0" ]; then | |
ERRORS+=("ERROR: LAST_IO_ERRNO is $LAST_IO_ERRNO, should be 0") | |
fi | |
if [ "$SLAVE_SQL" != "Yes" ]; then | |
ERRORS+=("ERROR: SLAVE_SQL is $SLAVE_SQL, should be 'Yes'") | |
fi | |
if [ "$SLAVE_IO" != "Yes" ]; then | |
ERRORS+=("ERROR: SLAVE_IO is $SLAVE_IO, should be 'Yes'") | |
fi | |
if [[ $SECONDS_BEHIND =~ ^-?[0-9]+$ ]] && [ "$SECONDS_BEHIND" -gt "$MAX_SECONDS_BEHIND" ]; then | |
ERRORS+=("ERROR: SECONDS_BEHIND is $SECONDS_BEHIND, should be less than $MAX_SECONDS_BEHIND") | |
fi | |
if [ "${#ERRORS[@]}" -gt 0 ]; then | |
for ERROR in "${ERRORS[@]}"; do | |
echo "$ERROR" | |
done | |
if [ -n "$EMAIL_ADDR" ]; then | |
echo "Emailing $EMAIL_ADDR" | |
echo -e "${ERRORS[*]}\n\n\n$STATUS" | mail -s "$0 - $(hostname -f)" "$EMAIL_ADDR" | |
fi | |
fi |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment