<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="reset-replica">
      </a>
      15.4.2.3 RESET REPLICA Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045177145376">
  </a>
  <a class="indexterm" name="idm46045177144304">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72892668"><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 keyword">RESET</span> <span class="token keyword">REPLICA</span> <span class="token punctuation">[</span><span class="token keyword">ALL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">channel_option</em><span class="token punctuation">]</span>

<em class="replaceable">channel_option</em>:
    <span class="token keyword">FOR</span> <span class="token keyword">CHANNEL</span> <span class="token keyword"><em class="replaceable">channel</em></span></code></pre>
  </div>
  <p>
   <code class="literal">
    RESET REPLICA
   </code>
   makes the replica forget its
        position in the source's binary log.
  </p>
  <p>
   This statement is meant to be used for a clean start; it clears
        the replication metadata repositories, deletes all the relay log
        files, and starts a new relay log file. It also resets to 0 the
        replication delay specified with the
   <code class="literal">
    SOURCE_DELAY
   </code>
   option of the
   <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.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    All relay log files are deleted, even if they have not been
          completely executed by the replication SQL thread. (This is a
          condition likely to exist on a replica if you have issued a
    <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
     <code class="literal">
      STOP
          REPLICA
     </code>
    </a>
    statement or if the replica is highly
          loaded.)
   </p>
  </div>
  <p>
   For a server where GTIDs are in use
        (
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
    <code class="literal">
     gtid_mode
    </code>
   </a>
   is
   <code class="literal">
    ON
   </code>
   ), issuing
   <code class="literal">
    RESET REPLICA
   </code>
   has no effect on the GTID execution history. The statement does
        not change the values of
   <code class="literal">
    gtid_executed
   </code>
   or
   <code class="literal">
    gtid_purged
   </code>
   , or the
   <code class="literal">
    mysql.gtid_executed
   </code>
   table. If you need to
        reset the GTID execution history, use
   <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>
   , even if the GTID-enabled server
        is a replica where binary logging is disabled.
  </p>
  <p>
   <code class="literal">
    RESET REPLICA
   </code>
   requires the
   <a class="link" href="privileges-provided.html#priv_reload">
    <code class="literal">
     RELOAD
    </code>
   </a>
   privilege.
  </p>
  <p>
   To use
   <code class="literal">
    RESET REPLICA
   </code>
   , the replication SQL
        thread and replication I/O (receiver) thread must be stopped, so
        on a running replica use
   <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
    <code class="literal">
     STOP
        REPLICA
    </code>
   </a>
   before issuing
   <code class="literal">
    RESET
        REPLICA
   </code>
   . To use
   <code class="literal">
    RESET REPLICA
   </code>
   on a
        Group Replication group member, the member status must be
   <code class="literal">
    OFFLINE
   </code>
   , meaning that the plugin is loaded
        but the member does not currently belong to any group. A group
        member can be taken offline by using a
   <a class="link" href="stop-group-replication.html" title="15.4.3.2 STOP GROUP_REPLICATION Statement">
    <code class="literal">
     STOP
        GROUP REPLICATION
    </code>
   </a>
   statement.
  </p>
  <p>
   The optional
   <code class="literal">
    FOR CHANNEL
    <em class="replaceable">
     <code>
      channel
     </code>
    </em>
   </code>
   clause enables you
        to name which replication channel the statement applies to.
        Providing a
   <code class="literal">
    FOR CHANNEL
    <em class="replaceable">
     <code>
      channel
     </code>
    </em>
   </code>
   clause applies the
   <code class="literal">
    RESET REPLICA
   </code>
   statement to a specific
        replication channel. Combining a
   <code class="literal">
    FOR CHANNEL
    <em class="replaceable">
     <code>
      channel
     </code>
    </em>
   </code>
   clause with the
   <code class="literal">
    ALL
   </code>
   option deletes the specified channel. If
        no channel is named and no extra channels exist, the statement
        applies to the default channel. Issuing a
   <code class="literal">
    RESET REPLICA
        ALL
   </code>
   statement without a
   <code class="literal">
    FOR CHANNEL
    <em class="replaceable">
     <code>
      channel
     </code>
    </em>
   </code>
   clause when
        multiple replication channels exist deletes
   <span class="emphasis">
    <em>
     all
    </em>
   </span>
   replication channels and recreates only
        the default channel. See
   <a class="xref" href="replication-channels.html" title="19.2.2 Replication Channels">
    Section 19.2.2, “Replication Channels”
   </a>
   for more information.
  </p>
  <p>
   <code class="literal">
    RESET REPLICA
   </code>
   does not change any replication
        connection parameters, which include the source's host name and
        port, the replication user account and its password, the
   <code class="literal">
    PRIVILEGE_CHECKS_USER
   </code>
   account, the
   <code class="literal">
    REQUIRE_ROW_FORMAT
   </code>
   option, the
   <code class="literal">
    REQUIRE_TABLE_PRIMARY_KEY_CHECK
   </code>
   option,and
        the
   <code class="literal">
    ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
   </code>
   option. If you want to change any of the replication connection
        parameters, you can do this 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 after the server
        starts. If you want to remove all of the replication connection
        parameters, use
   <code class="literal">
    RESET REPLICA ALL
   </code>
   .
   <code class="literal">
    RESET REPLICA ALL
   </code>
   also clears the
   <code class="literal">
    IGNORE_SERVER_IDS
   </code>
   list set by
   <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>
   .
        When you have used
   <code class="literal">
    RESET REPLICA ALL
   </code>
   , if you
        want to use the instance as a replica again, you need to issue 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 after the server start to specify new connection
        parameters.
  </p>
  <p>
   You can set the
   <code class="literal">
    GTID_ONLY
   </code>
   option on the
   <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 to stop a replication channel from persisting file
        names and file positions in the replication metadata
        repositories. When you issue
   <a class="link" href="reset-replica.html" title="15.4.2.3 RESET REPLICA Statement">
    <code class="literal">
     RESET
        REPLICA
    </code>
   </a>
   , the replication metadata repositories are
        synchronized.
   <code class="literal">
    RESET REPLICA ALL
   </code>
   deletes
        rather than updates the repositories, so they are synchronized
        implicitly.
  </p>
  <p>
   In the event of an unexpected server exit or deliberate restart
        after issuing
   <code class="literal">
    RESET REPLICA
   </code>
   but before
        issuing
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START REPLICA
    </code>
   </a>
   ,
        replication connection parameters are preserved in the
        crash-safe
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables
   <code class="literal">
    mysql.slave_master_info
   </code>
   and
   <code class="literal">
    mysql.slave_relay_log_info
   </code>
   as part of the
   <code class="literal">
    RESET REPLICA
   </code>
   operation. They are also
        retained in memory. In the event of an unexpected server exit or
        deliberate restart after issuing
   <code class="literal">
    RESET
        REPLICA
   </code>
   but before issuing
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START
        REPLICA
    </code>
   </a>
   , the replication connection parameters are
        retrieved from the tables and reapplied to the channel. This
        applies for both the connection and applier metadata
        repositories.
  </p>
  <p>
   <code class="literal">
    RESET REPLICA
   </code>
   does not change any replication
        filter settings (such as
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-table">
    <code class="option">
     --replicate-ignore-table
    </code>
   </a>
   ) for
        channels affected by the statement. However,
   <code class="literal">
    RESET
        REPLICA ALL
   </code>
   removes the replication filters that were
        set on the channels deleted by the statement. When the deleted
        channel or channels are recreated, any global replication
        filters specified for the replica are copied to them, and no
        channel specific replication filters are applied. For more
        information see
   <a class="xref" href="replication-rules-channel-based-filters.html" title="19.2.5.4 Replication Channel Based Filters">
    Section 19.2.5.4, “Replication Channel Based Filters”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    RESET REPLICA
   </code>
   causes an implicit commit of an
        ongoing transaction. See
   <a class="xref" href="implicit-commit.html" title="15.3.3 Statements That Cause an Implicit Commit">
    Section 15.3.3, “Statements That Cause an Implicit Commit”
   </a>
   .
  </p>
  <p>
   If the replication SQL thread was in the middle of replicating
        temporary tables when it was stopped, and
   <code class="literal">
    RESET
        REPLICA
   </code>
   is issued, these replicated temporary tables
        are deleted on the replica.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    When used on an NDB Cluster replica SQL node,
    <code class="literal">
     RESET
          REPLICA
    </code>
    clears the
    <code class="literal">
     mysql.ndb_apply_status
    </code>
    table. You should
          keep in mind when using this statement that
    <code class="literal">
     ndb_apply_status
    </code>
    uses the
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    storage engine and so is
          shared by all SQL nodes attached to the cluster.
   </p>
   <p>
    You can override this behavior by issuing
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    <code class="literal">
     GLOBAL
          @@
    </code>
    <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_clear_apply_status">
     <code class="literal">
      ndb_clear_apply_status=OFF
     </code>
    </a>
    prior to executing
    <code class="literal">
     RESET REPLICA
    </code>
    , which
          keeps the replica from purging the
    <code class="literal">
     ndb_apply_status
    </code>
    table in such cases.
   </p>
  </div>
 </div>
 <br/>
</div>
