Difference between row-based and statement-based replication in MySQL?

Question

What is the actual difference between row-based and statement-based replication. I am actually looking in terms of replication's effect on the slave.

If I am using row-based replication then what is the effect on the slave and if I am using statement-based then what is the effect?

Please also take the following parameters in consideration:

 replicate-ignore-db and replicate-do-db 

Thanks....!

Answer 1

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

• Abdul Manaf: what is default mode of replication SBR or RBR...??
• RolandoMySQLDBA: SBR - Statement
• Aaron Brown: The default mode is MIXED since 5.1.8, which uses primarily statement based replication, but automatically switches to RBR when queries that are unsafe with SBR are executed. dev.mysql.com/doc/refman/5.1/en/replication-formats.html

Answer 2

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.