9

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,

Delenda

Chetan Bhargava
  • 245
  • 5
  • 15
delenda
  • 91
  • 1
  • 1
  • 2
  • why is raw winning ? – silviud Dec 14 '10 at 18:25
  • According to Oracle, it's the safest form of replication. In these slides; http://www.slideshare.net/ligaya/mysql-51-replication 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 09:53

1 Answers1

13

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.

Synchro
  • 2,983
  • 5
  • 25
  • 35
  • 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](https://www.kickstarter.com/backing-and-hacking/the-day-the-replication-died), and [documentation on how it's decided](http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html). – Synchro Aug 26 '14 at 17:40
  • 1
    Can this answer be put straight into the mysql docs please? :) – JamesD Jul 22 '21 at 00:49