<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="replication-solutions-unexpected-replica-halt">
      </a>
      19.4.2 Handling an Unexpected Halt of a Replica
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045136674160">
  </a>
  <a class="indexterm" name="idm46045136673088">
  </a>
  <a class="indexterm" name="idm46045136671600">
  </a>
  <p>
   In order for replication to be resilient to unexpected halts of
      the server (sometimes described as crash-safe) it must be possible
      for the replica to recover its state before halting. This section
      describes the impact of an unexpected halt of a replica during
      replication, and how to configure a replica for the best chance of
      recovery to continue replication.
  </p>
  <p>
   After an unexpected halt of a replica, upon restart the
      replication SQL thread must recover information about which
      transactions have been executed already. The information required
      for recovery is stored in the replica's applier metadata
      repository. This repository is created by default as an
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   table named
   <code class="literal">
    mysql.slave_relay_log_info
   </code>
   . By using this
      transactional storage engine the information is always recoverable
      upon restart. Updates to the applier metadata repository are
      committed together with the transactions, meaning that the
      replica's progress information recorded in that repository is
      always consistent with what has been applied to the database, even
      in the event of an unexpected server halt. For more information on
      the applier metadata repository, see
   <a class="xref" href="replica-logs.html" title="19.2.4 Relay Log and Replication Metadata Repositories">
    Section 19.2.4, “Relay Log and Replication Metadata Repositories”
   </a>
   .
  </p>
  <p>
   DML transactions and also atomic DDL update the replication
      positions in the replica's applier metadata repository in the
   <code class="literal">
    mysql.slave_relay_log_info
   </code>
   table together with
      applying the changes to the database, as an atomic operation. In
      all other cases, including DDL statements that are not fully
      atomic, and exempted storage engines that do not support atomic
      DDL, the
   <code class="literal">
    mysql.slave_relay_log_info
   </code>
   table might
      be missing updates associated with replicated data if the server
      halts unexpectedly. Restoring updates in this case is a manual
      process. For details on atomic DDL support in MySQL
      8.4, and the resulting behavior for the replication
      of certain statements, see
   <a class="xref" href="atomic-ddl.html" title="15.1.1 Atomic Data Definition Statement Support">
    Section 15.1.1, “Atomic Data Definition Statement Support”
   </a>
   .
  </p>
  <p>
   The recovery process by which a replica recovers from an
      unexpected halt varies depending on the configuration of the
      replica. The details of the recovery process are influenced by the
      chosen method of replication, whether the replica is
      single-threaded or multithreaded, and the setting of relevant
      system variables. The overall aim of the recovery process is to
      identify what transactions had already been applied on the
      replica's database before the unexpected halt occurred, and
      retrieve and apply the transactions that the replica missed
      following the unexpected halt.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For GTID-based replication, the recovery process needs the
          GTIDs of the transactions that were already received or
          committed by the replica. The missing transactions can be
          retrieved from the source using GTID auto-positioning, which
          automatically compares the source's transactions to the
          replica's transactions and identifies the missing
          transactions.
     </p>
    </li>
    <li class="listitem">
     <p>
      For file position based replication, the recovery process
          needs an accurate replication SQL thread (applier) position
          showing the last transaction that was applied on the replica.
          Based on that position, the replication I/O thread (receiver)
          retrieves from the source's binary log all of the transactions
          that should be applied on the replica from that point on.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Using GTID-based replication makes it easiest to configure
      replication to be resilient to unexpected halts. GTID
      auto-positioning means the replica can reliably identify and
      retrieve missing transactions, even if there are gaps in the
      sequence of applied transactions.
  </p>
  <p>
   The following information provides combinations of settings that
      are appropriate for different types of replica to guarantee
      recovery as far as this is under the control of replication.
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Some factors outside the control of replication can have an
        impact on the replication recovery process and the overall state
        of replication after the recovery process. In particular, the
        settings that influence the recovery process for individual
        storage engines might result in transactions being lost in the
        event of an unexpected halt of a replica, and therefore
        unavailable to the replication recovery process. The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit">
     <code class="literal">
      innodb_flush_log_at_trx_commit=1
     </code>
    </a>
    setting mentioned in the list below is a key setting for a
        replication setup that uses
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    with transactions. However, other settings specific to
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    or to other storage engines,
        especially those relating to flushing or synchronization, can
        also have an impact. Always check for and apply recommendations
        made by your chosen storage engines about crash-safe settings.
   </p>
  </div>
  <p>
   The following combination of settings on a replica is the most
      resilient to unexpected halts:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When GTID-based replication is in use
          (
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode=ON
       </code>
      </a>
      ), set
      <code class="literal">
       SOURCE_AUTO_POSITION=1
      </code>
      , which activates
          GTID auto-positioning for the connection to the source to
          automatically identify and retrieve missing transactions. This
          option is set using a
      <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>
      statement. If the replica has multiple
          replication channels, you need to set this option for each
          channel individually. For details of how GTID auto-positioning
          works, see
      <a class="xref" href="replication-gtids-auto-positioning.html" title="19.1.3.3 GTID Auto-Positioning">
       Section 19.1.3.3, “GTID Auto-Positioning”
      </a>
      . When
          file position based replication is in use,
      <code class="literal">
       SOURCE_AUTO_POSITION=1
      </code>
      is not used, and
          instead the binary log position or relay log position is used
          to control where replication starts.
     </p>
    </li>
    <li class="listitem">
     <p>
      When GTID-based replication is in use
          (
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode=ON
       </code>
      </a>
      ), set
      <code class="literal">
       GTID_ONLY=1
      </code>
      , which makes the replica use
          only GTIDs in the recovery process, and stop persisting binary
          log and relay log file names and file positions in the
          replication metadata repositories. This option is set using a
      <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>
      statement. If the replica has multiple replication channels,
          you need to set this option for each channel individually.
          With
      <code class="literal">
       GTID_ONLY=1
      </code>
      , during recovery, the file
          position information is ignored and GTID auto-skip is used to
          skip transactions that have already been supplied, rather than
          identifying the correct file position. This strategy is more
          efficient provided that you purge relay logs using the default
          setting for
      <a class="link" href="replication-options-replica.html#sysvar_relay_log_purge">
       <code class="literal">
        relay_log_purge
       </code>
      </a>
      ,
          which means only one relay log file needs to be inspected.
     </p>
    </li>
    <li class="listitem">
     <p>
      Set
      <a class="link" href="replication-options-replica.html#sysvar_sync_relay_log">
       <code class="literal">
        sync_relay_log=1
       </code>
      </a>
      , which
          instructs the replication receiver thread to synchronize the
          relay log to disk after each received transaction is written
          to it. This means the replica's record of the current position
          read from the source's binary log (in the applier metadata
          repository) is never ahead of the record of transactions saved
          in the relay log. Note that although this setting is the
          safest, it is also the slowest due to the number of disk
          writes involved. With
      <code class="literal">
       sync_relay_log &gt; 1
      </code>
      ,
          or
      <code class="literal">
       sync_relay_log=0
      </code>
      (where synchronization
          is handled by the operating system), in the event of an
          unexpected halt of a replica there might be committed
          transactions that have not been synchronized to disk. Such
          transactions can cause the recovery process to fail if the
          recovering replica, based on the information it has in the
          relay log as last synchronized to disk, tries to retrieve and
          apply the transactions again instead of skipping them. Setting
      <code class="literal">
       sync_relay_log=1
      </code>
      is particularly important
          for a multi-threaded replica, where the recovery process fails
          if gaps in the sequence of transactions cannot be filled using
          the information in the relay log. For a single-threaded
          replica, the recovery process only needs to use the relay log
          if the relevant information is not available in the applier
          metadata repository.
     </p>
    </li>
    <li class="listitem">
     <p>
      Set
      <a class="link" href="innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit">
       <code class="literal">
        innodb_flush_log_at_trx_commit=1
       </code>
      </a>
      ,
          which synchronizes the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      logs to disk before each transaction is committed. This
          setting, which is the default, ensures that
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables and the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      logs are saved on disk so
          that there is no longer a requirement for the information in
          the relay log regarding the transaction. Combined with the
          setting
      <a class="link" href="replication-options-replica.html#sysvar_sync_relay_log">
       <code class="literal">
        sync_relay_log=1
       </code>
      </a>
      ,
          this setting further ensures that the content of the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables and the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      logs is consistent with
          the content of the relay log at all times, so that purging the
          relay log files cannot cause unfillable gaps in the replica's
          history of transactions in the event of an unexpected halt.
     </p>
    </li>
    <li class="listitem">
     <p>
      Set
      <a class="ulink" href="/doc/refman/8.0/en/replication-options-replica.html#sysvar_relay_log_info_repository" target="_top">
       <code class="literal">
        relay_log_info_repository =
          TABLE
       </code>
      </a>
      , which stores the replication SQL thread
          position in the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      table
      <code class="literal">
       mysql.slave_relay_log_info
      </code>
      , and updates it
          together with the transaction commit to ensure a record that
          is always accurate. This setting is the default;
      <code class="literal">
       FILE
      </code>
      is deprecated. The system variable
          itself is also deprecated, so omit it and allow it to assume
          the default. If
      <code class="literal">
       FILE
      </code>
      is used, the
          information is stored in a file in the data directory that is
          updated after the transaction has been applied. This creates a
          risk of losing synchrony with the source depending at which
          stage of processing a transaction the replica halts at, or
          even corruption of the file itself. With
      <a class="ulink" href="/doc/refman/8.0/en/replication-options-replica.html#sysvar_relay_log_info_repository" target="_top">
       <code class="literal">
        relay_log_info_repository =
          FILE
       </code>
      </a>
      , recovery is not guaranteed.
     </p>
    </li>
    <li class="listitem">
     <p>
      Set
      <a class="link" href="replication-options-replica.html#sysvar_relay_log_recovery">
       <code class="literal">
        relay_log_recovery = ON
       </code>
      </a>
      ,
          which enables automatic relay log recovery immediately
          following server startup. This global variable defaults to
      <code class="literal">
       OFF
      </code>
      and is read-only at runtime, but you
          can set it to
      <code class="literal">
       ON
      </code>
      with the
      <a class="link" href="replication-options-replica.html#sysvar_relay_log_recovery">
       <code class="option">
        --relay-log-recovery
       </code>
      </a>
      option at
          replica startup following an unexpected halt of a replica.
          Note that this setting ignores the existing relay log files,
          in case they are corrupted or inconsistent. The relay log
          recovery process starts a new relay log file and fetches
          transactions from the source beginning at the replication SQL
          thread position recorded in the applier metadata repository.
          The previous relay log files are removed over time by the
          replica's normal purge mechanism.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For a multithreaded replica, setting
   <a class="link" href="replication-options-replica.html#sysvar_relay_log_recovery">
    <code class="literal">
     relay_log_recovery = ON
    </code>
   </a>
   automatically handles any inconsistencies and gaps in the sequence
      of transactions that have been executed from the relay log. These
      gaps can occur when file position based replication is in use.
      (For more details, see
   <a class="xref" href="replication-features-transaction-inconsistencies.html" title="19.5.1.34 Replication and Transaction Inconsistencies">
    Section 19.5.1.34, “Replication and Transaction Inconsistencies”
   </a>
   .)
      The relay log recovery process deals with gaps using the same
      method as the
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START
      REPLICA UNTIL SQL_AFTER_MTS_GAPS
    </code>
   </a>
   statement would. When
      the replica reaches a consistent gap-free state, the relay log
      recovery process goes on to fetch further transactions from the
      source beginning at the replication SQL thread position. When
      GTID-based replication is in use, a multithreaded replica checks
      first whether
   <code class="literal">
    SOURCE_AUTO_POSITION
   </code>
   is set to
   <code class="literal">
    ON
   </code>
   , and if it is, omits the step of calculating
      the transactions that should be skipped or not skipped, so that
      the old relay logs are not required for the recovery process.
  </p>
 </div>
 <br/>
</div>
