mysql-replication

# HOW REPLICATION WORKS

IO_THREAD connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. 
SQL_THREAD reads events from a relay log stored locally on the replication slave and then applies them as fast as possible. 

There could be two reasons for delay in replication: 
1. IO_THREAD delay
2. SQL_THREAD deplay

# HOW TO INDENTIFY REPLICATION LAG

To identify the slave replication lag. We need to run following command on slave. 
`> SHOW MASTER STATUS;`
`> SHOW SLAVE STATUS;`

To execute above queries, user need either SUPER or REPLICATION CLIENT privilege. 

Important fields to look:
In the master status output
- File, Position: Coordinates of the master eg. (mysql-bin.018196, 15818564) 
In the slave status output
- (Master_Log_file, Read_Master_Log_Pos):  Coordinates in the master binary log indicating how far the slave I/O thread has read events from that log. eg. (mysql-bin.018192, 10050480)
- (Relay_Master_Log_File, Exec_Master_Log_Pos): Coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log. eg. (mysql-bin.018192, 5395871) 
- Seconds_Behind_Master: Approx number of seconds that the slave SQL thread is behind processing the master binary log. 


# HOW TO MITIGATE lag

Above information can help us to identify the reason of delay. 
- In general, SQL_THREAD lag = Read_Master_Log_Pos – Exec_Master_Log_Pos 
- IO_THREAD is behind because of slow network between master/slave. 
- using row based binlog format and database table missing primary key or unique key 

FOR IO_THREAD lag 
- enabling slave_compressed_protocol to reduce bandwidth
  
FOR SQL_THREAD lag 
- enable log_slow_slave_statements and then identify slow queries and create indexes
- using row based binlog format and your database table missing primary key or unique key then slave will scan all rows of the table for applying changes.