Skip navigation links

Additional languages
19.1.2.1 Comparison of Statement-Based Versus Row-Based Replication »
Section Navigation      [Toggle]

19.1.2. Replication Formats

[+/-]

19.1.2.1. Comparison of Statement-Based Versus 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 (SBR) and this correlates to the standard statement-based binary logging format. Binary logging and replication in MySQL 5.1.4 and earlier, and all previous versions of MySQL, used this format.

  • Row-based binary logging logs the physical changes to individual table rows. In replication terms this is row-based replication (RBR), the master writes events to the binary log that indicate how individual table rows are affected. Support for RBR was added in MySQL 5.1.5.

  • As of MySQL 5.1.8, the binary logging format can be altered on the fly according to the event being logged. With mixed-based logging (and the associated mixed-based replication (MBR)), statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described below. See Section 5.2.4.3, “Mixed Binary Logging Format”.

Starting with MySQL 5.1.12, mixed-based replication (that is, mixed-based logging) is the default format for all replication environment unless you specify otherwise.

Starting with MySQL 5.1.20, the binary logging format used is partially determined by the storage engine being used and the statement being executed. For more information on mixed-based logging and the rules governing the support of different logging formatsion, see Section 5.2.4.3, “Mixed Binary Logging Format”.

There are some known limitations and issues between the different replication formats. For a comparison that shows the advantages and disadvantages of statement-based and row-based replication, see Section 19.1.2.1, “Comparison of Statement-Based Versus Row-Based Replication”.

MySQL Cluster Replication makes use of row-based replication. The NDB storage engine is incompatible with statement-based replication, and NDB sets row-based logging format automatically. For more information, see Section 20.11, “MySQL Cluster Replication”.

With MySQL's classic statement-based replication, there may be issues with replicating stored routines or triggers. You can avoid these issues by using MySQL's row-based replication instead. For a detailed list of issues, see Section 23.4, “Binary Logging of Stored Routines and Triggers”.

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 backwards compatibility), the following session variables are written to the binary log and honoured 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.