Related Documentation Download this Excerpt
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb
HTML Download (TGZ) - 444.6Kb
HTML Download (Zip) - 455.8Kb

MySQL Replication  /  ...  /  Usage of Row-Based Logging and Replication

2.2.2 Usage of Row-Based Logging and Replication

MySQL uses statement-based logging (SBL), row-based logging (RBL) or mixed-format logging. The type of binary log used impacts the size and efficiency of logging.Therefore the choice between row-based replication (RBR) or statement-based replication (SBR) depends on your application and environment. This section describes known issues when using a row-based format log, and discusses some best practices using it in replication.

For additional information, see Section 2.2, “Replication Formats”, and Section 2.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

For information about issues specific to NDB Cluster Replication (which depends on row-based replication), see Known Issues in NDB Cluster Replication.

  • Row-based logging of temporary tables.  As noted in Section 4.1.30, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When using mixed format logging, safe statements involving temporary tables are logged using statement-based format. For more information, see Section 2.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

    Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.

    In MySQL 5.6, you can switch from statement-based to row-based binary logging mode even when temporary tables have been created. However, while using the row-based format, the MySQL server cannot determine the logging mode that was in effect when a given temporary table was created. For this reason, the server in such cases logs a DROP TEMPORARY TABLE IF EXISTS statement for each temporary table that still exists for a given client session when that session ends. While this means that it is possible that an unnecessary DROP TEMPORARY TABLE statement might be logged in some cases, the statement is harmless, and does not cause an error even if the table does not exist, due to the presence of the IF EXISTS option.

    In MySQL 5.6.6 and earlier, the --disable-gtid-unsafe-statements option caused any nontransactional DML statement involving temporary tables to fail with an error when using row-based logging, in spite of the fact that they are not written to the binary log. In MySQL 5.6.7 and later, such statements are allowed when using binlog_format=ROW, as long as any nontransactional tables affected by the statements are temporary tables (Bug #14272672).

  • RBL and synchronization of nontransactional tables.  When many rows are affected, the set of changes is split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, and then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table.)

  • Latency and binary log size.  RBL writes changes for each row to the binary log and so its size can increase quite rapidly. This can significantly increase the time required to make changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.

  • Reading the binary log.  mysqlbinlog displays row-based events in the binary log using the BINLOG statement (see BINLOG Syntax). This statement displays an event as a base 64-encoded string, the meaning of which is not evident. When invoked with the --base64-output=DECODE-ROWS and --verbose options, mysqlbinlog formats the contents of the binary log to be human readable. When binary log events were written in row-based format and you want to read or recover from a replication or database failure you can use this command to read contents of the binary log. For more information, see mysqlbinlog Row Event Display.

  • Binary log execution errors and slave_exec_mode.  If slave_exec_mode is IDEMPOTENT, a failure to apply changes from RBL because the original row cannot be found does not trigger an error or cause replication to fail. This means that it is possible that updates are not applied on the slave, so that the master and slave are no longer synchronized. Latency issues and use of nontransactional tables with RBR when slave_exec_mode is IDEMPOTENT can cause the master and slave to diverge even further. For more information about slave_exec_mode, see Server System Variables.


    slave_exec_mode=IDEMPOTENT is generally useful only for circular replication or multi-master replication with NDB Cluster, for which IDEMPOTENT is the default value.

    For other scenarios, setting slave_exec_mode to STRICT is normally sufficient; this is the default value.


    Formerly, the default value when using NDB Cluster was slave_exec_mode=IDEMPOTENT, but this is no longer the case in MySQL NDB Cluster 7.3 and later.

  • Filtering based on server ID not supported.  In MySQL 5.6, you can filter based on server ID by using the IGNORE_SERVER_IDS option for the CHANGE MASTER TO statement. This option works with statement-based and row-based logging formats. Another method to filter out changes on some slaves is to use a WHERE clause that includes the relation @@server_id <> id_value clause with UPDATE and DELETE statements. For example, WHERE @@server_id <> 1. However, this does not work correctly with row-based logging. To use the server_id system variable for statement filtering, use statement-based logging.

  • Database-level replication options.  The effects of the --replicate-do-db, --replicate-ignore-db, and --replicate-rewrite-db options differ considerably depending on whether row-based or statement-based logging is used. Therefore, it is recommended to avoid database-level options and instead use table-level options such as --replicate-do-table and --replicate-ignore-table. For more information about these options and the impact replication format has on how they operate, see Section 2.4, “Replication and Binary Logging Options and Variables”.

  • RBL, nontransactional tables, and stopped slaves.  When using row-based logging, if the slave server is stopped while a slave thread is updating a nontransactional table, the slave database can reach an inconsistent state. For this reason, it is recommended that you use a transactional storage engine such as InnoDB for all tables replicated using the row-based format. Use of STOP SLAVE or STOP SLAVE SQL_THREAD prior to shutting down the slave MySQL server helps prevent issues from occurring, and is always recommended regardless of the logging format or storage engine you use.

User Comments
Sign Up Login You must be logged in to post a comment.