Bug #84698 provide a native way to report seconds behind "top master" and below
Submitted: 28 Jan 2017 9:47 Modified: 28 Jan 2017 9:48
Reporter: Simon Mudd (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.7,8.0 OS:Any
Assigned to: CPU Architecture:Any

[28 Jan 2017 9:47] Simon Mudd
Description:
Inspired by bug#84415 it seems we all want to know how far behind a slave is behind the master.  This has always been tricky.

When intermediate masters are involved seconds_behind_master only tells you the delay relative to the upstream master not the primary master which is generally more important, yet most people with a setup like this:

primary_master -> intermediate_master -> .... --> bottom_slave

actually want to know the delay behind the primary_master.  This information is not provided by MySQL, nor is the information of any delay on intermediate masters.

While there are several "solutions" to find the delay behind the primary master such as pt-heartbeat or home grown solutions it would be much more convenient to have a native way of determining this delay.

While many people using replication may only have a single level of slaves, if you distribute your servers over more than one datacentre it is likely that the replication topology is going to be deeper.  Even with this setup as more servers are involved bandwidth issues and avoiding single points of failure may make it desirable to have further intermediate masters so the desire for being able to observe intermediate master delay on a leaf node becomes more important.

How to repeat:
SECONDS_BEHIND_MASTER from SHOW SLAVE STATUS is not reliable and does not give complete information.

Suggested fix:
I would like to see the delay from the primary master and also the delay  of any intermediate masters along the way.  Delays can be triggered for a number of reasons so having this information would be good.

Any solution should also work in multi-source environments.

This would probably require injecting a periodic "heartbeat event" on primary masters with a timestamp (plus server-id/server_uuid), but modifying the event on intermediate masters by including their details and timing information along the way.

This would be similar to how what happens with emails and the SMTP Received: headers which show the path the email took to reach its destination.
[28 Jan 2017 9:48] Simon Mudd
Adjust heading to indicate that seeing the delay along a replication chain would be good.