Home / server / Questions / 151392
Accepted
Alistair Prestidge
Asked: 2010-06-16 07:31:28 +0800 CST

why use mixed-based replication for mysql

  • 772

I am in the process of configuring MySQL replication and am intending to use row-based-replication but I was also reading up about mixed-based replication.

This is where statement-based is the default and then for certain circumstances (http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html) MySQL will switch to row-based.

The list is quit vast on when it will switch to row-based.

My questions are:

Does any one use mixed? If yes why did you chose this over just using one or the other?

Thanks in advance

mysql replication mysql-replication

1 Answers

  1. Best Answer

    Does any one use mixed?

    I bet many people have used Mixed-mode replication, since this was the default for a long time according to the MySQL Reference Manual:

    From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.

    The MySQL Performance Blog talks about different problems with the different replication methods.

    So, it seems that MySQL has been trying to switch between mixed-mode, statement-based and row-based replication within the 5.1.x series, and that is unnerving.

    I'm currently reading High Performance MySQL, Second Edition from O'Reilly. It has a great chapter on replication, and is well worth the read. Many of my questions have been answered with an hour of reading. You can read some of the chapter in Chapter 8. Replication > Replication Under the Hood - Pg. 357:

    Because neither format is perfect for every situation, MySQL 5.1 switches between statement-based and row-based replication dynamically. By default, it uses statement-based replication, but when it detects an event that cannot be replicated correctly with a statement, it switches to row-based replication. You can also control the format as needed by setting the binlog_format session variable.

    • 1