How to access Seconds_Behind_Master from SQL

Question

I would like to have access to the Seconds_Behind_Master field, (as returned by SHOW SLAVE STATUS) from inside a stored procedure.

I can't figure out how to get its value inside a variable. None of the usual SET/SELECT syntax seems to work.

Is there a way to do that?

Answer 1

Just for the record: it has turned out to be possible to open a cursor for SHOW statements. This allows to parse the output and work with it inside a stored procedure.

Answer 2

From what I see in the recent docs and MySQL Bug#37187 there does not seem to be any way but SHOW SLAVE STATUS to get to this information.

Answer 3

PHP can grab all the fields of the show slave status into a hashmap, like this:

$db = new pdo(
    "mysql:host=your_database_hostname;dbname=your_database_name",
    'your_username', 'your_password');

$sql = 'show slave status';

$query = $db->query($sql);
$res = $query->fetchall();

foreach($res as $item){
    print ">" . $item["Seconds_Behind_Master"];
}

Which prints 0 seconds because everything is up to date:

>0

I tried for an hour to create a stored procedure to do this. I recommend you don't waste your time.