4

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?

2

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

6

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

1

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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