MySQL: High-Availability - Replication

MySQL being one of the best open-source light weight database, comes with the in-built data replication feature. In fact, this feature made MySQL more and more popular in the data world.

Replication in MySQL do mean – The data flow from one server to the other remote server through network, due to which, both of them will be identical to each other. It’s basically having an exact copy of data at a remote place, while, the data being updated dynamically with respect to the source.

The MySQL Replication includes below terminologies:

•         Master – The source server from where the data is being replicated. Applications usually pointed to this server for write operations.
• 
  
•        Slave – This is the destination for replication. Slave is the actual remote mirror copy of Master server.

o   I/O Thread – This is one of the basic component of replication. This thread is responsible for reading the Master’s Binary log content and write it into the Slave’s Relay log.

The performance of this thread is the key factor of Replication speed and it solely depends on the Network Bandwidth.

o   SQL Thread – This is the other important replication component, which is responsible for reading Slave’s Relay logs and execute  them into the Slave Databases. This is always slower, compared to I/O thread and this decides the Delay in Replication/Replication Lag.

•        Binary Logs – These binary files logs every committed changes on Master. Every modification on the Master server is being written to these files, which are used by slaves for replication. And, also they are used for recovery purposes.
• 
  
•         Relay Logs – These are the  Slave’s version of Binary Logs. They are generated only for Slave servers.

Below is the pictorial representation of MySQL Replication with Master-Slave Hierarchy :

Please note that there can be Multiple Slaves connected to Single Master. However, that doesn’t affect Master’s performance.

There are 3 types of replication MySQL supports:

·        Asynchronous Replication

This is MySQL Default. Any flavor of MySQL should support this type of replication. Asynchronous Replication is contrast to Synchronous Replication.

Here, Master sends the committed transactions to it’s Binary Logs and DO NOT wait for the Slave to read this data.

So, while using this type, we can’t be 100% sure that all the data from Master is replicated to Slave!

•         Semi-synchronous Replication

MySQL 5.5 or above supports this type of Replication. In this type, the Master sends the committed transactions to the Binary Logs and waits for the acknowledgement from at least one of the Slaves that the replicated data is received.

If no Slaves returns an acknowledgement, the Master switch back to Asynchronous mode after timeout, until at least one of the Slaves synchronized.

Using this type of replication ensures the replication is up-to-date, but, it slows the performance.

•         Synchronous Replication

This type of replication is not available with the normal version of MySQL. This is a part of MySQL Cluster.

In this type , the Master waits until all the Slaves receive and commit the data.  This is much more costlier w.r.t. performance.

Please note that, MySQL Replication is not complete High Availability solution. Because, if the Master goes down, the Slave should be made as new Master , Manually. 

This leads opts for downtime until the manual process completes!