<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="binary-log">
      </a>
      7.4.4 The Binary Log
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="binary-log-formats.html">
       7.4.4.1 Binary Logging Formats
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="binary-log-setting.html">
       7.4.4.2 Setting The Binary Log Format
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="binary-log-mixed.html">
       7.4.4.3 Mixed Binary Logging Format
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="binary-log-mysql-database.html">
       7.4.4.4 Logging Format for Changes to mysql Database Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="binary-log-transaction-compression.html">
       7.4.4.5 Binary Log Transaction Compression
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045258354432">
  </a>
  <a class="indexterm" name="idm46045258353360">
  </a>
  <p>
   The binary log contains
   <span class="quote">
    “
    <span class="quote">
     events
    </span>
    ”
   </span>
   that describe
      database changes such as table creation operations or changes to
      table data. It also contains events for statements that
      potentially could have made changes (for example, a
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   which matched no rows),
      unless row-based logging is used. The binary log also contains
      information about how long each statement took that updated data.
      The binary log has two important purposes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For replication, the binary log on a replication source server
          provides a record of the data changes to be sent to replicas.
          The source sends the information contained in its binary log
          to its replicas, which reproduce those transactions to make
          the same data changes that were made on the source. See
      <a class="xref" href="replication-implementation.html" title="19.2 Replication Implementation">
       Section 19.2, “Replication Implementation”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Certain data recovery operations require use of the binary
          log. After a backup has been restored, the events in the
          binary log that were recorded after the backup was made are
          re-executed. These events bring databases up to date from the
          point of the backup. See
      <a class="xref" href="point-in-time-recovery.html" title="9.5 Point-in-Time (Incremental) Recovery">
       Section 9.5, “Point-in-Time (Incremental) Recovery”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The binary log is not used for statements such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   or
   <a class="link" href="show.html" title="15.7.7 SHOW Statements">
    <code class="literal">
     SHOW
    </code>
   </a>
   that do not modify data. To
      log all statements (for example, to identify a problem query), use
      the general query log. See
   <a class="xref" href="query-log.html" title="7.4.3 The General Query Log">
    Section 7.4.3, “The General Query Log”
   </a>
   .
  </p>
  <p>
   Running a server with binary logging enabled makes performance
      slightly slower. However, the benefits of the binary log in
      enabling you to set up replication and for restore operations
      generally outweigh this minor performance decrement.
  </p>
  <p>
   The binary log is resilient to unexpected halts. Only complete
      events or transactions are logged or read back.
  </p>
  <p>
   Passwords in statements written to the binary log are rewritten by
      the server not to occur literally in plain text. See also
   <a class="xref" href="password-logging.html" title="8.1.2.3 Passwords and Logging">
    Section 8.1.2.3, “Passwords and Logging”
   </a>
   .
  </p>
  <p>
   MySQL binary log files and relay log files can be encrypted,
      helping to protect these files and the potentially sensitive data
      contained in them from being misused by outside attackers, and
      also from unauthorized viewing by users of the operating system
      where they are stored. You enable encryption on a MySQL server by
      setting the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_encryption">
    <code class="literal">
     binlog_encryption
    </code>
   </a>
   system variable to
   <code class="literal">
    ON
   </code>
   . For more information,
      see
   <a class="xref" href="replication-binlog-encryption.html" title="19.3.2 Encrypting Binary Log Files and Relay Log Files">
    Section 19.3.2, “Encrypting Binary Log Files and Relay Log Files”
   </a>
   .
  </p>
  <p>
   The following discussion describes some of the server options and
      variables that affect the operation of binary logging. For a
      complete list, see
   <a class="xref" href="replication-options-binary-log.html" title="19.1.6.4 Binary Logging Options and Variables">
    Section 19.1.6.4, “Binary Logging Options and Variables”
   </a>
   .
  </p>
  <p>
   Binary logging is enabled by default (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
      ON). The exception is if you use
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   to
      initialize the data directory manually by invoking it with the
   <a class="link" href="server-options.html#option_mysqld_initialize">
    <code class="option">
     --initialize
    </code>
   </a>
   or
   <a class="link" href="server-options.html#option_mysqld_initialize-insecure">
    <code class="option">
     --initialize-insecure
    </code>
   </a>
   option, when
      binary logging is disabled by default, but can be enabled by
      specifying the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option.
  </p>
  <p>
   To disable binary logging, you can specify the
   <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>
   option at startup. If either of these options is specified and
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   is also specified, the
      option specified later takes precedence.
  </p>
  <p>
   The
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="option">
     --log-replica-updates
    </code>
   </a>
   and
   <a class="link" href="replication-options-replica.html#sysvar_replica_preserve_commit_order">
    <code class="option">
     --replica-preserve-commit-order
    </code>
   </a>
   options require binary logging. If you disable binary logging,
      either omit these options, or specify
   <code class="option">
    --log-replica-updates=OFF
   </code>
   and
   <code class="option">
    --skip-replica-preserve-commit-order
   </code>
   . MySQL
      disables these options by default when
   <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>
   is specified. If you specify
   <code class="option">
    --log-replica-updates
   </code>
   or
   <a class="link" href="replication-options-replica.html#sysvar_replica_preserve_commit_order">
    <code class="option">
     --replica-preserve-commit-order
    </code>
   </a>
   together 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>
   ,
      a warning or error message is issued.
  </p>
  <p>
   The
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin[=
     <em class="replaceable">
      <code>
       base_name
      </code>
     </em>
     ]
    </code>
   </a>
   option is used to specify the base name for binary log files. If
      you do not supply the
   <code class="option">
    --log-bin
   </code>
   option, MySQL
      uses
   <code class="filename">
    binlog
   </code>
   as the default base name for the
      binary log files. For compatibility with earlier releases, if you
      supply the
   <code class="option">
    --log-bin
   </code>
   option with no string or
      with an empty string, the base name defaults to
   <code class="filename">
    <em class="replaceable">
     <code>
      host_name
     </code>
    </em>
    -bin
   </code>
   ,
      using the name of the host machine. It is recommended that you
      specify a base name, so that if the host name changes, you can
      easily continue to use the same binary log file names (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>
   ). If you supply an extension in the
      log name (for example,
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin=
     <em class="replaceable">
      <code>
       base_name.extension
      </code>
     </em>
    </code>
   </a>
   ),
      the extension is silently removed and ignored.
  </p>
  <p>
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   appends a numeric extension to the
      binary log base name to generate binary log file names. The number
      increases each time the server creates a new log file, thus
      creating an ordered series of files. The server creates a new file
      in the series each time any of the following events occurs:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server is started or restarted
     </p>
    </li>
    <li class="listitem">
     <p>
      The server flushes the logs.
     </p>
    </li>
    <li class="listitem">
     <p>
      The size of the current log file reaches
      <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_size">
       <code class="literal">
        max_binlog_size
       </code>
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   A binary log file may become larger than
   <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_size">
    <code class="literal">
     max_binlog_size
    </code>
   </a>
   if you are using
      large transactions because a transaction is written to the file in
      one piece, never split between files.
  </p>
  <p>
   To keep track of which binary log files have been used,
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   also creates a binary log index file
      that contains the names of the binary log files. By default, this
      has the same base name as the binary log file, with the extension
   <code class="literal">
    '.index'
   </code>
   . You can change the name of the binary
      log index file with the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin-index">
    <code class="option">
     --log-bin-index[=
     <em class="replaceable">
      <code>
       file_name
      </code>
     </em>
     ]
    </code>
   </a>
   option. You should not manually edit this file while
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   is running; doing so would confuse
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   The term
   <span class="quote">
    “
    <span class="quote">
     binary log file
    </span>
    ”
   </span>
   generally denotes an
      individual numbered file containing database events. The term
   <span class="quote">
    “
    <span class="quote">
     binary log
    </span>
    ”
   </span>
   collectively denotes the set of numbered
      binary log files plus the index file.
  </p>
  <p>
   The default location for binary log files and the binary log index
      file is the data directory. You can use the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option to specify an
      alternative location, by adding a leading absolute path name to
      the base name to specify a different directory. When the server
      reads an entry from the binary log index file, which tracks the
      binary log files that have been used, it checks whether the entry
      contains a relative path. If it does, the relative part of the
      path is replaced with the absolute path set using the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option. An absolute path
      recorded in the binary log index file remains unchanged; in such a
      case, the index file must be edited manually to enable a new path
      or paths to be used. The binary log file base name and any
      specified path are available as the
   <a class="link" href="replication-options-binary-log.html#sysvar_log_bin_basename">
    <code class="literal">
     log_bin_basename
    </code>
   </a>
   system variable.
  </p>
  <p>
   The server can be started with the default server ID when binary
      logging is enabled, but an informational message is issued if you
      do not specify a server ID explicitly using the
   <a class="link" href="replication-options.html#sysvar_server_id">
    <code class="literal">
     server_id
    </code>
   </a>
   system variable. For
      servers that are used in a replication topology, you must specify
      a unique nonzero server ID for each server.
  </p>
  <p>
   A client that has 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>
   ) can disable binary
      logging of its own statements by using a
   <a class="link" href="set-sql-log-bin.html" title="15.4.1.3 SET sql_log_bin Statement">
    <code class="literal">
     SET
      sql_log_bin=OFF
    </code>
   </a>
   statement.
  </p>
  <p>
   By default, the server logs the length of the event as well as the
      event itself and uses this to verify that the event was written
      correctly. You can also cause the server to write checksums for
      the events by setting the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_checksum">
    <code class="literal">
     binlog_checksum
    </code>
   </a>
   system variable.
      When reading back from the binary log, the source uses the event
      length by default, but can be made to use checksums if available
      by enabling
   <a class="link" href="replication-options-binary-log.html#sysvar_source_verify_checksum">
    <code class="literal">
     source_verify_checksum
    </code>
   </a>
   . The
      replication I/O (receiver) thread on the replica also verifies
      events received from the source. You can cause the replication SQL
      (applier) thread to use checksums if available when reading from
      the relay log by enabling
   <a class="link" href="replication-options-replica.html#sysvar_replica_sql_verify_checksum">
    <code class="literal">
     replica_sql_verify_checksum
    </code>
   </a>
   .
  </p>
  <p>
   The format of the events recorded in the binary log is dependent
      on the binary logging format. Three format types are supported:
      row-based logging, statement-based logging and mixed-base logging.
      The binary logging format used depends on the MySQL version. For
      descriptions of the logging formats, see
   <a class="xref" href="binary-log-formats.html" title="7.4.4.1 Binary Logging Formats">
    Section 7.4.4.1, “Binary Logging Formats”
   </a>
   .
  </p>
  <p>
   The server evaluates the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_binlog-do-db">
    <code class="option">
     --binlog-do-db
    </code>
   </a>
   and
   <a class="link" href="replication-options-binary-log.html#option_mysqld_binlog-ignore-db">
    <code class="option">
     --binlog-ignore-db
    </code>
   </a>
   options in the
      same way as it does the
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-db">
    <code class="option">
     --replicate-do-db
    </code>
   </a>
   and
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
    <code class="option">
     --replicate-ignore-db
    </code>
   </a>
   options. For
      information about how this is done, see
   <a class="xref" href="replication-rules-db-options.html" title="19.2.5.1 Evaluation of Database-Level Replication and Binary Logging Options">
    Section 19.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”
   </a>
   .
  </p>
  <p>
   A replica is started with
   <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
    <code class="literal">
     log_replica_updates
    </code>
   </a>
   enabled by
      default, meaning that the replica writes to its own binary log any
      data modifications that are received from the source. The binary
      log must be enabled for this setting to work (see
   <a class="xref" href="replication-options-replica.html" title="19.1.6.3 Replica Server Options and Variables">
    Section 19.1.6.3, “Replica Server Options and Variables”
   </a>
   ). This setting
      enables the replica to act as a source to other replicas.
  </p>
  <p>
   You can delete all binary log files with the
   <a class="link" href="reset-binary-logs-and-gtids.html" title="15.4.1.2 RESET BINARY LOGS AND GTIDS Statement">
    <code class="literal">
     RESET BINARY LOGS AND GTIDS
    </code>
   </a>
   statement, or a subset of them with
   <a class="link" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    <code class="literal">
     PURGE
      BINARY LOGS
    </code>
   </a>
   . See
   <a class="xref" href="reset.html" title="15.7.8.6 RESET Statement">
    Section 15.7.8.6, “RESET Statement”
   </a>
   , and
   <a class="xref" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    Section 15.4.1.1, “PURGE BINARY LOGS Statement”
   </a>
   .
  </p>
  <p>
   If you are using MySQL Replication, you should not delete old
      binary log files on the source until you are sure that no replica
      still needs to use them. For example, if your replicas never run
      more than three days behind, once a day you can execute
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin flush-logs binary
     </strong>
    </span>
   </a>
   on the source and
      then remove any logs that are more than three days old. You can
      remove the files manually, but it is preferable to use
   <a class="link" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    <code class="literal">
     PURGE BINARY LOGS
    </code>
   </a>
   , which also
      safely updates the binary log index file for you (and which can
      take a date argument). See
   <a class="xref" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
    Section 15.4.1.1, “PURGE BINARY LOGS Statement”
   </a>
   .
  </p>
  <p>
   You can display the contents of binary log files with the
   <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>
   utility. This can be useful when
      you want to reprocess statements in the log for a recovery
      operation. For example, you can update a MySQL server from the
      binary log as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa23452066"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqlbinlog</span> <em class="replaceable">log_file</em> | mysql <span class="token property">-h</span> <em class="replaceable">server_name</em></code></pre>
  </div>
  <p>
   <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>
   also can be used to display the
      contents of the relay log file on a replica, because they are
      written using the same format as binary log files. For more
      information on the
   <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>
   utility and how
      to use it, see
   <a class="xref" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
    Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”
   </a>
   . For more information
      about the binary log and recovery operations, see
   <a class="xref" href="point-in-time-recovery.html" title="9.5 Point-in-Time (Incremental) Recovery">
    Section 9.5, “Point-in-Time (Incremental) Recovery”
   </a>
   .
  </p>
  <p>
   Binary logging is done immediately after a statement or
      transaction completes but before any locks are released or any
      commit is done. This ensures that the log is logged in commit
      order.
  </p>
  <p>
   Updates to nontransactional tables are stored in the binary log
      immediately after execution.
  </p>
  <p>
   Within an uncommitted transaction, all updates
      (
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   , or
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ) that change transactional
      tables such as
   <code class="literal">
    InnoDB
   </code>
   tables are cached until a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   statement is received by the
      server. At that point,
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   writes the entire
      transaction to the binary log before the
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   is executed.
  </p>
  <p>
   Modifications to nontransactional tables cannot be rolled back. If
      a transaction that is rolled back includes modifications to
      nontransactional tables, the entire transaction is logged with a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   statement at the end to ensure that the modifications to those
      tables are replicated.
  </p>
  <p>
   When a thread that handles the transaction starts, it allocates a
      buffer of
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_cache_size">
    <code class="literal">
     binlog_cache_size
    </code>
   </a>
   to
      buffer statements. If a statement is bigger than this, the thread
      opens a temporary file to store the transaction. The temporary
      file is deleted when the thread ends. If binary log encryption is
      active on the server, the temporary file is encrypted.
  </p>
  <p>
   The
   <a class="link" href="server-status-variables.html#statvar_Binlog_cache_use">
    <code class="literal">
     Binlog_cache_use
    </code>
   </a>
   status
      variable shows the number of transactions that used this buffer
      (and possibly a temporary file) for storing statements. The
   <a class="link" href="server-status-variables.html#statvar_Binlog_cache_disk_use">
    <code class="literal">
     Binlog_cache_disk_use
    </code>
   </a>
   status
      variable shows how many of those transactions actually had to use
      a temporary file. These two variables can be used for tuning
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_cache_size">
    <code class="literal">
     binlog_cache_size
    </code>
   </a>
   to a large
      enough value that avoids the use of temporary files.
  </p>
  <p>
   The
   <a class="link" href="replication-options-binary-log.html#sysvar_max_binlog_cache_size">
    <code class="literal">
     max_binlog_cache_size
    </code>
   </a>
   system
      variable (default 4GB, which is also the maximum) can be used to
      restrict the total size used to cache a multiple-statement
      transaction. If a transaction is larger than this many bytes, it
      fails and rolls back. The minimum value is 4096.
  </p>
  <p>
   If you are using the binary log and row based logging, concurrent
      inserts are converted to normal inserts for
   <code class="literal">
    CREATE ...
      SELECT
   </code>
   or
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
      SELECT
    </code>
   </a>
   statements. This is done to ensure that you can
      re-create an exact copy of your tables by applying the log during
      a backup operation. If you are using statement-based logging, the
      original statement is written to the log.
  </p>
  <p>
   The binary log format has some known limitations that can affect
      recovery from backups. See
   <a class="xref" href="replication-features.html" title="19.5.1 Replication Features and Issues">
    Section 19.5.1, “Replication Features and Issues”
   </a>
   .
  </p>
  <p>
   Binary logging for stored programs is done as described in
   <a class="xref" href="stored-programs-logging.html" title="27.7 Stored Program Binary Logging">
    Section 27.7, “Stored Program Binary Logging”
   </a>
   .
  </p>
  <p>
   Note that the binary log format differs in MySQL 8.4
      from previous versions of MySQL, due to enhancements in
      replication. See
   <a class="xref" href="replication-compatibility.html" title="19.5.2 Replication Compatibility Between MySQL Versions">
    Section 19.5.2, “Replication Compatibility Between MySQL Versions”
   </a>
   .
  </p>
  <p>
   If the server is unable to write to the binary log, flush binary
      log files, or synchronize the binary log to disk, the binary log
      on the replication source server can become inconsistent and
      replicas can lose synchronization with the source. The
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_error_action">
    <code class="literal">
     binlog_error_action
    </code>
   </a>
   system
      variable controls the action taken if an error of this type is
      encountered with the binary log.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The default setting,
      <code class="literal">
       ABORT_SERVER
      </code>
      , makes
          the server halt binary logging and shut down. At this point,
          you can identify and correct the cause of the error. On
          restart, recovery proceeds as in the case of an unexpected
          server halt (see
      <a class="xref" href="replication-solutions-unexpected-replica-halt.html" title="19.4.2 Handling an Unexpected Halt of a Replica">
       Section 19.4.2, “Handling an Unexpected Halt of a Replica”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      The setting
      <code class="literal">
       IGNORE_ERROR
      </code>
      provides backward
          compatibility with older versions of MySQL. With this setting,
          the server continues the ongoing transaction and logs the
          error, then halts binary logging, but continues to perform
          updates. At this point, you can identify and correct the cause
          of the error. To resume binary logging,
      <a class="link" href="replication-options-binary-log.html#sysvar_log_bin">
       <code class="literal">
        log_bin
       </code>
      </a>
      must be enabled
          again, which requires a server restart. Only use this option
          if you require backward compatibility, and the binary log is
          non-essential on this MySQL server instance. For example, you
          might use the binary log only for intermittent auditing or
          debugging of the server, and not use it for replication from
          the server or rely on it for point-in-time restore operations.
     </p>
    </li>
   </ul>
  </div>
  <p>
   By default, the binary log is synchronized to disk at each write
      (
   <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
    <code class="literal">
     sync_binlog=1
    </code>
   </a>
   ). If
   <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
    <code class="literal">
     sync_binlog
    </code>
   </a>
   was not enabled, and
      the operating system or machine (not only the MySQL server)
      crashed, there is a chance that the last statements of the binary
      log could be lost. To prevent this, enable the
   <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
    <code class="literal">
     sync_binlog
    </code>
   </a>
   system variable to
      synchronize the binary log to disk after every
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   commit groups. See
   <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
    Section 7.1.8, “Server System Variables”
   </a>
   . The safest value for
   <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
    <code class="literal">
     sync_binlog
    </code>
   </a>
   is 1 (the default),
      but this is also the slowest.
  </p>
  <p>
   In earlier MySQL releases, there was a chance of inconsistency
      between the table content and binary log content if a crash
      occurred, even with
   <a class="link" href="replication-options-binary-log.html#sysvar_sync_binlog">
    <code class="literal">
     sync_binlog
    </code>
   </a>
   set to 1. For example, if you are using
   <code class="literal">
    InnoDB
   </code>
   tables and the MySQL server processes a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   statement, it writes many
      prepared transactions to the binary log in sequence, synchronizes
      the binary log, and then commits the transaction into
   <code class="literal">
    InnoDB
   </code>
   . If the server unexpectedly exited
      between those two operations, the transaction would be rolled back
      by
   <code class="literal">
    InnoDB
   </code>
   at restart but still exist in the
      binary log. Such an issue was resolved in previous releases by
      enabling
   <code class="literal">
    InnoDB
   </code>
   support for two-phase commit in
      XA transactions. In MySQL 8.4,
   <code class="literal">
    InnoDB
   </code>
   support for two-phase commit in XA
      transactions is always enabled.
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   support for two-phase commit in XA
      transactions ensures that the binary log and
   <code class="literal">
    InnoDB
   </code>
   data files are synchronized. However,
      the MySQL server should also be configured to synchronize the
      binary log and the
   <code class="literal">
    InnoDB
   </code>
   logs to disk before
      committing the transaction. The
   <code class="literal">
    InnoDB
   </code>
   logs are
      synchronized by default, and
   <code class="literal">
    sync_binlog=1
   </code>
   ensures the binary log is synchronized. The effect of implicit
   <code class="literal">
    InnoDB
   </code>
   support for two-phase commit in XA
      transactions and
   <code class="literal">
    sync_binlog=1
   </code>
   is that at
      restart after a crash, after doing a rollback of transactions, the
      MySQL server scans the latest binary log file to collect
      transaction
   <em class="replaceable">
    <code>
     xid
    </code>
   </em>
   values and calculate
      the last valid position in the binary log file. The MySQL server
      then tells
   <code class="literal">
    InnoDB
   </code>
   to complete any prepared
      transactions that were successfully written to the to the binary
      log, and truncates the binary log to the last valid position. This
      ensures that the binary log reflects the exact data of
   <code class="literal">
    InnoDB
   </code>
   tables, and therefore the replica
      remains in synchrony with the source because it does not receive a
      statement which has been rolled back.
  </p>
  <p>
   If the MySQL server discovers at crash recovery that the binary
      log is shorter than it should have been, it lacks at least one
      successfully committed
   <code class="literal">
    InnoDB
   </code>
   transaction. This
      should not happen if
   <code class="literal">
    sync_binlog=1
   </code>
   and the
      disk/file system do an actual sync when they are requested to
      (some do not), so the server prints an error message
   <code class="literal">
    The
      binary log
    <em class="replaceable">
     <code>
      file_name
     </code>
    </em>
    is shorter than
      its expected size
   </code>
   . In this case, this binary log is not
      correct and replication should be restarted from a fresh snapshot
      of the source's data.
  </p>
  <p>
   The session values of the following system variables are written
      to the binary log and honored by the replica when parsing the
      binary log:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_sql_mode">
       <code class="literal">
        sql_mode
       </code>
      </a>
      (except that the
      <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
       <code class="literal">
        NO_DIR_IN_CREATE
       </code>
      </a>
      mode is not
          replicated; see
      <a class="xref" href="replication-features-variables.html" title="19.5.1.39 Replication and Variables">
       Section 19.5.1.39, “Replication and Variables”
      </a>
      )
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
       <code class="literal">
        foreign_key_checks
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_unique_checks">
       <code class="literal">
        unique_checks
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_character_set_client">
       <code class="literal">
        character_set_client
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_connection">
       <code class="literal">
        collation_connection
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_database">
       <code class="literal">
        collation_database
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_collation_server">
       <code class="literal">
        collation_server
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="server-system-variables.html#sysvar_sql_auto_is_null">
       <code class="literal">
        sql_auto_is_null
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
