Which binlog format to use for MySQL Replication?

Solution 1:

Statement-based replication is the fastest and most compact, but in some circumstances it can produce different (non-deterministic) results on slaves than on the master, resulting in inconsistency. An example might be:

UPDATE mytable SET a = a + 1 LIMIT 1;

There's no way to guarantee which row will get updated as there is no sort order on it and order on disk is not predictable or consistent.

Row based replication avoids this problem by replicating the changed data rather than the queries, but whereas a statement like:

UPDATE mytable SET a = a + 1;

requires replicating just a few bytes for statement-based replication no matter how many rows it affects: if it updates 1 million rows, row-based replication will replicate all 1 million rows, which will be much slower and create much bigger binary logs.

Mixed mode switches between the two, using whichever is most efficient or safe (for example, simple inserts are probably best done by row-based replication - using statements may be slower). The opportunity for problems comes in recognising which statements are non-deterministic, which is a non-trivial problem.

In summary:

• Row-based: always safe, possibly very slow and inefficient in time and space
• Statement-based: not always safe, but may be much faster
• Mixed-mode: best of both worlds in theory, but could possibly get it wrong resulting in either slow performance, or wrong data depending on which way it gets it wrong!

Official docs are here.

This is also an old question: you should be using MySQL 5.5 for any new builds now. I prefer Percona's builds.