MySQL - Master Slave Replication and It’s Impact

What is Master Slave Replication ?

The MySQL replication process allows us to maintain multiple copies of MySQL data. So our Data Leakage and Loss issue will get resolved here. All data in the master server is synced to Slave servers in an automated process and we can easily promote Slave to a Master for commit operations.

The main role of replication is to spread read and write workloads across multiple servers for easy scalability and also we can use it for Real time Data backup purpose.

Why we have implemented Master Slave Replication ?

Previously we had a single master database for LMS/ Onboarding which performs all the operations such as Read, Write , Delete operations and used by all our resources such as Dev team, IT Tech Support and Data analyst team to sync the Data in Zoho and available to our Business Team to read those data in real time and to manage the Analytics.

Due to heavily usage of Those data, and daily basis incremental leads/ on boarded clients in our system, our database usage was getting high (as multiple operations i.e. Read / Write at the same time) and our system goes down each time. It impacts our LMS and Onboarding system due to which our operation team work gets impacted.

Also we were not maintaining the real time database backup for our Both LMS and Onboarding system, so there were chances of Data loss in case of any of our LMS / on boarding server getting crashed.

In order to avoid this, we have initially implemented Master Slave Replication for LMS.

The main purpose to implement Master Slave Replication was :

• Implement a real time backup process to achieve the Zero Data Leakage & Loss.
• Slave server will be use to serve the data to Business / Operational / Internal Team (Only read access) which will distribute load on slave server and our master server performance will improvise.
  
  

What we have done for implementation ?

By default, replication is asynchronous and slaves do not need to be connected permanently to receive updates from the master. It is primarily used to read access on multiple servers for scalability and also used for fail over. Hence we have implemented this approach.

There are two more approach of Replication:

• Synchronous

    In fully synchronous replication, when a source commits a transaction i.e when any new operation is triggered on source, all replica's have also committed the transaction before the source returns to the session that performed the transaction. In Fully synchronous replication, there might be chances of fail over from the source to any replica is possible at any time. The drawback of fully synchronous replication is that there might be a lot of delay to complete a transaction and it may impact on master server operations such as Insert / Update / Delete.

• Semi-synchronous

    Semi-synchronous replication falls between asynchronous and fully synchronous replication. In this replication, the source waits until at least one replica has received and logged the events, and then commits the transaction in case we have multiple replica configurations. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas. Semi-synchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica and this is the plus point of this replication.

Compared to asynchronous replication, semi-synchronous replication provides improved data integrity because there are no chances of data loss even if the source crashes, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semi-synchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.

But at the initial stage, our main focus was on Real time database backup, hence we have selected the asynchronous approach. We will keep this functionality under observation till the next 5 days. In between if we face any Data integrity related issues, we will plan for a Semi synchronous approach.

Action Plan :

• Remove On boarding Instances (for e.g. 127.0.0.1 & 127.0.0.2) from LB to stop requests. (Purpose new request should not come to database)
• Stop MySQL service.
• Take backup of MySQL. (For rollback purpose)
• Make necessary for replication in MySQL.
• Start MySQL service
• Add instances in LB.
• Take a hot backup of MySQL. ( Tool Percona xtrabackup )
• Sync backup date to slave. (for e.g. 127.0.0.3)
• Initiate replication on slave.
• Testing of all applications related to onboarding. (by development team)
• Confirmation from development for BAU.
  

Roll back plan ( In case of replication failure) :

• Remove Onboarding Instances (for e.g. 127.0.0.1 & 127.0.0.2) from LB to stop requests.
• Stop MySQL service of (for e.g. 127.0.0.4).
• Restore MySQL backup data to (for e.g. 127.0.0.4).
• Start MySQL service.
• Add instances in LB.
• Testing of all applications related to onboarding. (by development team)
• Confirmation from development for BAU.

What Was The Impact?

Before implementing Master-Slave Replication :

We hadn’t any real time database backup utility for LMS and Onboarding as well.

After implementing Master-Slave Replication :

We can easily use our Slave server to  use to serve the data to Business / Operational / Internal Team (Only read access) which will distribute load on slave server and our master server performance will improvise.

         
If we compare both Master and Slave Server details, we are getting a same count for both tables in both server.

It means, we have achieved the Real time Database backup syncing using MySQL- Master Slave Replication.