-
-
Save ssimpson89/7207165 to your computer and use it in GitHub Desktop.
#!/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 |
For the line:
MYSQL='/usr/bin/mysql -u mysql_user -pyourpassword'
You may want to set up the /var/lib/mysql/repl_chk.cnf file with the MySQL root user ID password and make sure that it's only readable by the root and/or mysql user and then use this:
MYSQL='/usr/bin/mysql --defaults-file=/var/lib/repl_chk.cnf'
This way your root password isn't readable in your script. You can also use this for other monitoring scripts as well.
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.
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 }')
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.
- Generate a login-path using
mysql_config_editor
as explained here. - 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.
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.
Hi, this is great. Any chance you could add a license? :)