Mysql – thesql master-slave setup with synchronous replication

MySQLmysql-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?

Best Answer

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

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

How to monitor thesql replication slave

Nagios has several MySQL replication monitors. These should do the trick.

Best Answer

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

How to monitor thesql replication slave

Related Topic