Skip to content

Instantly share code, notes, and snippets.

@ssimpson89
Last active April 30, 2022 13:54
Show Gist options
  • Star 54 You must be signed in to star a gist
  • Fork 26 You must be signed in to fork a gist
  • Save ssimpson89/7207165 to your computer and use it in GitHub Desktop.
Save ssimpson89/7207165 to your computer and use it in GitHub Desktop.
Just a simple Mysql Replication Health Check script I wrote. You can put this in a cron.
#!/bin/bash
### VARIABLES ### \
EMAIL=""
SERVER=$(hostname)
MYSQL_CHECK=$(mysql -e "SHOW VARIABLES LIKE '%version%';" || echo 1)
LAST_ERRNO=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" | grep "Last_Errno" | awk '{ print $2 }')
SECONDS_BEHIND_MASTER=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G"| grep "Seconds_Behind_Master" | awk '{ print $2 }')
IO_IS_RUNNING=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{ print $2 }')
SQL_IS_RUNNING=$(/usr/bin/mysql -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{ print $2 }')
ERRORS=()
### Run Some Checks ###
## Check if I can connect to Mysql ##
if [ "$MYSQL_CHECK" == 1 ]
then
ERRORS=("${ERRORS[@]}" "Can't connect to MySQL (Check Pass)")
fi
## Check For Last Error ##
if [ "$LAST_ERRNO" != 0 ]
then
ERRORS=("${ERRORS[@]}" "Error when processing relay log (Last_Errno)")
fi
## Check if IO thread is running ##
if [ "$IO_IS_RUNNING" != "Yes" ]
then
ERRORS=("${ERRORS[@]}" "I/O thread for reading the master's binary log is not running (Slave_IO_Running)")
fi
## Check for SQL thread ##
if [ "$SQL_IS_RUNNING" != "Yes" ]
then
ERRORS=("${ERRORS[@]}" "SQL thread for executing events in the relay log is not running (Slave_SQL_Running)")
fi
## Check how slow the slave is ##
if [ "$SECONDS_BEHIND_MASTER" == "NULL" ]
then
ERRORS=("${ERRORS[@]}" "The Slave is reporting 'NULL' (Seconds_Behind_Master)")
elif [ "$SECONDS_BEHIND_MASTER" > 60 ]
then
ERRORS=("${ERRORS[@]}" "The Slave is at least 60 seconds behind the master (Seconds_Behind_Master)")
fi
### Send and Email if there is an error ###
if [ "${#ERRORS[@]}" -gt 0 ]
then
MESSAGE="An error has been detected on ${SERVER} involving the mysql replciation. Below is a list of the reported errors:\n\n
$(for i in $(seq 1 ${#ERRORS[@]}) ; do echo "\t${ERRORS[$i]}\n" ; done)
Please correct this ASAP
"
echo -e $MESSAGE | mail -s "Mysql Replication for $SERVER is reporting Error" ${EMAIL}
fi
@KevinBott
Copy link

I took a different approach to parsing. I used sed to delete the first row, then remove leading spaces, and replace colon space with colon (to strip the leading space from $value"):
mysql ${connect_options} -e "SHOW SLAVE STATUS \G" | sed '1d;s/^ *//;s/: /:/;' > $slave_status

Then reading through my file I split the keys and values with a colon delimiter.

while read line; do
  key=$(echo $line | cut -f1 -d:)
  value=$(echo $line | cut -f2 -d:)

  case $key in
          Slave_*|SQL_*|Last_*)
                #Use Indirect Variable Assigment to assign "Value" to all "Key"s
                eval "$key=\$value"
                ;;
  esac
done < $slave_status

When the case statement matches a key, then using eval for indirect variable assignment. For example, I end up with variables $Last_IO_Errno, $Slave_IO_Running, etc. If you prefer UPPERCASE variable names, use tr.
key=$(echo $line | cut -f1 -d: | tr [:lower:] [:upper:])

I'm working on adding threshold for cases where Slave is stopped gracefully - so some short maintenance is possible without getting alerted.

@lemnisk8
Copy link

lemnisk8 commented Nov 8, 2017

ubuntu 16.04 MariaDB-10.2.10 - needed to add the exit in awk to stop after the first line
SQL_IS_RUNNING=$(grep "Slave_SQL_Running" <<< "$STATUS_LINE" | awk '{ print $2; exit }')

@xikaos
Copy link

xikaos commented Jun 14, 2018

Checkout for the login-path option in MySQL client so you can sleep at night knowing your MySQL root user is not in pure plain-text, as suggested previously here.

  1. Generate a login-path using mysql_config_editor as explained here.
  2. Add the --login-path option: MYSQL_CHECK=$(mysql --login-path=login_path_generated -e "SHOW VARIABLES LIKE '%version%';" || echo 1) in other statements as well.

The file encryption can be cracked, so ensure no one beside root have access to it.

@OliverBailey
Copy link

OliverBailey commented May 15, 2019

Nice script.

Forked here if anyone wants to see the revision

Added some extra checks.

Used --login-path=mysql_login instead of using a mysql -u -p via the mysql_config_editor

Added SSMTP sending of email instead of something like postfix.

Added AWS S3 backup granted the check goes through ok.

Changed it to utilise the $STATUS_LINE variable instead of calling multiple times to SHOW SLAVE STATUS

Few other changes as per the comments above.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment