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.

Click through to the end for multi-master solutions that work with MySQL.

3. Row-based replication limitations

Row-based replication addresses some of the limitations of statement based replication.

o works better with stored procedures
o reduces problems associated with non-deterministic functions

But it creates a few of it’s own, some are show-stoppers:

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

NEXT: Reason 4 – Cannot reclone without a single master

PREV: Reason 2 – MySQL Replication is prone to failure

Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample

  • Pingback: MySQL requires an authoritative master to build slaves

  • Pingback: Why does MySQL replication fail?

  • http://www.iheavy.com/blog/ Sean Hull

    Friend & colleague Morgan Tocker had the following very helpful comments.

    Hi Sean,

    I wanted to write to you personally so I don’t seem negative in comments. I’d love to see some MySQL 5.6 mentions :D Some of these features get a lot better.

    RE: http://www.iheavy.com/2013/04/16/why-does-mysql-replication-fail/

    * No globally unique transaction id (like Oracle SCN – system change number)
    –> There is from MySQL 5.6.

    * 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.

    * row-based still a new code path – doesn’t support zero downtime
    –> You might mean GTIDs? Row-based is a 5.1 feature, it’s very well supported: http://www.tocker.ca/2013/09/04/row-based-replication.html

    * 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.

    * MySQL replica slaves die frequently, require re-clone from authoritative master
    –> Fixed in 5.6. Slaves can be made crash-safe (Facebook runs crash safe for example, I also recommend it: http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-make-mysql-durable.html )

    RE: http://www.iheavy.com/2013/04/16/limitations-of-mysql-row-based-replication/

    * doesn’t write SQL to the binlogs – useful for troubleshooting
    –> Mentioned from previous post, can in 5.6.

    * harder to do point-in-time recovery without SQL in binlogs
    –> Will actually be better in 5.7 with idempotent replay: http://geek.rohitkalhans.com/2013/05/mysqlbinlog-idemmpotent-mode.html

    * 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.

    - Morgan