Replication - High Performance MySQL - page 486

On the master, you can use the SHOW MASTER STATUS command to see the master's current
binary log position and configuration (see the section “Configuring the Master and
Replica” on page 452 ). You can also ask the master which binary logs exist on disk:
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000220 | 425605 |
| mysql-bin.000221 | 1134128 |
| mysql-bin.000222 | 13653 |
| mysql-bin.000223 | 13634 |
+------------------+-----------+
This information is useful in determining what parameters to give the PURGE MASTER
LOGS command. You can also view replication events in the binary log with the SHOW
BINLOG EVENTS command. For example, after running the previous command, we cre-
ated a table on an otherwise unused server. Because we knew this was the only state-
ment that changed any data, we knew the statement's offset in the binary log was 13634,
so we were able to view it as follows:
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000223' FROM 13634\G
*************************** 1. row ***************************
Log_name: mysql-bin.000223
Pos: 13634
Event_type: Query
Server_id: 1
End_log_pos: 13723
Info: use `test`; CREATE TABLE test.t(a int)
Measuring Replication Lag
One of the most common things you'll need to monitor is how far behind the master
a replica is running. Although the Seconds_behind_master column in SHOW SLAVE STA
TUS theoretically shows the replica's lag, in fact it's not always accurate, for a variety of
reasons:
• The replica calculates Seconds_behind_master by comparing the server's current
timestamp to the timestamp recorded in the binary log event, so the replica can't
even report its lag unless it is processing a query.
• The replica will usually report NULL if the replication processes aren't running.
• Some errors (for example, mismatched max_allowed_packet settings between the
master and replica, or an unstable network) can break replication and/or stop the
replication threads, but Seconds_behind_master will report 0 rather than indicating
an error.
• The replica sometimes can't calculate the lag even if the replication processes are
running. If this happens, the replica might report either 0 or NULL .