Problem: Our Replication was going behind on a particular time even there was not much (un-usual) updation or load on server. To know that exact issue when I tried to know slave status at that particular time and executed “show slave status” command on server. I could not get the results and it took too much time but could not execute.
Reason: After trouble shooting, below facts came to notice-
- mysql partition has 60 GB space on server.
- Our DB Size was 30 GB (earlier it was 22 GB at the time of server space requirement and increased now to 30 GB).
- We were taking its full binary backup in a backup folder in same partition, which was also 30 GB.
- 30 GB db size + 30 GB backup was equal to total size allocated to this partition. So now there was no space in this partition, so data was not replication on slave server and also show slave status command was not executing.
- As after taking backup we archive it to another disk (attached space) and then remove backup from server, so as backup is removed from server after archiving data then server again got space and every thing was fine.
- So when we monitor server space as a regular activity then there was no issue but problem was during this backup time.
Solution: We increased server space for mysql partition and now every thing is fine.
Note: We are taking first backup on same server due to some issues at the time of backup on another attached space also where was sufficient space was available only in mysql partition otherwise good idea is, to take backup in different partition than mysql partition.