Monitor Dual Master Mysql Replication


Here are shell script for monitoring replication, resetting replication if failed, logging if replication occured.
Write a file name mysqlslave.sh on /tmp directory.

#!/bin/sh

user=mysql_root_username
passwd=mysql_root_passwd

repuser=mysql_replica_username
reppasswd=mysql_replica_passwd

GO=1
while [ $GO=1 ]; do

rf=$(mktemp)

/usr/local/mysql/bin/mysql -u$user -p$passwd -e "show slave status \G" > $rf 2>&1

repl_IO=$(cat $rf|grep "Slave_IO_Running"|cut -f2 -d':')
repl_SQL=$(cat $rf|grep "Slave_SQL_Running"|cut -f2 -d':')

if [ "$repl_IO" != " Yes" -o "$repl_SQL" != " Yes" ] ; then
/usr/local/mysql/bin/mysql -u$user -p$passwd -e "stop slave"
/usr/local/mysql/bin/mysql -u$user -p$passwd -e "reset slave"

rg=$(mktemp)

/usr/local/mysql/bin/mysql -hserverb -u$repuser -p$reppasswd -e "show master status \G" > $rg 2>&1

filemaster=$(cat $rg|grep "File"|cut -f2 -d':'|cut -f2 -d' ')
positionmaster=$(cat $rg|grep "Position"|cut -f2 -d':')

/usr/local/mysql/bin/mysql -u$user -p$passwd -e "CHANGE MASTER TO MASTER_LOG_FILE='$filemaster', MASTER_LOG_POS=$positionmaster"

/usr/local/mysql/bin/mysql -u$user -p$passwd -e "start slave"

/bin/date >> /tmp/mysqlslave.log 2>&1

rm $rf $rg
fi
sleep 30
done

You can change duration of monitor by changing value of sleep.
You can check if any resetting slave log at /tmp/mysqlslave.log

Popular posts from this blog

Howto configure boot device order on ILOM

SAN Switch Config Command

Howto cstm on HP-UX