Seconds_Behind_Master may be zero, but you should be looking at other parts of the
SHOW SLAVE STATUS\G because it can tell you something about why it's zero. Let's discuss three(3) aspects that will shed some light on Replication's
There are occasions when a group of SQL commands are executed individually in the same one-second timeframe. Why does this happen on a Slave?
Keep in mind that the Master may have executed many INSERTs, UPDATEs, and DELETEs, in parallel. However, the binary logs records them as serialized events. An event will include the TIMESTAMP of the SQL command followed by the SQL Command.
If 30 DML commands each took 1 second to complete on a Master at
2014-05-04 10:10:01), they get recorded in the binary logs with the timestamp
On the Slave, when each command is read one-by-one, the timestamp will be
1399212601 for each command. Yet, real-time clock on the server will be steadily increasing. Seconds_Behind_Master is nothing more than
UNIX_TIMESTAMP(NOW()) - 1399212601. This is the manifestation of Replication Lag.
There are occasions when a group of SQL commands are executed as series of row changes.
This could easily happen with row based replication (binlog_format is set to
ROW instead of
STATEMENT). Individual row changes bloat binary logs and result in same replication lag as stated in
Here are three posts where I discuss how Replication handles
LOAD DATA INFILE
When you execute
LOAD DATA INFILE on a Master, the Master will copy the entire CSV file as binlog events into the Binary Logs. The next binlog event recorded is the
LOAD DATA INFILE command itself.
The Slave will see a binlog event in its relay logs that will alert the Slave to read the entire CSV file. Then, when it see the
LOAD DATA INFILE, it executes it against the temp CSV file in created.
During the manifestation of the temp CSV file, the
Seconds_Behind_Master will read 0. Then, when the entire CSV file is made and the LOAD DATA INFILE begins execution, the
Seconds_Behind_Master will just increase by 1 or 2. It will do a punctuated jump to hundreds or even thousands of seconds.
Binary Logs are are to handle group commits for better performance. Notwithstanding, you should looking at the following
Master_Log_File : Binary Log of the Last Binlog Event recorded on the Master read on the Slave
Read_Master_Log_Pos : Position of Last Binlog Event that was read from the Master and written to Relay Log on the Slave
Relay_Master_Log_File : Binary Log of the Last Binlog Event that was read from Master and was executed on Slave
Exec_Master_Log_Pos : Position of the Last Binlog Event that was read from Master and was executed on Slave
Relay_Log_Space is changing, then the IO Thread is reading just fine from the network. You can also see that by watching either
Read_Master_Log_Pos or both changing.
Exec_Master_Log_Pos is changing, then the command are begin executed on the Slave.
Therefore, once Seconds_Behind_Master is 0 for a long time, look for changes in these five fields in the
SHOW SLAVE STATUS\G.