Databases Reference
In-Depth Information
used to be, so they can potentially be more useful for some kinds of data recovery
efforts.
Row-based replication can be less CPU-intensive in many cases, due to the lack of
a need to plan and execute queries in the same way that statement-based replication
does.
Finally, row-based replication can help you find and solve data inconsistencies
more quickly in some cases. For example, statement-based replication won't fail
if you update a row on the master and it doesn't exist on the replica, but row-based
replication will throw an error and stop.
Row-based replication disadvantages
The statement isn't included in the log event, so it can be tough to figure out what
SQL was executed. This is important in many cases, in addition to knowing the
row changes. (This will probably be fixed in a future version of MySQL.)
Replication changes are applied on replicas in a completely different manner—it
isn't SQL being executed. In fact, the process of applying row-based changes is
pretty much a black box with no visibility into what the server is doing, and it's
not well documented or explained, so when things don't work right, it can be tough
to troubleshoot. As an example, if the replica chooses an inefficient way to find
rows to change, you can't observe that.
If you have multiple levels of replication servers, and all are configured for row-
based logging, a statement that you execute while your session-level
@@binlog_
format
variable is set to
STATEMENT
will be logged as a statement on the server
where it originates, but then the first-level replicas might relay the event in row-
based format to further replicas in the chain. That is, your desired statement-based
logging will get switched back to row-based logging as it propagates through the
replication topology.
Row-based logging can't handle some things that statement-based logging can,
such as schema changes on replicas.
Replication will sometimes halt in cases where statement-based replication would
continue, such as when the replica is missing a row that's supposed to be changed.
This could be regarded as a good thing. In any case, it is configurable with the
slave_exec_mode
option.
Many of these disadvantages are being lifted as time passes, but at the time of
writing, they are still true in most production deployments.
Replication Files
Let's take a look at some of the files replication uses. You already know about the binary
log and the relay log, but there are several other files, too. Where MySQL places
them depends mostly on your configuration settings. Different MySQL versions place
them in different directories by default. You can probably find them either in the data
