5.3. Row-Based Replication

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (SBR). As of MySQL 5.1.5, another basis for replication is available. This is called row-based replication (RBR). Instead of sending SQL statements to the slave, the master writes events to its binary log that indicate how individual table rows are affected.

For a comparison that shows the advantages and disadvantages of SBR and RBR, see Section 5.12, "Comparison of Statement-Based Versus Row-Based 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.

If you build MySQL from source, row-based replication is unavailable unless you invoke configure with the --with-row-based-replication option.

MySQL Server use statement-based replication by default, even if it has been configured with support for row-based replication. To cause row-based replication to be used, start the server with the --binlog-format=row option to enable row-based replication globally (for all client connections). The option also automatically turns on innodb_locks_unsafe_for_binlog, which is safe when row-based replication is used.

Statement-based replication can be chosen at server startup either by specifying --binlog-format=statement or by not using the --binlog-format option at all.

Row-based replication causes most changes to be written to the binary log using the row-based format. Some changes, however, must be written to the binary log as statements:

• ANALYZE TABLE

• REPAIR TABLE

• OPTIMIZE TABLE

The --binlog-row-event-max-size is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 1024.