<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-rbr-usage">
      </a>
      19.2.1.2 Usage of Row-Based Logging and Replication
     </h4>
    </div>
   </div>
  </div>
  <p>
   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 describes some best practices using it in
        replication.
  </p>
  <p>
   For additional information, see
   <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
    Section 19.2.1, “Replication Formats”
   </a>
   , and
   <a class="xref" href="replication-sbr-rbr.html" title="19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication">
    Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based
        Replication”
   </a>
   .
  </p>
  <p>
   For information about issues specific to NDB Cluster Replication
        (which depends on row-based replication), see
   <a class="xref" href="mysql-cluster-replication-issues.html" title="25.7.3 Known Issues in NDB Cluster Replication">
    Section 25.7.3, “Known Issues in NDB Cluster Replication”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a name="replication-rbr-usage-temptables">
      </a>
      <b>
       Row-based logging of temporary tables.
      </b>
      As noted in
      <a class="xref" href="replication-features-temptables.html" title="19.5.1.31 Replication and Temporary Tables">
       Section 19.5.1.31, “Replication and Temporary Tables”
      </a>
      ,
              temporary tables are not replicated when using row-based
              or mixed format. For more information, see
      <a class="xref" href="replication-sbr-rbr.html" title="19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication">
       Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based
        Replication”
      </a>
      .
     </p>
     <p>
      Temporary tables are not replicated when using row-based or
            mixed 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.
     </p>
     <p>
      You can switch from statement-based to row-based binary
            logging format at runtime even when temporary tables have
            been created, but you cannot switch from row-based or mixed
            format for binary logging to statement-based format at
            runtime, due to any
      <code class="literal">
       CREATE TEMPORARY
            TABLE
      </code>
      statements having been omitted from the
            binary log in the previous mode.
     </p>
     <p>
      The MySQL server tracks the logging mode that was in effect
            when each temporary table was created. When a given client
            session ends, the server logs a
      <code class="literal">
       DROP TEMPORARY
            TABLE IF EXISTS
      </code>
      statement for each temporary table
            that still exists and was created when statement-based
            binary logging was in use. If row-based or mixed format
            binary logging was in use when the table was created, the
      <code class="literal">
       DROP TEMPORARY TABLE IF EXISTS
      </code>
      statement
            is not logged.
     </p>
     <p>
      Nontransactional DML statements involving temporary tables
            are allowed when using
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=ROW
       </code>
      </a>
      , as long
            as any nontransactional tables affected by the statements
            are temporary tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       RBL and synchronization of nontransactional tables.
      </b>
      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 replica, a table lock is taken on all tables
              involved, and then the rows are applied in batch mode.
              Depending on the engine used for the replica's copy of the
              table, this may or may not be effective.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Latency and binary log size.
      </b>
      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 replica that match those on the source. You should
              be aware of the potential for this delay in your
              applications.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Reading the binary log.
      </b>
      <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
       <span class="command">
        <strong>
         mysqlbinlog
        </strong>
       </span>
      </a>
      displays row-based events
              in the binary log using the
      <a class="link" href="binlog.html" title="15.7.8.1 BINLOG Statement">
       <code class="literal">
        BINLOG
       </code>
      </a>
      statement. This
              statement displays an event as a base 64-encoded string,
              the meaning of which is not evident. When invoked with the
      <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_base64-output">
       <code class="option">
        --base64-output=DECODE-ROWS
       </code>
      </a>
      and
      <a class="link" href="mysqlbinlog.html#option_mysqlbinlog_verbose">
       <code class="option">
        --verbose
       </code>
      </a>
      options,
      <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
       <span class="command">
        <strong>
         mysqlbinlog
        </strong>
       </span>
      </a>
      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
      <a class="xref" href="mysqlbinlog-row-events.html" title="6.6.9.2 mysqlbinlog Row Event Display">
       Section 6.6.9.2, “mysqlbinlog Row Event Display”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Binary log execution errors and replica execution mode.
      </b>
      Using
      <a class="link" href="replication-options-replica.html#sysvar_replica_exec_mode">
       <code class="literal">
        replica_exec_mode=IDEMPOTENT
       </code>
      </a>
      is generally only useful with MySQL NDB Cluster
              replication, for which
      <code class="literal">
       IDEMPOTENT
      </code>
      is
              the default value. (See
      <a class="xref" href="mysql-cluster-replication-multi-source.html" title="25.7.10 NDB Cluster Replication: Bidirectional and Circular Replication">
       Section 25.7.10, “NDB Cluster Replication: Bidirectional and Circular Replication”
      </a>
      ).
              When
      <code class="literal">
       replica_exec_mode
      </code>
      is
      <code class="literal">
       IDEMPOTENT
      </code>
      , 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
              replica, so that the source and replica are no longer
              synchronized. Latency issues and use of nontransactional
              tables with RBR when
      <a class="link" href="replication-options-replica.html#sysvar_replica_exec_mode">
       <code class="literal">
        replica_exec_mode
       </code>
      </a>
      is
      <code class="literal">
       IDEMPOTENT
      </code>
      can cause the source and
              replica to diverge even further. For more information
              about
      <a class="link" href="replication-options-replica.html#sysvar_replica_exec_mode">
       <code class="literal">
        replica_exec_mode
       </code>
      </a>
      ,
              see
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
     </p>
     <p>
      For other scenarios, setting
      <a class="link" href="replication-options-replica.html#sysvar_replica_exec_mode">
       <code class="literal">
        replica_exec_mode
       </code>
      </a>
      to
      <code class="literal">
       STRICT
      </code>
      is normally sufficient; this is
            the default value for storage engines other than
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Filtering based on server ID not supported.
      </b>
      You can filter based on server ID using the
      <a class="link" href="change-replication-source-to.html#crs-opt-ignore_server_ids">
       <code class="literal">
        IGNORE_SERVER_IDS
       </code>
      </a>
      option
              for
      <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
       <code class="literal">
        CHANGE REPLICATION SOURCE
              TO
       </code>
      </a>
      . This option works with both the
              statement-based and row-based logging formats, but cannot
              be used when
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode=ON
       </code>
      </a>
      . Another
              method to filter out changes on some replicas is to use a
      <code class="literal">
       WHERE
      </code>
      clause that includes the relation
      <code class="literal">
       @@server_id &lt;&gt;
       <em class="replaceable">
        <code>
         id_value
        </code>
       </em>
      </code>
      clause with
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements. For
              example,
      <code class="literal">
       WHERE @@server_id &lt;&gt; 1
      </code>
      .
              However, this does not work correctly with row-based
              logging. To use the
      <a class="link" href="replication-options.html#sysvar_server_id">
       <code class="literal">
        server_id
       </code>
      </a>
      system variable
              for statement filtering, use statement-based logging.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       RBL, nontransactional tables, and stopped replicas.
      </b>
      When using row-based logging, if the replica server is
              stopped while a replica thread is updating a
              nontransactional table, the replica database can reach an
              inconsistent state. For this reason, it is recommended
              that you use a transactional storage engine such as
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      for all tables
              replicated using the row-based format. Use of
      <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
       <code class="literal">
        STOP REPLICA
       </code>
      </a>
      or
      <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
       <code class="literal">
        STOP REPLICA
              SQL_THREAD
       </code>
      </a>
      prior to shutting down the replica
              MySQL server helps prevent issues from occurring, and is
              always recommended regardless of the logging format or
              storage engine you use.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
