Databases Reference
In-Depth Information
In theory, row-based replication is probably better all-around, and in practice it gen-
erally works fine for most people. But its implementation is new enough that it hasn't
had years of little special-case behaviors baked in to support all the operational needs
of MySQL administrators, and as a result it's still a nonstarter for some people. Here's
a more complete discussion of the benefits and drawbacks of each format to help you
decide which is more suitable for your needs:
Statement-based replication advantages
Logical replication works in more cases when the schema is different on the master
and the replica. For example, it can be made to work in more cases where the tables
have different but compatible data types, different column orders, and so on. This
makes it easier to perform schema changes on a replica and then promote it to
master, reducing downtime. Statement-based replication generally permits more
operational flexibility.
The replication-applying process in statement-based replication is normal SQL
execution, by and large. This means that all changes on the server are taking place
through a well-understood mechanism, and it's easy to inspect and determine what
is happening if something isn't working as expected.
Statement-based replication disadvantages
The list of things that can't be replicated correctly through statement-based logging
is so large that any given installation is likely to run into at least one of them. In
particular, there were tons of bugs affecting replication of stored procedures, trig-
gers, and so on in the 5.0 and 5.1 series of the server—so many that the way these
are replicated was actually changed around a couple of times in attempts to make
it work better. Bottom line: if you're using triggers or stored procedures, don't use
statement-based replication unless you're watching like a hawk to make sure you
don't run into problems.
There are also lots of problems with temporary tables, mixtures of storage engines,
specific SQL constructs, nondeterministic statements, and so on. These range from
annoying to show-stopping.
Row-based replication advantages
There are a lot fewer cases that don't work in row-based replication. It works cor-
rectly with all SQL constructs, with triggers, with stored procedures, and so on. It
generally only fails when you're trying to do something clever such as schema
changes on the replica.
It also creates opportunities for reduced locking, because it doesn't require such
strong serialization to be repeatable.
Row-based replication works by logging the data that's changed, so the binary log
is a record of what has actually changed on the master. You don't have to look at
a statement and guess whether it changed any data. Thus, in some ways you ac-
tually know more about what's changed in your server, and you have a better record
of the changes. Also, in some cases the row-based binary logs record what the data
