<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-sbr-rbr">
      </a>
      19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based
Replication
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045138524096">
  </a>
  <a class="indexterm" name="idm46045138522640">
  </a>
  <p>
   Each binary logging format has advantages and disadvantages. For
        most users, the mixed replication format should provide the best
        combination of data integrity and performance. If, however, you
        want to take advantage of the features specific to the
        statement-based or row-based replication format when performing
        certain tasks, you can use the information in this section,
        which provides a summary of their relative advantages and
        disadvantages, to determine which is best for your needs.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="replication-sbr-rbr.html#replication-sbr-rbr-sbr-advantages" title="Advantages of statement-based replication">
       Advantages
            of statement-based replication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages" title="Disadvantages of statement-based replication">
       Disadvantages
            of statement-based replication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replication-sbr-rbr.html#replication-sbr-rbr-rbr-advantages" title="Advantages of row-based replication">
       Advantages
            of row-based replication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="replication-sbr-rbr.html#replication-sbr-rbr-rbr-disadvantages" title="Disadvantages of row-based replication">
       Disadvantages
            of row-based replication
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="replication-sbr-rbr-sbr-advantages">
   </a>
   Advantages of statement-based replication
  </h5>
  <a class="indexterm" name="idm46045138510672">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Proven technology.
     </p>
    </li>
    <li class="listitem">
     <p>
      Less data written to log files. When updates or deletes
            affect many rows, this results in
      <span class="emphasis">
       <em>
        much
       </em>
      </span>
      less storage space required for log files. This also means
            that taking and restoring from backups can be accomplished
            more quickly.
     </p>
    </li>
    <li class="listitem">
     <p>
      Log files contain all statements that made any changes, so
            they can be used to audit the database.
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="replication-sbr-rbr-sbr-disadvantages">
   </a>
   Disadvantages of statement-based replication
  </h5>
  <a class="indexterm" name="idm46045138504320">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Statements that are unsafe for SBR.
      </b>
      <a class="indexterm" name="idm46045138501664">
      </a>
      <a class="indexterm" name="idm46045138500144">
      </a>
      Not all statements which modify data (such as
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      statements) can be
              replicated using statement-based replication. Any
              nondeterministic behavior is difficult to replicate when
              using statement-based replication. Examples of such Data
              Modification Language (DML) statements include the
              following:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         A statement that depends on a loadable function or
                stored program that is nondeterministic, since the value
                returned by such a function or stored program or depends
                on factors other than the parameters supplied to it.
                (Row-based replication, however, simply replicates the
                value returned by the function or stored program, so its
                effect on table rows and data is the same on both the
                source and replica.) See
         <a class="xref" href="replication-features-invoked.html" title="19.5.1.16 Replication of Invoked Features">
          Section 19.5.1.16, “Replication of Invoked Features”
         </a>
         , for more
                information.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
         and
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         statements that
                use a
         <code class="literal">
          LIMIT
         </code>
         clause without an
         <code class="literal">
          ORDER BY
         </code>
         are nondeterministic. See
         <a class="xref" href="replication-features-limit.html" title="19.5.1.18 Replication and LIMIT">
          Section 19.5.1.18, “Replication and LIMIT”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Locking read statements
                (
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT ... FOR
                UPDATE
          </code>
         </a>
         and
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT ... FOR
                SHARE
          </code>
         </a>
         ) that use
         <code class="literal">
          NOWAIT
         </code>
         or
         <code class="literal">
          SKIP LOCKED
         </code>
         options. See
         <a class="xref" href="innodb-locking-reads.html#innodb-locking-reads-nowait-skip-locked" title="Locking Read Concurrency with NOWAIT and SKIP LOCKED">
          Locking Read Concurrency with NOWAIT and SKIP LOCKED
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         Deterministic loadable functions must be applied on the
                replicas.
        </p>
       </li>
       <li class="listitem">
        <p>
         Statements using any of the following functions cannot
                be replicated properly using statement-based
                replication:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            <a class="link" href="string-functions.html#function_load-file">
             <code class="literal">
              LOAD_FILE()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="miscellaneous-functions.html#function_uuid">
             <code class="literal">
              UUID()
             </code>
            </a>
            ,
            <a class="link" href="miscellaneous-functions.html#function_uuid-short">
             <code class="literal">
              UUID_SHORT()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="information-functions.html#function_user">
             <code class="literal">
              USER()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="information-functions.html#function_found-rows">
             <code class="literal">
              FOUND_ROWS()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="date-and-time-functions.html#function_sysdate">
             <code class="literal">
              SYSDATE()
             </code>
            </a>
            (unless
                    both the source and the replica are started with the
            <a class="link" href="server-options.html#option_mysqld_sysdate-is-now">
             <code class="option">
              --sysdate-is-now
             </code>
            </a>
            option)
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="locking-functions.html#function_get-lock">
             <code class="literal">
              GET_LOCK()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="locking-functions.html#function_is-free-lock">
             <code class="literal">
              IS_FREE_LOCK()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="locking-functions.html#function_is-used-lock">
             <code class="literal">
              IS_USED_LOCK()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="mathematical-functions.html#function_rand">
             <code class="literal">
              RAND()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="locking-functions.html#function_release-lock">
             <code class="literal">
              RELEASE_LOCK()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="replication-functions-synchronization.html#function_source-pos-wait">
             <code class="literal">
              SOURCE_POS_WAIT()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="miscellaneous-functions.html#function_sleep">
             <code class="literal">
              SLEEP()
             </code>
            </a>
           </p>
          </li>
          <li class="listitem">
           <p>
            <a class="link" href="information-functions.html#function_version">
             <code class="literal">
              VERSION()
             </code>
            </a>
           </p>
          </li>
         </ul>
        </div>
        <p>
         However, all other functions are replicated correctly
                using statement-based replication, including
         <a class="link" href="date-and-time-functions.html#function_now">
          <code class="literal">
           NOW()
          </code>
         </a>
         and so forth.
        </p>
        <p>
         For more information, see
         <a class="xref" href="replication-features-functions.html" title="19.5.1.14 Replication and System Functions">
          Section 19.5.1.14, “Replication and System Functions”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      Statements that cannot be replicated correctly using
            statement-based replication are logged with a warning like
            the one shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa45951381"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">[Warning] Statement is not safe to log in statement format.</code></pre>
     </div>
     <p>
      A similar warning is also issued to the client in such
            cases. The client can display it using
      <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       <code class="literal">
        SHOW WARNINGS
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT ...
            SELECT
       </code>
      </a>
      requires a greater number of row-level
            locks than with row-based replication.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      statements that
            require a table scan (because no index is used in the
      <code class="literal">
       WHERE
      </code>
      clause) must lock a greater number
            of rows than with row-based replication.
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      : An
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statement that uses
      <code class="literal">
       AUTO_INCREMENT
      </code>
      blocks other
            nonconflicting
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      For complex statements, the statement must be evaluated and
            executed on the replica before the rows are updated or
            inserted. With row-based replication, the replica only has
            to modify the affected rows, not execute the full statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      If there is an error in evaluation on the replica,
            particularly when executing complex statements,
            statement-based replication may slowly increase the margin
            of error across the affected rows over time. See
      <a class="xref" href="replication-features-errors.html" title="19.5.1.29 Replica Errors During Replication">
       Section 19.5.1.29, “Replica Errors During Replication”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Stored functions execute with the same
      <a class="link" href="date-and-time-functions.html#function_now">
       <code class="literal">
        NOW()
       </code>
      </a>
      value as the calling
            statement. However, this is not true of stored procedures.
     </p>
    </li>
    <li class="listitem">
     <p>
      Deterministic loadable functions must be applied on the
            replicas.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table definitions must be (nearly) identical on source and
            replica. See
      <a class="xref" href="replication-features-differing-tables.html" title="19.5.1.9 Replication with Differing Table Definitions on Source and Replica">
       Section 19.5.1.9, “Replication with Differing Table Definitions on Source and Replica”
      </a>
      , for
            more information.
     </p>
    </li>
    <li class="listitem">
     <p>
      DML operations that read data from MySQL grant tables
            (through a join list or subquery) but do not modify them are
            performed as non-locking reads on the MySQL grant tables and
            are therefore not safe for statement-based replication. For
            more information, see
      <a class="xref" href="grant-tables.html#grant-tables-concurrency" title="Grant Table Concurrency">
       Grant Table Concurrency
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="replication-sbr-rbr-rbr-advantages">
   </a>
   Advantages of row-based replication
  </h5>
  <a class="indexterm" name="idm46045138421008">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      All changes can be replicated. This is the safest form of
            replication.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Statements that update the information in the
       <code class="literal">
        mysql
       </code>
       system schema, such as
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       ,
       <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
        <code class="literal">
         REVOKE
        </code>
       </a>
       and the manipulation
              of triggers, stored routines (including stored
              procedures), and views, are all replicated to replicas
              using statement-based replication.
      </p>
      <p>
       For statements such as
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
              ... SELECT
        </code>
       </a>
       , a
       <code class="literal">
        CREATE
       </code>
       statement is generated from the table definition and
              replicated using statement-based format, while the row
              insertions are replicated using row-based format.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Fewer row locks are required on the source, which thus
            achieves higher concurrency, for the following types of
            statements:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
          <code class="literal">
           INSERT
                ... SELECT
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
          <code class="literal">
           INSERT
          </code>
         </a>
         statements with
         <code class="literal">
          AUTO_INCREMENT
         </code>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         or
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
         statements with
         <code class="literal">
          WHERE
         </code>
         clauses that do not use keys or
                do not change most of the examined rows.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Fewer row locks are required on the replica for any
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="replication-sbr-rbr-rbr-disadvantages">
   </a>
   Disadvantages of row-based replication
  </h5>
  <a class="indexterm" name="idm46045138395600">
  </a>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      RBR can generate more data that must be logged. To replicate
            a DML statement (such as an
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      or
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statement),
            statement-based replication writes only the statement to the
            binary log. By contrast, row-based replication writes each
            changed row to the binary log. If the statement changes many
            rows, row-based replication may write significantly more
            data to the binary log; this is true even for statements
            that are rolled back. This also means that making and
            restoring a backup can require more time. In addition, the
            binary log is locked for a longer time to write the data,
            which may cause concurrency problems. Use
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_image">
       <code class="literal">
        binlog_row_image=minimal
       </code>
      </a>
      to
            reduce the disadvantage considerably.
     </p>
    </li>
    <li class="listitem">
     <p>
      Deterministic loadable functions that generate large
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      values take longer to
            replicate with row-based replication than with
            statement-based replication. This is because the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      column value is logged,
            rather than the statement generating the data.
     </p>
    </li>
    <li class="listitem">
     <p>
      You cannot see on the replica what statements were received
            from the source and executed. However, you can see what data
            was changed using
      <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>
      with the
            options
      <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>
      .
     </p>
     <p>
      Alternatively, use the
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_rows_query_log_events">
       <code class="literal">
        binlog_rows_query_log_events
       </code>
      </a>
      variable, which if enabled adds a
      <code class="literal">
       Rows_query
      </code>
      event with the statement to
      <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>
      output when the
      <code class="literal">
       -vv
      </code>
      option is used.
     </p>
    </li>
    <li class="listitem">
     <p>
      For tables using the
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      storage engine, a stronger lock is required on the replica
            for
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements when
            applying them as row-based events to the binary log than
            when applying them as statements. This means that concurrent
            inserts on
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      tables are
            not supported when using row-based replication.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
