MySQL Tutorial – Understanding The Seconds Behind Master Value
In a MySQL hosting replication setup, the parameter Seconds_Behind_Master (SBM), as displayed by the SHOW SLAVE STATUS command, is commonly used as an indication of the current replication lag of the slave. In this blog post, we examine how to understand and interpret the MySQL Seconds Behind Master value in various situations.
Possible Values of Seconds Behind Master
The value of SBM, as explained in the MySQL documentation, depends on the state of the MySQL slave in general, and the states of MySQL slave SQL_THREAD and IO_THREAD in particular. While IO_THREAD connects with the master and reads the updates, SQL_THREAD applies these updates on the slave. Let’s examine the possible values of SBM during different states of the MySQL Slave.
When SBM Value is Null
SBM is always NULL if your slave is stopped, or your SQL Thread is stopped (or not running).
SBM will also be NULL if the IO Thread is stopped, provided the SQL Thread has already processed all events from the relay log. A sample output of SHOW SLAVE STATUS (trimmed to show only values of interest) demonstrates this:
Slave_IO_State:
Master_Host: 172.19.0.13
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_UUID: 23b326b1-a452-11e8-91ca-000d3a065e8e
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Retrieved_Gtid_Set: 23b326b1-a452-11e8-91ca-000d3a065e8e:818-389213
Executed_Gtid_Set: 23b326b1-a452-11e8-91ca-000d3a065e8e:1-389213
When SBM Value is Zero or Positive
SBM is going ...
Read More on Datafloq
Possible Values of Seconds Behind Master
The value of SBM, as explained in the MySQL documentation, depends on the state of the MySQL slave in general, and the states of MySQL slave SQL_THREAD and IO_THREAD in particular. While IO_THREAD connects with the master and reads the updates, SQL_THREAD applies these updates on the slave. Let’s examine the possible values of SBM during different states of the MySQL Slave.
When SBM Value is Null
SBM is always NULL if your slave is stopped, or your SQL Thread is stopped (or not running).
SBM will also be NULL if the IO Thread is stopped, provided the SQL Thread has already processed all events from the relay log. A sample output of SHOW SLAVE STATUS (trimmed to show only values of interest) demonstrates this:
Slave_IO_State:
Master_Host: 172.19.0.13
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_UUID: 23b326b1-a452-11e8-91ca-000d3a065e8e
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Retrieved_Gtid_Set: 23b326b1-a452-11e8-91ca-000d3a065e8e:818-389213
Executed_Gtid_Set: 23b326b1-a452-11e8-91ca-000d3a065e8e:1-389213
When SBM Value is Zero or Positive
SBM is going ...
Read More on Datafloq
Comments
Post a Comment