Difference between Row based and statement based replication in Mysql?

Question

What is the Actual difference between Row based and Statement based replication I actually looking in terms of replication effect's on slave.

If I am using Row based replication then whats is the effect on 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.

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.

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.