MySQL Replication Lag Monitoring Script
Master-slave replication is widely used in production. Monitoring the
replication lag is a common and critical task. Typically, we are able to
get the real-time difference between the master and the slave by
periodically checking the Seconds_Behind_Master
variable.
According to link
Seconds_Behind_Master: this field shows an approximation for difference between the current timestamp on the slave against the timestamp on the master for the event currently being processed on the slave.
Get the Seconds_Behind_Master
value is easy, just run
show slave status
on the slave node.
However, this command displays too much information so that it's hard to
find the key info. If you want to select only
Seconds_Behind_Master
from mysql system table, it's a
nightmare to find a worked
solution for your mysql version.
Here the simple script helps:
#!/bin/bash
# Fill the following variables
host= # slave node!
user=
password=
while true;
do
seconds=`mysql -h $host -u $user -p$password -e 'show slave status\G' 2>/dev/null |grep Seconds`
echo `date "+%Y-%m-%d %H:%M:%S"`$seconds
sleep 1
done
Sample output of the script:
...
2020-11-11 17:17:11 Seconds_Behind_Master: 89
2020-11-11 17:17:12 Seconds_Behind_Master: 89
2020-11-11 17:17:13 Seconds_Behind_Master: 91
2020-11-11 17:17:14 Seconds_Behind_Master: 90
2020-11-11 17:17:15 Seconds_Behind_Master: 91
2020-11-11 17:17:16 Seconds_Behind_Master: 91
...