Limitations of MySQL row-based replication

MySQL offers a few different options for how you perform replication. Statement-based has been around a lot longer, and though it has some troublesome characteristics they’re known well and can be managed. What’s more it supports online schema changes with multi-master active-passive setup. We recommend this solution.

Row-based replication is newer. It attempts to address problems like those introduced by non-deterministic functions, and replicating stored procedures. But it introduces it’s own challenges.

o won’t work if target table storage engine, column order, data types or row itself are different or missing.
o doesn’t write SQL to the binlogs – useful for troubleshooting
o harder to do point-in-time recovery without SQL in binlogs
o harder to do online schema changes by switching masters

* No built in checksums – pt-table-checksum is a must
–> There is from 5.6, but pt-table-checksum solves a different need and is still required. You could change this point to “no built in consistency checking”.

* temp tables disappear after restart
–> This applies only to statement based. Row based doesn’t replicate temp tables. And you are correct, if you modify data via temp tables – don’t use statement based.

* even with row-based mysql can fall back to statement
–> This is the case for DDL, but it’s fairly deterministic in how it works. I don’t recommend MIXED myself for this reason, because the behaviors are different (ROW: triggers execute on master, STATEMENT: triggers execute on slave).

* row-based does not include SQL in binlogs
–> Available in 5.6 as informational events.

* harder to do online schema changes by switching masters
–> The binlog_format is a session variable, pt-online-schema-change changes to STATEMENT itself for this to work. The specific issue is “not possible to do online schema change with a tiered replication architecture” since the slave in the middle breaks things.