Difference between row-based and statement-based replication in MySQL?
Statement-Based Replication (SBR) will replicate SQL Statements. It is easier to read using mysqlbinlog dump program.
Row based replication (RBR) will replicate actual data changes. Binary logs and relay logs will grow much faster than SBR. You also cannot identify the SQL that generated the changes.
(Please read updated information for MySQL 5.6.2. below)
Here is a more comprehensive pros-and-cons list : http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm
MySQL will allow both types of statement/row formats in its binary logs. I recommend STATEMENT based. Default binary log format is MIXED which is OK.
Since you are using replicate-ignore-db
and replicate-do-db
, I would trust Statement-Based Replication.
Update for MySQL 5.6.2:
Introduced in MySQL 5.6.2 the binlog_rows_query_log_events
system variable causes a MySQL 5.6.2 or later server to write informational log events such as row query log events into its binary log. So we can identify the SQL that generated the changes.
Reference: 17.1.4.4 Binary Log Options and Variables
In MySQL 5.5, statement-based format is the default. But it is better to use the mixed format. Because in that case, statement-based logging is used by default, but automatically switches to row-based logging in cases where-ever required.