Wednesday, June 22, 2011

Selecting BINLOG_FORMAT in MySQL

There are several reasons why a client might want to set binary logging on a per-session basis:
  • A session that makes many small changes to the database might want to use row-based logging.
  • A session that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.
  • Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.
So it seems that ROW base logging will help on the queries which has more update having where clause and taking time on master. Because in "STATEMENT" log format statement is exactly copied as it is and stored in log and same statement is replicated on slave. but if "ROW" base logging enabled then only updated ROWS are stored in the logs and only those ROW's statements only executed on SLAVE. 
[Ref]