How Does Mysql Master Slave Configuration Works

This blog covers only how does replication works. If you are looking for how to do Mysql Master Slave Replication then click here.

A) On Master DB Server-

1. Master server writes every event to binary logs. It is very lightweight activity, (these writes are buffered and are sequential), Later replicating Slave reads this data.

2. There will be no physical disk read on master in order to feed binary logs events to slaves that are running in parallel, but if a slave is few hours or days behind the master, than slave will start reading logs that were written hours or days ago, in this case disk read will occur because master may no longer have these cached.

(To see master's Replication Status Variables)

 mysql-> show master status\g

B) On Slave DB Server-

1. IO thread connects to master and they read binary log events and copies them locally on slave server in relay logs. There are 2 IO threads, one reading binary logs and another writing relay log. its very rare to have delay in replication due to copying.

* Master_log_file- Last file copied from master

* Read_master_log_pos- Logs from binary to slave will be copied until this position

2. SQL Thread reads events from relay log on slave and applies them fast.

* Relay_master_log_file- Binary logs(relay logs in real) from master, that SQL thread is working on.

* Exec_Master_Log_Pos- Master binary log position, that is being executed by SQL thread.

(See Master log files, its position, Slave running, Replication status variables)

 mysql> show slave status\G;

Note-

1.To find replication lag, We have to compare both Master's & Slave's Replication Status Variables to find Replication lag.

2. Avoid long running updates, because a sin

gle_large/ bunch_of_millions small updates will choke down the buffer. to avoid this keep max. query length to 1/5th of the tolerable replication lag.

3. To update many rows at a time, use query chopping- i.e. limiting the loop, controlling max. amount of value per batch in multiple row insert statement/ fetching data to update/delete.                                   

4. do not make slave do much work by running a long query, ex. don't use select and update together in a single query, instead have separate select and update queries.