|Applicable To||MySQL Server|
|Server Startup Option||--binlog-format=<value>|
|Possible Values||enum(ROW, STATEMENT, MIXED)|
|Default||< 5.1.12: STATEMENT |
>= 5.1.12: MIXED
Starting with 5.1.5, MySQL has implemented ROW based replication format which logs the physical changes to individual row changes. This looks like the most optimal way to many users. But it is not always, rather not optimal most of the times. E.g. consider a statement that does bulk insert of thousands of rows. In ROW based logging, there will be those many entries in binlog and otherwise it would have been just one single statement.
STATEMENT based replication, the old good historical way, propagates SQL statements from master to slave and has been working good all those years except for few cases like statements using non deterministic UDFs.
In MIXED format, MySQL uses STATEMENT based replication by default other than a few cases like
- when USER(), CURRENT_USER() are used
- when a call to UDF is involved
- when 2 or more tables with AUTO_INCREMENT columns are updated.
... For full list of all such cases, refer the official documentation.
What about UDF's?
A user defined function or stored procedure is very hard to predict. In such cases, statement based replication can create inconsistencies. Few days back, I saw a case where a table included in
Replicate_Ignore_Table list was propagating statements from a procedure. If one has procedures and such cases, consider using ROW or MIXED mode replication.
What to use?
Though ROW based replication is the safest in case of creating inconsistencies, it may lead to sheer performance degradation thanks to binlog size. On the other hand STATEMENT based replication has drawbacks like with UDFs etc. IMHO, MIXED mode is the best way out unless you have some very special case. The only problem is that there might be more cases that need to be handled by mixed mode than currently being served. We need time's stamp on it. :)
- MySQL manual entry on binlog_format
- MySQL manual on Replication Formats
- MySQL manual: Mixed Binary Logging Format
- Statement based vs Row based replication
Hope you enjoyed reading this.