A simple shell script to check MySQL slave replication status in a cluster


We use MySQL in a master-slave configuration, with one of the slaves designated to take over as the master should anything nasty happen to the master.

Before switching to the new master, we used to flip onto each slave and check the replication status. This got to be quite stressful and prone to error, so I’ve written a small shell script to help with this. Comments welcome! Enjoy!

As root, put this script in /usr/local/bin, call it (say) mysql-get-slave-server-status

#!/bin/sh
echo ""
echo "Checking status of mysql servers"
echo "================================"
echo ""
mysqlmaster=`mysql_master_password`
parameterFile=/etc/cluster/mysql-parameters
mysqlServerFile=/etc/cluster/mysql-servers
echo "SSH-ing onto the servers listed in $mysqlServerFile"
echo "Running SHOW SLAVE STATUS on each box"
echo "Storing results in /tmp"
echo ""
for host in $(cat $mysqlServerFile)
do
 echo $host
 ssh -l root $host "mysql -u root -p$mysqlmaster -Bse 'SHOW SLAVE STATUS\G'" > /tmp/mysql-slave-status-$host.txt
done

echo ""
echo "Extracting data based on list in $parameterFile"
echo "================================================================================"
echo ""

for parameter in $(cat $parameterFile)
do
 echo $parameter
 echo "------------------------------"
 for host in $(cat /etc/cluster/mysql-servers)
 do
 value=`cat /tmp/mysql-slave-status-$host.txt | grep $parameter | awk '{print \$2}'`
 echo $host: $value
 done
 echo ""
done

The box this runs on will need to be able to ssh onto the other servers without needing a password. Don’t forget to put it on your failover box as well!

You will need to tweak mysqlmaster, parameterFile, and mysqlServerFile settings and then create the mysql-parameters and mysql-servers files to cover your cluster

For example, mysql-servers:

10.189.1.1

10.189.1.2

10.189.1.3

And mysql-parameters

Master_Host
Slave_IO_Running
Slave_SQL_Running
Master_Log_File
Read_Master_Log_Pos
Relay_Log_File
Relay_Log_Pos
Relay_Master_Log_File

Good luck. Please note I’m not able to provide tech support (sorry!). But if you do make an improvement, let me know. You might be able to test if the mysqld process is running on the box first, for example.

 

 

Advertisements

About saasmd

I am an experienced software-as-a-service entrepreneur, based in London, UK. I love building interesting software businesses. My current venture is StorIQ, a platform to help bricks-and-mortar retailers manage their operations more effectively. This blog is a space to share low-level techie stuff that I think other people will find useful.
This entry was posted in Techy Stuff and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s