Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Just about to start using MySQL replication and I'm wondering what kind of binlog format is the best to use? There's Row, Statement and Mixed.

I'm going to be using 5.1.49.

Row is winning at the moment but I'm just wondering why I should use mixed instead of row.

Many thanks,


share|improve this question
why is raw winning ? – silviud Dec 14 '10 at 18:25
According to Oracle, it's the safest form of replication. In these slides; Oracle seems weary of mixed mode saying "but requires testing". – delenda Dec 16 '10 at 10:07
The default logging format for MySQL 5.1 is "mixed", so whatever the concerns were, they seem to be no longer valid – the-wabbit Feb 14 '12 at 9:53

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.

share|improve this answer
Regarding your last bullet point, why wouldn't MIXED-mode always fall back to ROW-based in situations where the determinism can't be determined? – Joe Holloway Aug 26 '14 at 17:34
I don't know for sure - it's really an internals question, but there are examples of it happening, and documentation on how it's decided. – Synchro Aug 26 '14 at 17:40

Your Answer


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.