Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the master's binary log. The correlation between binary logging formats and the terms used during replication are:
Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard statement-based binary logging format. In MySQL 5.1.4 and earlier, binary logging and replication used this format exclusively.
Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known as row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.
As of MySQL 5.1.8, the server can change the binary logging format in real time according to the type of event using mixed-format logging.
When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 18.104.22.168, “Mixed Binary Logging Format”.
From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.
The default binary logging format in all MySQL Cluster NDB 6.x
and 7.x releases is
ROW. MySQL Cluster
Replication always uses row-based replication, and the
NDBCLUSTER storage engine is
incompatible with statement-based replication. Using
NDBCLUSTER sets row-based logging
format automatically. See
Section 17.6.2, “General Requirements for MySQL Cluster Replication”, for more
Starting with MySQL 5.1.20, when using
format, the binary logging format is determined in part by the
storage engine being used and the statement being executed. For
more information on mixed-format logging and the rules governing
the support of different logging formats, see
Section 22.214.171.124, “Mixed Binary Logging Format”.
The logging format in a running MySQL server is controlled by
system variable. This variable can be set with session or global
scope. The rules governing when and how the new setting takes
effect are the same as for other MySQL server system
variables—setting the variable for the current session lasts
only until the end of that session, and the change is not visible
to other sessions; setting the variable globally requires a
restart of the server to take effect. For more information, see
Section 13.7.4, “SET Syntax”.
There are conditions under which you cannot change the binary logging format at runtime or doing so causes replication to fail. See Section 126.96.36.199, “Setting The Binary Log Format”.
You must have the
to set the global
value. Starting with MySQL 5.1.29, you must have the
SUPER privilege to set either the
global or session
value. (Bug #39106)
The statement-based and row-based replication formats have different issues and limitations. For a comparison of their relative advantages and disadvantages, see Section 188.8.131.52, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.
With statement-based replication, you may encounter issues with replicating stored routines or triggers. You can avoid these issues by using row-based replication instead. For more information, see Section 19.7, “Binary Logging of Stored Programs”.