<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="binary-log-setting">
      </a>
      7.4.4.2 Setting The Binary Log Format
     </h4>
    </div>
   </div>
  </div>
  <p>
   You can select the binary logging format explicitly by starting
        the MySQL server with
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="option">
     --binlog-format=
     <em class="replaceable">
      <code>
       type
      </code>
     </em>
    </code>
   </a>
   .
        The supported values for
   <em class="replaceable">
    <code>
     type
    </code>
   </em>
   are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       STATEMENT
      </code>
      causes logging to be statement
            based.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROW
      </code>
      causes logging to be row based. This
            is the default.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MIXED
      </code>
      causes logging to use mixed format.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Setting the binary logging format does not activate binary
        logging for the server. The setting only takes effect when
        binary logging is enabled on the server, which is the case when
        the
   <a class="link" href="replication-options-binary-log.html#sysvar_log_bin">
    <code class="literal">
     log_bin
    </code>
   </a>
   system variable is
        set to
   <code class="literal">
    ON
   </code>
   . In MySQL 8.4, binary
        logging is enabled by default, and is disabled only if you start
        the server with
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --skip-log-bin
    </code>
   </a>
   or
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --disable-log-bin
    </code>
   </a>
   .
  </p>
  <p>
   The logging format also can be switched at runtime, although
        note that there are a number of situations in which you cannot
        do this, as discussed later in this section. Set the global
        value of the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   system variable to specify the format for clients that connect
        subsequent to the change:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7906507"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> binlog_format <span class="token operator">=</span> <span class="token string">'STATEMENT'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> binlog_format <span class="token operator">=</span> <span class="token string">'ROW'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> binlog_format <span class="token operator">=</span> <span class="token string">'MIXED'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   An individual client can control the logging format for its own
        statements by setting the session value of
   <code class="literal">
    binlog_format
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97630742"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">SESSION</span> binlog_format <span class="token operator">=</span> <span class="token string">'STATEMENT'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">SESSION</span> binlog_format <span class="token operator">=</span> <span class="token string">'ROW'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">SESSION</span> binlog_format <span class="token operator">=</span> <span class="token string">'MIXED'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Changing the global
   <code class="literal">
    binlog_format
   </code>
   value
        requires privileges sufficient to set global system variables.
        Changing the session
   <code class="literal">
    binlog_format
   </code>
   value
        requires privileges sufficient to set restricted session system
        variables. See
   <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
    Section 7.1.9.1, “System Variable Privileges”
   </a>
   .
  </p>
  <p>
   There are several reasons why a client might want to set binary
        logging on a per-session basis:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A session that makes many small changes to the database
            might want to use row-based logging.
     </p>
    </li>
    <li class="listitem">
     <p>
      A session that performs updates that match many rows in the
      <code class="literal">
       WHERE
      </code>
      clause might want to use
            statement-based logging because it is more efficient to log
            a few statements than many rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      Some statements require a lot of execution time on the
            source, but result in just a few rows being modified. It
            might therefore be beneficial to replicate them using
            row-based logging.
     </p>
    </li>
   </ul>
  </div>
  <p>
   There are exceptions when you cannot switch the replication
        format at runtime:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The replication format cannot be changed from within a
            stored function or a trigger.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine is
            enabled.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a session has open temporary tables, the replication
            format cannot be changed for the session (
      <code class="literal">
       SET
            @@SESSION.binlog_format
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      If any replication channel has open temporary tables, the
            replication format cannot be changed globally (
      <code class="literal">
       SET
            @@GLOBAL.binlog_format
      </code>
      or
      <code class="literal">
       SET
            @@PERSIST.binlog_format
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      If any replication channel applier thread is currently
            running, the replication format cannot be changed globally
            (
      <code class="literal">
       SET @@GLOBAL.binlog_format
      </code>
      or
      <code class="literal">
       SET @@PERSIST.binlog_format
      </code>
      ).
     </p>
    </li>
   </ul>
  </div>
  <p>
   Trying to switch the replication format in any of these cases
        (or attempting to set the current replication format) results in
        an error. You can, however, use
   <code class="literal">
    PERSIST_ONLY
   </code>
   (
   <code class="literal">
    SET @@PERSIST_ONLY.binlog_format
   </code>
   ) to change
        the replication format at any time, because this action does not
        modify the runtime global system variable value, and takes
        effect only after a server restart.
  </p>
  <p>
   Switching the replication format at runtime is not recommended
        when any temporary tables exist, because temporary tables are
        logged only when using statement-based replication, whereas with
        row-based replication and mixed replication, they are not
        logged.
  </p>
  <p>
   Switching the replication format while replication is ongoing
        can also cause issues. Each MySQL Server can set its own and
        only its own binary logging format (true whether
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   is set with
        global or session scope). This means that changing the logging
        format on a replication source server does not cause a replica
        to change its logging format to match. When using
   <code class="literal">
    STATEMENT
   </code>
   mode, the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format
    </code>
   </a>
   system variable
        is not replicated. When using
   <code class="literal">
    MIXED
   </code>
   or
   <code class="literal">
    ROW
   </code>
   logging mode, it is replicated but is
        ignored by the replica.
  </p>
  <p>
   A replica is not able to convert binary log entries received in
   <code class="literal">
    ROW
   </code>
   logging format to
   <code class="literal">
    STATEMENT
   </code>
   format for use in its own binary
        log. The replica must therefore use
   <code class="literal">
    ROW
   </code>
   or
   <code class="literal">
    MIXED
   </code>
   format if the source does. Changing the
        binary logging format on the source from
   <code class="literal">
    STATEMENT
   </code>
   to
   <code class="literal">
    ROW
   </code>
   or
   <code class="literal">
    MIXED
   </code>
   while replication is ongoing to a
        replica with
   <code class="literal">
    STATEMENT
   </code>
   format can cause
        replication to fail with errors such as
   <span class="errortext">
    Error
        executing row event: 'Cannot execute statement: impossible to
        write to binary log since statement is in row format and
        BINLOG_FORMAT = STATEMENT.'
   </span>
   Changing the binary
        logging format on the replica to
   <code class="literal">
    STATEMENT
   </code>
   format when the source is still using
   <code class="literal">
    MIXED
   </code>
   or
   <code class="literal">
    ROW
   </code>
   format also causes the same type of
        replication failure. To change the format safely, you must stop
        replication and ensure that the same change is made on both the
        source and the replica.
  </p>
  <p>
   If you are using
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables and
        the transaction isolation level is
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
    <code class="literal">
     READ
        COMMITTED
    </code>
   </a>
   or
   <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-uncommitted">
    <code class="literal">
     READ
        UNCOMMITTED
    </code>
   </a>
   , only row-based logging can be used. It is
   <span class="emphasis">
    <em>
     possible
    </em>
   </span>
   to change the logging format to
   <code class="literal">
    STATEMENT
   </code>
   , but doing so at runtime leads very
        rapidly to errors because
   <code class="literal">
    InnoDB
   </code>
   can no
        longer perform inserts.
  </p>
  <p>
   With the binary log format set to
   <code class="literal">
    ROW
   </code>
   , many
        changes are written to the binary log using the row-based
        format. Some changes, however, still use the statement-based
        format. Examples include all DDL (data definition language)
        statements such as
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   ,
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   , or
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   .
  </p>
  <p>
   When row-based binary logging is used, the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_event_max_size">
    <code class="literal">
     binlog_row_event_max_size
    </code>
   </a>
   system variable and its corresponding startup option
   <a class="link" href="replication-options-binary-log.html#option_mysqld_binlog-row-event-max-size">
    <code class="option">
     --binlog-row-event-max-size
    </code>
   </a>
   set a
        soft limit on the maximum size of row events. The default value
        is 8192 bytes, and the value can only be changed at server
        startup. Where possible, rows stored in the binary log are
        grouped into events with a size not exceeding the value of this
        setting. If an event cannot be split, the maximum size can be
        exceeded.
  </p>
  <p>
   The
   <a class="link" href="replication-options-binary-log.html#option_mysqld_binlog-row-event-max-size">
    <code class="option">
     --binlog-row-event-max-size
    </code>
   </a>
   option 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 8192.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    When using
    <span class="emphasis">
     <em>
      statement-based logging
     </em>
    </span>
    for
          replication, it is possible for the data on the source and
          replica to become different if a statement is designed in such
          a way that the data modification is
    <span class="firstterm">
     nondeterministic
    </span>
    ; that
          is, it is left up to the query optimizer. In general, this is
          not a good practice even outside of replication. For a
          detailed explanation of this issue, see
    <a class="xref" href="known-issues.html" title="B.3.7 Known Issues in MySQL">
     Section B.3.7, “Known Issues in MySQL”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
