Skip navigation links
16.1.2.1 Comparison of Statement-Based and Row-Based Replication »
Section Navigation      [Toggle]

16.1.2. Replication Formats

[+/-]

16.1.2.1. Comparison of Statement-Based and Row-Based Replication
16.1.2.2. Usage of Row-based Logging and Row-Based Replication

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 the type of event being recorded. 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 messages known as events to the binary log that indicate how individual table rows are changed.

  • As of MySQL 5.1.8, the binary logging format can be changed in real time according to the event being logged 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 below. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, “Mixed Binary Logging Format”.

From MySQL 5.1.12 to MySQL 5.1.28 (inclusive), the mixed format is the default for MySQL replication. Beginning with MySQL 5.1.29, statement-based format is the default.

Note

MySQL Cluster.  The default binary logging format in all MySQL Cluster NDB 6.1, 6.2, 6.3, and later 6.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 MySQL Cluster Replication — Assumptions and General Requirements, for more information.

Starting with MySQL 5.1.20, when using MIXED 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 5.2.4.3, “Mixed Binary Logging Format”.

The logging format in a running MySQL server is controlled by setting the binlog_format server 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 in order to take effect. For more information, see Section 12.5.4, “SET Syntax”.

You must have the SUPER privilege to set the binary logging format on the global level. Starting with MySQL 5.1.29, you must also have the SUPER privilege to set the binary logging format for the current session. (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 16.1.2.1, “Comparison 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 18.6, “Binary Logging of Stored Programs”.

If you build MySQL from source, row-based replication is available by default unless you invoke configure with the --without-row-based-replication option.

For MySQL 5.1.20 and later (and MySQL 5.0.46 for backward compatibility), the following session variables are written to the binary log and honored by the replication slave when parsing the binary log:

Important

Even though session variables relating to character sets and collations are written to the binary log, replication between different character sets is not supported.