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

echo ""
echo "Checking status of mysql servers"
echo "================================"
echo ""
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)
 echo $host
 ssh -l root $host "mysql -u root -p$mysqlmaster -Bse 'SHOW SLAVE STATUS\G'" > /tmp/mysql-slave-status-$host.txt

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

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

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:

And mysql-parameters


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.




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