<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-mode-change-online-disable-gtids">
      </a>
      19.1.4.3 Disabling GTID Transactions Online
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045145395808">
  </a>
  <p>
   This section describes how to disable GTID transactions on servers
      that are already online. This procedure does not require taking
      the server offline and is suited to use in production. However, if
      you have the possibility to take the servers offline when
      disabling GTIDs mode that process is easier.
  </p>
  <p>
   The process is similar to enabling GTID transactions while the
      server is online, but reversing the steps. The only thing that
      differs is the point at which you wait for logged transactions to
      replicate.
  </p>
  <p>
   Before starting, all servers must meet the following conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      All servers have
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode
       </code>
      </a>
      set to
      <code class="literal">
       ON
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="replication-options-replica.html#option_mysqld_replicate-same-server-id">
       <code class="option">
        --replicate-same-server-id
       </code>
      </a>
      option is not set on any server. You cannot disable GTID
          transactions if this option is set together with the
      <a class="link" href="replication-options-binary-log.html#sysvar_log_replica_updates">
       <code class="option">
        --log-replica-updates
       </code>
      </a>
      option
          (default) and binary logging is enabled (also the default).
          Without GTIDs, this combination of options causes infinite
          loops in circular replication.
     </p>
    </li>
   </ul>
  </div>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Execute the following statements on each replica, and if you
          are using multi-source replication, do so for each channel,
          including the
      <code class="literal">
       FOR CHANNEL
      </code>
      clause when using
          multi-source replication:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63905681"><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">STOP</span> <span class="token keyword">REPLICA</span> <span class="token punctuation">[</span><span class="token keyword">FOR</span> <span class="token keyword">CHANNEL</span> <span class="token string">'<em class="replaceable">channel</em>'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token keyword">CHANGE</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span> 
  <span class="token keyword">SOURCE_AUTO_POSITION</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">,</span> 
  <span class="token keyword">SOURCE_LOG_FILE</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">file</em>'</span><span class="token punctuation">,</span>
  <span class="token keyword">SOURCE_LOG_POS</span> <span class="token operator">=</span> <em class="replaceable">position</em> 
  <span class="token punctuation">[</span><span class="token keyword">FOR</span> <span class="token keyword">CHANNEL</span> <span class="token string">'<em class="replaceable">channel</em>'</span><span class="token punctuation">]</span><span class="token punctuation">;</span>

<span class="token keyword">START</span> <span class="token keyword">REPLICA</span> <span class="token punctuation">[</span><span class="token keyword">FOR</span> <span class="token keyword">CHANNEL</span> <span class="token string">'<em class="replaceable">channel</em>'</span><span class="token punctuation">]</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You can obtain the values for
      <em class="replaceable">
       <code>
        file
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        position
       </code>
      </em>
      from the
      <code class="literal">
       relay_source_log_file
      </code>
      and
      <code class="literal">
       exec_source_log_position
      </code>
      columns in the
          output of
      <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>
      .
          The
      <em class="replaceable">
       <code>
        file
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        channel
       </code>
      </em>
      names are strings; both of
          these must be quoted when used in the
      <a class="link" href="stop-replica.html" title="15.4.2.5 STOP REPLICA Statement">
       <code class="literal">
        STOP REPLICA
       </code>
      </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>
      ,
          and
      <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
       <code class="literal">
        START REPLICA
       </code>
      </a>
      statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, execute the following statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54749112"><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">SET</span> <span class="token variable">@@global.gtid_mode</span> <span class="token operator">=</span> ON_PERMISSIVE<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      On each server, execute the following statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22579326"><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">SET</span> <span class="token variable">@@global.gtid_mode</span> <span class="token operator">=</span> OFF_PERMISSIVE<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      On each server, wait until the global value of
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_owned">
       <code class="literal">
        gtid_owned
       </code>
      </a>
      is equal to the
          empty string. This can be checked using the statement shown
          here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa30474902"><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">SELECT</span> <span class="token variable">@@global.gtid_owned</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      On a replica, it is theoretically possible that this is empty
          and then becomes nonempty again. This is not a problem; it
          suffices that the value is empty at least once.
     </p>
    </li>
    <li class="listitem">
     <p>
      Wait for all transactions that currently exist in any binary
          log to be committed on all replicas. See
      <a class="xref" href="replication-mode-change-online-verify-transactions.html" title="19.1.4.4 Verifying Replication of Anonymous Transactions">
       Section 19.1.4.4, “Verifying Replication of Anonymous Transactions”
      </a>
      ,
          for one method of checking that all anonymous transactions
          have replicated to all servers.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use binary logs for anything other than
          replication—for example, to perform point-in-time backup
          or restore—wait until you no longer need any old binary
          logs containing GTID transactions.
     </p>
     <p>
      For instance, after the previous step has completed, you can
          execute
      <a class="link" href="flush.html#flush-logs">
       <code class="literal">
        FLUSH LOGS
       </code>
      </a>
      on the
          server where you are taking the backup. Then, either take a
          backup manually, or wait for the next iteration of any
          periodic backup routine you may have set up.
     </p>
     <p>
      Ideally, you should wait for the server to purge all binary
          logs that existed when step 5 was completed, and for any
          backup taken before then to expire.
     </p>
     <p>
      You should keep in mind that logs containing GTID transactions
          cannot be used after the next step. For this reason, before
          proceeding further, you must be sure that no uncommitted GTID
          transactions exist anywhere in the topology.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, execute the following statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92523296"><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">SET</span> <span class="token variable">@@global.gtid_mode</span> <span class="token operator">=</span> <span class="token keyword">OFF</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      On each server, set
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
       <code class="literal">
        gtid_mode=OFF
       </code>
      </a>
      in
      <code class="filename">
       my.cnf
      </code>
      .
     </p>
     <p>
      Optionally, you can also set
      <a class="link" href="replication-options-gtids.html#sysvar_enforce_gtid_consistency">
       <code class="literal">
        enforce_gtid_consistency=OFF
       </code>
      </a>
      .
          After doing so, you should add
      <a class="link" href="replication-options-gtids.html#sysvar_enforce_gtid_consistency">
       <code class="literal">
        enforce_gtid_consistency=OFF
       </code>
      </a>
      to your configuration file.
     </p>
    </li>
   </ol>
  </div>
  <p>
   If you want to downgrade to an earlier version of MySQL, you can
      do so now, using the normal downgrade procedure.
  </p>
 </div>
 <br/>
</div>
