mysql master-slave setup with synchronous replication

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?

Solution 1:

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).

Solution 2:

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

Solution 3:

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