MySQL SHOW SLAVE STATUS Explained

Sometimes I don't have to deal with MySQL slaves for months and again I forgot which entry means what.

Here is a SHOW SLAVE STATUS explained  while catching up (I just restarted the slave after an error)




On the slave:
10:24 [vega]:> show slave status\G
...
      Master_Log_File: vega20-bin.000740  just current log and pos on the master
  Read_Master_Log_Pos: 3396922             == show master status
                                           (sometimes takes time to be refreshed)

       Relay_Log_File: vega21-relay-bin.000074 relay file on the slave we're
                                               currently applying

        Relay_Log_Pos: 274423937          position in bytes in the relay file.

Relay_Master_Log_File: vega20-bin.000727  corresponding log file on the master to
                                          what we're applying 
                                          (727 -> 740  is left to be done)
...
  Exec_Master_Log_Pos: 274423781          position on master log corresponding to
                                          what we're applying
...
Seconds_Behind_Master: 110997             estimated time to complete applying,
                                          here 727->740
Files on slave:
        219 Sep 10 04:08 vega21-relay-bin.000076
     510344 Sep 10 04:08 vega21-relay-bin.000075
 1073741921 Sep 10 04:08 vega21-relay-bin.000074 <- applying this one
     193266 Sep 10 07:18 vega21-relay-bin.000079  (a bit later, are deleted)
...
  196984797 Sep 11 09:03 vega21-relay-bin.000112
    3397078 Sep 11 10:24 vega21-relay-bin.000114 <- last redo info we copied from the master (data from master's log is copied to a slave relay log. Size may not be same as master ex:if we stop/start etc..)

Files on the master:
 1073904414 Sep  7 09:59 vega20-bin.000720
 1073774868 Sep 10 02:21 vega20-bin.000721
...
 1074251834 Sep 10 04:07 vega20-bin.000727<- applying data which is here 
 1073934723 Sep 10 07:17 vega20-bin.000728  (copied on slave in relay log 74)
 1073993761 Sep 10 07:27 vega20-bin.000729
...
 1091352292 Sep 11 09:02 vega20-bin.000739
    3396922 Sep 11 10:23 vega20-bin.000740<- current log written by the master




Status on the master:

10:24 [(none)]:> show master status;
+----------------------------+----------+--------------+------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+----------+--------------+------------------+
| mysql-clappdb50-bin.000740 |  3396922 |              |                  |
+----------------------------+----------+--------------+------------------+



Other notes:

11:00 [vega]:> show processlist;
+--+-----------+----+----+-------+------+--------------------------------+------+
|Id|User       |Host|db  |Command|Time  |State                           |Info  |
+--+-----------+----+----+-------+------+--------------------------------+------+
| 5|system user|    |NULL|Connect|  6979|Waiting for master to send event|NULL  |
|69|system user|    |NULL|Connect|111664|Reading event from the relay log|NULL  |

Waiting for master to send event: currently the Master isn't writing much, so nothing to send to the slave relay log

Reading event from the relay log: means we're reading the relay file. If this is seen often, it means we're spending more time reading than applying... (slow I/O!)

Sometimes we see the action replicated on the slave:

|97|system user |    |STOCKS|Connect|101289| NULL      |TRUNCATE TABLE `TOTA01`|