Mysql – Difference between row-based and statement-based replication in MySQL

MySQLreplication

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….!

Best Answer

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

Related Question