Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

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; 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 9:53
add comment

1 Answer 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.

share|improve this answer
add comment

Your Answer

 
discard

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.