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