Wednesday, June 21, 2006

Finding out how far behind are the slaves using Seconds_Behind_Master

When managing a large number of MySQL slaves, often one needs to view a summarized list of the seconds by which the slaves are falling behind. Here is a small script that will allow you to do just that.

#!/usr/bin/bash
cat /list/slaves | while read i ; do echo $i; mysql -h $i -e "SHOW SLAVE STATUS \G" | grep "Seconds_Behind_Master" ; done


To use it create a list of slave hosts and place it in /list/slaves or wherever you like. Then create a script /scripts/slave_behind_all and paste the above code in it.

4 comments:

Mike Kruckenberg said...

Be aware that seconds behind master only tells you the difference between when a statement was put into the relay log on the slave and when it was executed, it doesn't account for the time it takes for the statement to get from the master to the slave. If your slave isn't reading statements from the master quickly, but is executing the statements immediately your slave will report 0 seconds behind the master when in fact it might be further behind.

Anonymous said...

Here's a version that I've written that converts the seconds into a human readable form. Enjoy!

http://day32.com/MySQL/time_behind_master.sh

Matt Montgomery

Frankly Speaking! said...

You are absolutely right Mike. Thanks for pointing that out.

Frank

Frankly Speaking! said...

Thanks Matt for the URL.
Frank