Solved

MySQL Semi-Synchronous vs Synchronous replicaton when slave fails

Posted on 2016-07-19
2
48 Views
Last Modified: 2016-07-20
I understand that in MySQL Semi-Synchronous replication, the transaction is not seen as committed before  a receipt is received back from the slave to the master given a certain time out period. Thus if the connection times out to the slave, the master still replies to the client and then replicates to the slave later when it is online?

Is that the same scenario with full synchronous replication, or does the slave have to be online all the time in order for the master to acknowledge the transaction made by the client when the slave has committed the transaction?
0
Comment
Question by:itnifl
2 Comments
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
Comment Utility
MySQL defaults to asynchronous replication.  That means a client orders a master to commit a transaction.  The master writes the transaction to the log, and responds with "OK, done!"  The client can now go on about its business.  The slaves may/may not receive that information in a timely manner, but their involvement is not up for consideration by the master.  It is possible, then, to have a failover to a slave with incomplete knowledge of past transaction.  This system is great for high availability systems, but not so much for guaranteed data integrity.

In synchronous replication, the slaves are given greater weight.  All active, synchronous slaves must report success on a transaction before the master returns to the client.  Different systems may have different definitions of what an "active" slave is, but the configured slaves must generally be available for every transaction.  This is the gold standard for data integrity, since transactions are guaranteed to be at all slaves 100% of the time.  However, it sucks for availability, since waiting for confirmations can result in delays.  How a system handles excessive delays is a very important question, and can vary widely.

MySQL's semi-synchronous replication is a lot like <winkwink>synchronous<winkwink> replication.  In this case, the master receives and resolves the commit, then replicates the transaction to all semi-sync'd slaves.  But, instead of waiting for all to confirm, it only waits for one.  Once it receives at least one confirmation, it returns to the client and calls it a day....except when it doesn't.  There is a timeout value for how long the master will wait for a confirmation.  If that timeout expires, the whole system drops back to asynchronous replication until the slaves catch up.  When all the slaves report being up-to-date, the system will switch itself back over to semi-sync.  

MySQL's strategy with semi-sync is to provide a "best of both worlds" kind of approach.  After all, data integrity is incredibly important in many applications.  But, sometimes you just need to get stuff done.  MySQL's approach is basically saying, "This server will try its best to be synchronous.  If that becomes a problem (and YOU tell me how long before it does), we'll switch over to high availability and prayer."
0
 
LVL 2

Author Closing Comment

by:itnifl
Comment Utility
Thanks.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now