I decided to use some of today to resolve an issue with MySQL Slave setup, where statements fail for various reasons.
I could ignore the statements that fail – but that could be potentially dangerous – leaving with no slave backup at all.
The next best option to ensure the database is up to date is simple, monitor mysql for slave errors, which is what I do now – on my home server, I have it querying the 4 services, and checking if the two “Yes” options are ever “No”.
I created the script to run in bash, I usually heavily use PHP for all sorts of tasks (yep, many years ago, I did a PHP script for simple ping monitoring, and just stuck with it).
Using bash doesn’t require as many resources (not that PHP is resource intensive).
The script is probably pretty simple, but it was a bit of a learning curve for me (with my minimal bash experience).
/usr/sbin/mysql_monitor:
#!/bin/bash
EMAIL=”admin@email.address”
HOSTS=”192.168.x.x 192.168.x.x x.x.x.x x.x.x.x”
while :
do
for host in $HOSTS;
do
result=`mysql -h $host -u root –password=x –execute=”SHOW SLAVE STATUS\G” 2>&1`
slave_result=`printf “%s\n” “$result” | grep -i running`
connect_result=`printf “%s\n” “$result” | grep -i Error`
if [[ $slave_result == *No* ]]
then
echo “$host – $result” | mail -s “SLAVE OUT OF SYNC” “$EMAIL”
fiif [[ $connect_result == *connect* ]]
then
echo “$host – $result” | mail -s “MySQL Connection Issue” “$EMAIL”
fi
done
sleep 1800
done
HOSTS is a variable which contains the IPs of the hosts to be monitored. EMAIL is self explanatory.
The script sleeps for 30 minutes so that it’s not hammering, yet will check more often then my random every so often, sometimes weeks between follow up.
It’s added bonus is it’ll also tell me if a MySQL instance isn’t running by chance to ensure I am alerted to that.
I created an init script too to make sure it runs on startup on my home CentOS box.
/etc/init.d/mysql_monitor:
#!/bin/sh
# chkconfig: 2345 95 20
# description: MySQL Monitor
# Monitors MySQL Slave Status
# processname: mysql_monitor
APP=/usr/sbin/mysql_monitor
PIDFILE=/tmp/mysql_monitor.pid
case “$1” in
start)
echo -n “Starting $APP: ”
$APP & pid=$!
echo $pid > $PIDFILE
echo “Done.”
;;
stop)
echo -n “Stopping $APP: ”
PID=`cat $PIDFILE`
kill -9 $PID
rm -f $PIDFILE
echo “Done.”
;;
restart)
echo -n “Restarting $APP: ”
PID=`cat $PIDFILE`
kill -9 $PID
rm -f $PIDFILE
sleep 1
$APP & pid=$!
echo $pid > $PIDFILE
echo “Done.”
;;
esac
exit 0
I’ll have to make more of my monitoring tools run using init scripts and rely less on cron.