Flite Careers

An Advantage of MySQL Row-based Replication

Recently I wrote about one advantage of MySQL statement-based replication. In the comments of that post Andrew Moore pointed out that I can set slave_exec_mode=IDEMPOTENT on my replicas in order to get similar behavior in row-based replication. Thanks Andrew!

Now I’m going to highlight an advantage of row-based replication in MySQL. Again this is not one of the advantages documented in the manual, but it’s one that I think is important.

Here it is:

  • Row-based replication does not write to the binary log if you execute a statement that doesn’t change any data.
  • Statement-based replication writes all successful inserts, updates, and deletes to the binary log regardless of whether they change any data.

Depending on your application this distinction may or may not be a big deal for you. I’ve found that a lot of web applications – especially those that use an ORM – execute a lot of gratuitous updates that don’t change any data. Typically saving an object in one of these apps updates that row (and the rows of all sub-objects) in the database, regardless of how much data actually changed.

If I use statement-based replication those gratuitous updates are all written to the binary log, and the replicas end up wasting resources to copy them down from the master and execute them.

For example, say I execute the update statement from my last post 3 times:

insert into pruning (id,str) values(1,'old');
update pruning set str = 'updated on master' where id = 1;
update pruning set str = 'updated on master' where id = 1;
update pruning set str = 'updated on master' where id = 1;

The first update modifies the row, so it’s written to the binary log regardless of format. With statement-based replication the other two updates are also written to the binary log and executed on the replicas, but with row-based replication they are not.

The bottom line: If you have an app that does a lot of gratuitous updates to your MySQL database then row-based replication may prevent a lot of unnecessary statements from being replicated.