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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#!/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:

1
2
3
4
5
6
7
8

...
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
...