CODEWITHSUNDEEP
mysqldatabase-replication
Eric Leschinski
Eric Leschinski

Reputation: 153842

MySQL get read replica lag

I have a MySQL server configured as a read-only read replica.

How to I find out the number of seconds of lag between it and the database being copied?

I tried this:

SHOW STATUS LIKE 'seconds_behind_master';

But that syntax is not supported. I'd like a one-liner SQL to retrieve this value. Can it be done?

Upvotes: 1

Views: 999

Answers (1)

Eric Leschinski
Eric Leschinski

Reputation: 153842

How to get the read replica lag for a mysql server configured as a read only replica.

http://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html

show slave status

This field Seconds_Behind_Master is an indication of how “late” the slave is.

I was able to write a little PHP script which grabs just the one value:

$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

Here is a little bash command which prints the contents of show slave status for you to parse:

mysql --host=your_host -u your_username --password=your_password your_database_name -e "show slave status"

Upvotes: 1

Related Questions