Shell script to monitor MariaDB replication and send email alert about server health status nixCraft

Here is a simple shell script to notify secondary read-only replica (slave) MariaDB server health status failure via email and push notification. Master-slave data replication allows you to copy databases to multiple MariaDB servers. It is useful for backup, data recovery, load balancing, and much more. See how to configure SSL-protected MariaDB replication between a master and slave server.

The terms master and slave have historically been used in MariaDB/MySQL replication, but primary and replica terms are now preferred. Currently, MariaDB is code, and docs are removing those references and replacing them with primary and replica. Untile those variables updates in the core software – this script will use historical terms.

In replication monitoring, mainly script look into three things “Slave_IO_Running,” “Slave_SQL_Running,” and “Seconds_Behind_Master” (the lag between master and slave) and last error code.

Requirements for shell script about MariaDB replication monitoring

  1. Obviously, you need working MariaDB cluster.
  2. Working email system to send outgoing email. See how to setup Postfix and how to configure AWS SES with Postfix MTA.
  3. Pushover free or paid API access to push notifications to your Android or Apple iOS device. See, how to push/send message to iOS and Android from Linux CLI.
  4. Bash shell

Shell script to monitor MariaDB replication and send email alert about server health status

Here is my sample shell script to notify secondary read-only replica MariaDB server health status:

#!/usr/bin/env bash
# Set mysql/mariadb login info  # default is root but I set up admin user with less permission 
_user="admin" _pass="MariaDB_ADMIN_USER_PASSWORD"
_host="localhost" _out="/tmp/mysql-status.$$"
_errs=()
_m_vars='Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Errno'
_is_error_found="false"
_alert_limit=60 # Email settings 
FROM="smtp.out@cyberciti.biz"
TO="YOU@gmail.com" # Grab keys and bash function for pushover API 
source ~/bin/cli_app.sh # Send html email to "$TO" from "$FROM" 
html_email(){ local SUBJECT="$HOSTNAME - mariadb/mysql slave server error(s)" local MSG=("$@") ( echo "From: $FROM" echo "To: $TO" echo "Subject: $SUBJECT" echo "Content-Type: text/html" echo echo "<html>" echo "<p>Hostname : $HOSTNAME</p>" echo "<p>Date : $(date)</p>" echo "<h4>Errors:</h4>" echo "<pre>" ( IFS=$'n'; echo "${MSG[*]}" ) echo "</pre>" echo "<h4>SQL query raw values</h4>" echo "<pre>" echo "$(<${_out})" echo "</pre>" echo "<p>-- ${0}</p>" echo "</html>" echo ) | sendmail -f "$FROM" "$TO" # Push message to my mobile device  push_to_mobile "$0" "$SUBJECT
 Hostname : $HOSTNAME
 Date: $(date)
 Errors:
 $( IFS=$'n'; echo "${MSG[*]}" | sed -e 's/<p>//' -e 's/</p>//' )

 SQL query raw values:
 $(<${_out})
 " [ -f "${_out}" ] && rm -f "${_out}"
} ## main ##
mysql -u "${_user}" -h "${_host}" -p"${_pass}" -e 'SHOW SLAVE STATUS G;' | grep -E -i "${_m_vars}" > "${_out}" || html_email "Can't connect to local mysql/mariadb server." IFS='|'
for v in $_m_vars
do value=$(awk -F':' -v m="${v}:" '$0 ~ m { gsub(/ /, "", $2); print $2 }' "${_out}") if [ "$v" == "Slave_IO_Running" ] then [[ "$value" != "Yes" ]] && { _errs=("${_errs[@]}" "<p>The I/O thread for reading the master's binary log not found ($v: $value)</p>"); _is_error_found="true"; } fi if [ "$v" == "Slave_SQL_Running" ] then [[ "$value" != "Yes" ]] && { _errs=("${_errs[@]}" "<p>The SQL thread for executing events in the relay log is ($v: $value)</p>"); _is_error_found="true"; } fi if [ "$v" == "Seconds_Behind_Master" ] then [ "$value" == "NULL" ] && { _errs=("${_errs[@]}" "<p>The slave server is in undefined or unknown state ($v: $value)</p>"); _is_error_found="true"; } if [[ $value =~ ^[0-9]+$ ]] then [ "$value" -gt $_alert_limit ] && { _errs=("${_errs[@]}" "<p>The Slave server is behind the master for at least $_alert_limit seconds ($v: $value)</p>"); _is_error_found="true"; } fi fi if [ "$v" == "Last_Errno" ] then [ "$value" -ne 0 ] && { _errs=("${_errs[@]}" "<p>The slave SQL thread receives an error ($v: $value</p>)"); _is_error_found="true"; } fi done # Send email and push message when error found 
[ $_is_error_found == "true" ] && html_email "${_errs[@]}" # Cleanup 
[ -f "${_out}" ] && rm -f "${_out}"

I set up a cron job as follows to monitor my MariaDB slave server:

*/5 * * * * root /root/bin/check-mysql-slave-server-health

Here is how email looks:
Script for MySQL Replication Monitoring and Send Email Alert

Script for MySQL Replication Monitoring and Send Email Alert
Pushover notification for my mobile phone:
Shell script to notify secondary read-only replica MariaDB server health statusShell script to notify secondary read-only replica MariaDB server health status

Summing up

MariaDB replication is tremendous as it provides high availability for reading data. Of course, you need to modify the application to send reading requests to replicas and write requests to the primary server. However, I no longer use this model. Instead, I recommend setting up a three-node Galera cluster, a generic synchronous multi-primary (master) replication library for transactional databases used in MySQL and MariaDB. I am sharing this script if someone is still using an older model.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics via:

Posted by Contributor