<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="start-replica">
      </a>
      15.4.2.4 START REPLICA Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045177066176">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48011275"><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">START</span> <span class="token keyword">REPLICA</span> <span class="token punctuation">[</span><em class="replaceable">thread_types</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">until_option</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">connection_options</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">channel_option</em><span class="token punctuation">]</span>

<em class="replaceable">thread_types</em>:
    <span class="token punctuation">[</span><em class="replaceable">thread_type</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">thread_type</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">]</span>

<em class="replaceable">thread_type</em>:
    <span class="token keyword">IO_THREAD</span> <span class="token operator">|</span> <span class="token keyword">SQL_THREAD</span>

<em class="replaceable">until_option</em>:
    <span class="token keyword">UNTIL</span> {   {<span class="token keyword">SQL_BEFORE_GTIDS</span> <span class="token operator">|</span> <span class="token keyword">SQL_AFTER_GTIDS</span>} <span class="token operator">=</span> <em class="replaceable">gtid_set</em>
          <span class="token operator">|</span>   <span class="token keyword">SOURCE_LOG_FILE</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">log_name</em>'</span><span class="token punctuation">,</span> <span class="token keyword">SOURCE_LOG_POS</span> <span class="token operator">=</span> <em class="replaceable">log_pos</em>
          <span class="token operator">|</span>   <span class="token keyword">RELAY_LOG_FILE</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">log_name</em>'</span><span class="token punctuation">,</span> <span class="token keyword">RELAY_LOG_POS</span> <span class="token operator">=</span> <em class="replaceable">log_pos</em>
          <span class="token operator">|</span>   <span class="token keyword">SQL_AFTER_MTS_GAPS</span>  }

<em class="replaceable">connection_options</em>:
    <span class="token punctuation">[</span><span class="token keyword">USER</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">user_name</em>'</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">PASSWORD</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">user_pass</em>'</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT_AUTH</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">plugin_name</em>'</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">PLUGIN_DIR</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">plugin_dir</em>'</span><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>

<em class="replaceable">gtid_set</em>:
    <em class="replaceable">uuid_set</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">uuid_set</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token operator">|</span> <span class="token string">''</span>

<em class="replaceable">uuid_set</em>:
    <em class="replaceable">uuid</em>:<span class="token keyword"><em class="replaceable">interval</em></span><span class="token punctuation">[</span>:<span class="token keyword"><em class="replaceable">interval</em></span><span class="token punctuation">]</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">uuid</em>:
    <em class="replaceable">hhhhhhhh</em><span class="token operator">-</span><em class="replaceable">hhhh</em><span class="token operator">-</span><em class="replaceable">hhhh</em><span class="token operator">-</span><em class="replaceable">hhhh</em><span class="token operator">-</span><em class="replaceable">hhhhhhhhhhhh</em>

<em class="replaceable">h</em>:
    <span class="token punctuation">[</span><span class="token number">0</span><span class="token operator">-</span><span class="token number">9</span><span class="token punctuation">,</span>A<span class="token operator">-</span>F<span class="token punctuation">]</span>

<span class="token keyword"><em class="replaceable">interval</em></span>:
    <em class="replaceable">n</em><span class="token punctuation">[</span><span class="token operator">-</span><em class="replaceable">n</em><span class="token punctuation">]</span>

    <span class="token punctuation">(</span><em class="replaceable">n</em> <span class="token operator">&gt;=</span> <span class="token number">1</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   <code class="literal">
    START REPLICA
   </code>
   starts the replication threads,
        either together or separately.
  </p>
  <p>
   <code class="literal">
    START REPLICA
   </code>
   requires the
   <a class="link" href="privileges-provided.html#priv_replication-slave-admin">
    <code class="literal">
     REPLICATION_SLAVE_ADMIN
    </code>
   </a>
   privilege
        (or the deprecated
   <a class="link" href="privileges-provided.html#priv_super">
    <code class="literal">
     SUPER
    </code>
   </a>
   privilege).
   <code class="literal">
    START 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>
   For the thread type options, you can specify
   <code class="literal">
    IO_THREAD
   </code>
   ,
   <code class="literal">
    SQL_THREAD
   </code>
   ,
        both of these, or neither of them. Only the threads that are
        started are affected by the statement.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       START REPLICA
      </code>
      with no thread type options
            starts all of the replication threads, and so does
      <code class="literal">
       START REPLICA
      </code>
      with both of the thread
            type options.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IO_THREAD
      </code>
      starts the replication receiver
            thread, which reads events from the source server and stores
            them in the relay log.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SQL_THREAD
      </code>
      starts the replication applier
            thread, which reads events from the relay log and executes
            them. A multithreaded replica (with
      <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_workers">
       <code class="literal">
        replica_parallel_workers
       </code>
      </a>
      &gt; 0) applies transactions using a coordinator thread and
            multiple applier threads, and
      <code class="literal">
       SQL_THREAD
      </code>
      starts all of these.
     </p>
    </li>
   </ul>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    <code class="literal">
     START REPLICA
    </code>
    sends an acknowledgment to
          the user after all the replication threads have started.
          However, the replication receiver thread might not yet have
          connected to the source successfully, or an applier thread
          might stop when applying an event right after starting.
    <code class="literal">
     START REPLICA
    </code>
    does not continue to monitor
          the threads after they are started, so it does not warn you if
          they subsequently stop or cannot connect. You must check the
          replica's error log for error messages generated by the
          replication threads, or check that they are running
          satisfactorily with
    <a class="link" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
     <code class="literal">
      SHOW REPLICA
          STATUS
     </code>
    </a>
    . A successful
    <code class="literal">
     START
          REPLICA
    </code>
    statement causes
    <a class="link" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
     <code class="literal">
      SHOW
          REPLICA STATUS
     </code>
    </a>
    to show
    <code class="literal">
     Replica_SQL_Running=Yes
    </code>
    , but it might or
          might not show
    <code class="literal">
     Replica_IO_Running=Yes
    </code>
    ,
          because
    <code class="literal">
     Replica_IO_Running=Yes
    </code>
    is only
          shown if the receiver thread is both running and connected.
          For more information, see
    <a class="xref" href="replication-administration-status.html" title="19.1.7.1 Checking Replication Status">
     Section 19.1.7.1, “Checking Replication Status”
    </a>
    .
   </p>
  </div>
  <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">
    START REPLICA
   </code>
   statement to a specific
        replication channel. If no clause is named and no extra channels
        exist, the statement applies to the default channel. If a
   <code class="literal">
    START REPLICA
   </code>
   statement does not have a
        channel defined when using multiple channels, this statement
        starts the specified threads for all channels. 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>
   The replication channels for Group Replication
        (
   <code class="literal">
    group_replication_applier
   </code>
   and
   <code class="literal">
    group_replication_recovery
   </code>
   ) are managed
        automatically by the server instance.
   <code class="literal">
    START
        REPLICA
   </code>
   cannot be used at all with the
   <code class="literal">
    group_replication_recovery
   </code>
   channel, and
        should only be used with the
   <code class="literal">
    group_replication_applier
   </code>
   channel when Group
        Replication is not running. The
   <code class="literal">
    group_replication_applier
   </code>
   channel only has an
        applier thread and has no receiver thread, so it can be started
        if required by using the
   <code class="literal">
    SQL_THREAD
   </code>
   option
        without the
   <code class="literal">
    IO_THREAD
   </code>
   option.
  </p>
  <p>
   <code class="literal">
    START REPLICA
   </code>
   supports pluggable
        user-password authentication (see
   <a class="xref" href="pluggable-authentication.html" title="8.2.17 Pluggable Authentication">
    Section 8.2.17, “Pluggable Authentication”
   </a>
   ) with the
   <code class="literal">
    USER
   </code>
   ,
   <code class="literal">
    PASSWORD
   </code>
   ,
   <code class="literal">
    DEFAULT_AUTH
   </code>
   and
   <code class="literal">
    PLUGIN_DIR
   </code>
   options, as described in the
        following list. When you use these options, you must start the
        receiver thread (
   <code class="literal">
    IO_THREAD
   </code>
   option) or all the
        replication threads; you cannot start the replication applier
        thread (
   <code class="literal">
    SQL_THREAD
   </code>
   option) alone.
  </p>
  <div class="variablelist">
   <dl class="variablelist">
    <dt>
     <span class="term">
      <code class="literal">
       USER
      </code>
     </span>
    </dt>
    <dd>
     <p>
      The user name for the account. You must set this if
      <code class="literal">
       PASSWORD
      </code>
      is used. The option cannot be
              set to an empty or null string.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       PASSWORD
      </code>
     </span>
    </dt>
    <dd>
     <p>
      The password for the named user account.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       DEFAULT_AUTH
      </code>
     </span>
    </dt>
    <dd>
     <p>
      The name of the authentication plugin. The default is
              MySQL native authentication.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       PLUGIN_DIR
      </code>
     </span>
    </dt>
    <dd>
     <p>
      The location of the authentication plugin.
     </p>
    </dd>
   </dl>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    The password that you set using
    <code class="literal">
     START
          REPLICA
    </code>
    is masked when it is written to MySQL
          Server’s logs, Performance Schema tables, and
    <a class="link" href="show-processlist.html" title="15.7.7.31 SHOW PROCESSLIST Statement">
     <code class="literal">
      SHOW PROCESSLIST
     </code>
    </a>
    statements.
          However, it is sent in plain text over the connection to the
          replica server instance. To protect the password in transit,
          use SSL/TLS encryption, an SSH tunnel, or another method of
          protecting the connection from unauthorized viewing, for the
          connection between the replica server instance and the client
          that you use to issue
    <code class="literal">
     START REPLICA
    </code>
    .
   </p>
  </div>
  <p>
   The
   <code class="literal">
    UNTIL
   </code>
   clause makes the replica start
        replication, then process transactions up to the point that you
        specify in the
   <code class="literal">
    UNTIL
   </code>
   clause, then stop again.
        The
   <code class="literal">
    UNTIL
   </code>
   clause can be used to make a
        replica proceed until just before the point where you want to
        skip a transaction that is unwanted, and then skip the
        transaction as described in
   <a class="xref" href="replication-administration-skip.html" title="19.1.7.3 Skipping Transactions">
    Section 19.1.7.3, “Skipping Transactions”
   </a>
   . To identify a
        transaction, you can use
   <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
        source's binary log or the replica's relay log, or use a
   <a class="link" href="show-binlog-events.html" title="15.7.7.3 SHOW BINLOG EVENTS Statement">
    <code class="literal">
     SHOW BINLOG EVENTS
    </code>
   </a>
   statement.
  </p>
  <p>
   You can also use the
   <code class="literal">
    UNTIL
   </code>
   clause for
        debugging replication by processing transactions one at a time
        or in sections. If you are using the
   <code class="literal">
    UNTIL
   </code>
   clause to do this, start the replica with
   <a class="link" href="replication-options-replica.html#option_mysqld_skip-replica-start">
    <code class="option">
     --skip-replica-start
    </code>
   </a>
   to prevent
        the SQL thread from running when the replica server starts.
        Remove the option or system variable setting after the procedure
        is complete, so that it is not forgotten in the event of an
        unexpected server restart.
  </p>
  <p>
   The
   <a class="link" href="show-replica-status.html" title="15.7.7.35 SHOW REPLICA STATUS Statement">
    <code class="literal">
     SHOW REPLICA STATUS
    </code>
   </a>
   statement
        includes output fields that display the current values of the
   <code class="literal">
    UNTIL
   </code>
   condition. The
   <code class="literal">
    UNTIL
   </code>
   condition lasts for as long as the affected threads are still
        running, and is removed when they stop.
  </p>
  <p>
   The
   <code class="literal">
    UNTIL
   </code>
   clause operates on the replication
        applier thread (
   <code class="literal">
    SQL_THREAD
   </code>
   option). You can
        use the
   <code class="literal">
    SQL_THREAD
   </code>
   option or let the replica
        default to starting both threads. If you use the
   <code class="literal">
    IO_THREAD
   </code>
   option alone, the
   <code class="literal">
    UNTIL
   </code>
   clause is ignored because the applier
        thread is not started.
  </p>
  <p>
   The point that you specify in the
   <code class="literal">
    UNTIL
   </code>
   clause can be any one (and only one) of the following options:
  </p>
  <div class="variablelist">
   <dl class="variablelist">
    <dt>
     <span class="term">
      <code class="literal">
       SOURCE_LOG_FILE
      </code>
      and
      <code class="literal">
       SOURCE_LOG_POS
      </code>
     </span>
    </dt>
    <dd>
     <p>
      These options make the replication applier process
              transactions up to a position in its relay log, identified
              by the file name and file position of the corresponding
              point in the binary log on the source server. The applier
              thread finds the nearest transaction boundary at or after
              the specified position, finishes applying the transaction,
              and stops there. For compressed transaction payloads,
              specify the end position of the compressed
      <code class="literal">
       Transaction_payload_event
      </code>
      .
     </p>
     <p>
      These options can still be used when the
      <code class="literal">
       GTID_ONLY
      </code>
      option was set 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 the replication channel
              from persisting file names and file positions in the
              replication metadata repositories. The file names and file
              positions are tracked in memory.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       RELAY_LOG_FILE
      </code>
      and
      <code class="literal">
       RELAY_LOG_POS
      </code>
     </span>
    </dt>
    <dd>
     <p>
      These options make the replication applier process
              transactions up to a position in the replica’s relay
              log, identified by the relay log file name and a position
              in that file. The applier thread finds the nearest
              transaction boundary at or after the specified position,
              finishes applying the transaction, and stops there. For
              compressed transaction payloads, specify the end position
              of the compressed
      <code class="literal">
       Transaction_payload_event
      </code>
      .
     </p>
     <p>
      These options can still be used when the
      <code class="literal">
       GTID_ONLY
      </code>
      option was set 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 the replication channel
              from persisting file names and file positions in the
              replication metadata repositories. The file names and file
              positions are tracked in memory.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       SQL_BEFORE_GTIDS
      </code>
     </span>
    </dt>
    <dd>
     <p>
      This option makes the replication applier start processing
              transactions and stop when it encounters any transaction
              that is in the specified GTID set. The encountered
              transaction from the GTID set is not applied, and nor are
              any of the other transactions in the GTID set. The option
              takes a GTID set containing one or more global transaction
              identifiers as an argument (see
      <a class="xref" href="replication-gtids-concepts.html#replication-gtids-concepts-gtid-sets" title="GTID Sets">
       GTID Sets
      </a>
      ).
              Transactions in a GTID set do not necessarily appear in
              the replication stream in the order of their GTIDs, so the
              transaction before which the applier stops is not
              necessarily the earliest.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       SQL_AFTER_GTIDS
      </code>
     </span>
    </dt>
    <dd>
     <p>
      This option makes the replication applier start processing
              transactions and stop when it has processed all of the
              transactions in a specified GTID set. The option takes a
              GTID set containing one or more global transaction
              identifiers as an argument (see
      <a class="xref" href="replication-gtids-concepts.html#replication-gtids-concepts-gtid-sets" title="GTID Sets">
       GTID Sets
      </a>
      ).
     </p>
     <p>
      With
      <code class="literal">
       SQL_AFTER_GTIDS
      </code>
      , the replication
              threads stop after they have processed all transactions in
              the GTID set. Transactions are processed in the order
              received, so it is possible that these include
              transactions which are not part of the GTID set, but which
              are received (and processed) before all transactions in
              the set have been committed. For example, executing
      <code class="literal">
       START REPLICA UNTIL SQL_AFTER_GTIDS =
              3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56
      </code>
      causes the replica to obtain (and process) all
              transactions from the source until all of the transactions
              having the sequence numbers 11 through 56 have been
              processed, and then to stop without processing any
              additional transactions after that point has been reached.
     </p>
     <p>
      In older versions of MySQL, this option could not be used
              with
      <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_workers">
       <code class="literal">
        replica_parallel_workers &gt;
              1
       </code>
      </a>
      . In MySQL 8.4, this is no longer
              an issue, and
      <code class="literal">
       SQL_AFTER_GTIDS
      </code>
      can be
              used without causing the replica to fall back into
              single-threaded mode.
     </p>
    </dd>
    <dt>
     <span class="term">
      <code class="literal">
       SQL_AFTER_MTS_GAPS
      </code>
     </span>
    </dt>
    <dd>
     <p>
      For a multithreaded replica only (with
      <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_workers">
       <code class="literal">
        replica_parallel_workers
       </code>
      </a>
      &gt; 0), this option makes the replica process
              transactions up to the point where there are no more gaps
              in the sequence of transactions executed from the relay
              log. When using a multithreaded replica, there is a chance
              of gaps occurring in the following situations:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: disc; ">
       <li class="listitem">
        <p>
         The coordinator thread is stopped.
        </p>
       </li>
       <li class="listitem">
        <p>
         An error occurs in the applier threads.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
          <span class="command">
           <strong>
            mysqld
           </strong>
          </span>
         </a>
         shuts down unexpectedly.
        </p>
       </li>
      </ul>
     </div>
     <p>
      When a replication channel has gaps, the replica’s
              database is in a state that might never have existed on
              the source. The replica tracks the gaps internally and
              disallows
      <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>
      statements that would remove the gap
              information if they executed.
     </p>
     <p>
      All replicas are multithreaded by default. When
      <a class="link" href="replication-options-replica.html#sysvar_replica_preserve_commit_order">
       <code class="literal">
        replica_preserve_commit_order=ON
       </code>
      </a>
      on the replica (the default), gaps should not occur except
              in the specific situations listed in the description for
              this variable. If
      <code class="literal">
       replica_preserve_commit_order
      </code>
      is
      <code class="literal">
       OFF
      </code>
      , the commit order of transactions
              is not preserved, so the chance of gaps occurring is much
              larger.
     </p>
     <p>
      If GTIDs are not in use and you need to change a failed
              multithreaded replica to single-threaded mode, you can
              issue the following series of statements, in the order
              shown:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17560095"><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">START</span> <span class="token keyword">REPLICA</span> <span class="token keyword">UNTIL</span> <span class="token keyword">SQL_AFTER_MTS_GAPS</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@GLOBAL.replica_parallel_workers</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">START</span> <span class="token keyword">REPLICA</span> <span class="token keyword">SQL_THREAD</span><span class="token punctuation">;</span></code></pre>
     </div>
    </dd>
   </dl>
  </div>
 </div>
 <br/>
</div>
