Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a very trivial mysql master->slave setup going on between two servers. The problem is, replication is asynchronous, and this can cause issues (even on a low latency link), if the master server was to crash after a COMMIT before the replication thread from the slave was able to fetch the last bin log.

Is there anyway to force mysql to do synchronous commits so that data consistency is guaranteed in a mysql->slave relationship?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

The normal MySQL replication is necessarily asynchronous, there are no distributed locks and the master is never blocked waiting for slaves. This has advantages and disadvantages.

DRBD + heartbeat is the standard solution to avoid data loss in the case of permanent loss of the master.

You can use DRBD + heartbeat underneath normal replication which allows for read-scaling or replicas off-site as well as very high durability.

As DRBD does synchronous replication at the block level, you need the network to have very low latency to get good performance (gigE is ok, custom low latency interconnects are better if you have very high performance requirements).

share|improve this answer
add comment

MySQL 5.5 has semi-synchronous replication. Basically it guarantees that at least one slave has received the transaction before it is commited.

http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html

share|improve this answer
add comment

MySQL Cluster provides synchronous replication but the engine (NDBCLUSTER) is not suitable for a lot of use cases.

Continuent had a synchronous replication solution (Sequoia) but it is not actively developed anymore.

The de facto standard is DRBD + Heartbeat but it is non-trivial to setup.

Cheers

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.