<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-mode-change-online-enable-gtids">
      </a>
      19.1.4.2 Enabling GTID Transactions Online
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045145438720">
  </a>
  <p>
   This section describes how to enable GTID transactions, and
      optionally auto-positioning, on servers that are already online
      and using anonymous transactions. 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 enabling GTID transactions that process is easier.
  </p>
  <p>
   You can set up replication channels to assign GTIDs to replicated
      transactions that do not already have any. This feature enables
      replication from a source server that does not use GTID-based
      replication, to a replica that does. If it is possible to enable
      GTIDs on the replication source server, as described in this
      procedure, use this approach instead. Assigning GTIDs is designed
      for replication source servers where you cannot enable GTIDs. For
      more information on this option, see
   <a class="xref" href="replication-gtids-assign-anon.html" title="19.1.3.6 Replication From a Source Without GTIDs to a Replica With GTIDs">
    Section 19.1.3.6, “Replication From a Source Without GTIDs to a Replica With GTIDs”
   </a>
   .
  </p>
  <p>
   Before you start, ensure that
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
    <code class="literal">
     gtid_mode
    </code>
   </a>
   is
   <code class="literal">
    OFF
   </code>
   on all servers.
  </p>
  <p>
   The following procedure can be paused at any time and later
      resumed where it was, or reversed by jumping to the corresponding
      step of
   <a class="xref" href="replication-mode-change-online-disable-gtids.html" title="19.1.4.3 Disabling GTID Transactions Online">
    Section 19.1.4.3, “Disabling GTID Transactions Online”
   </a>
   ,
      the online procedure to disable GTIDs. This makes the procedure
      fault-tolerant because any unrelated issues that may appear in the
      middle of the procedure can be handled as usual, and then the
      procedure continued where it was left off.
  </p>
  <p>
   To enable GTID transactions, you must complete each of the
      following steps before continuing to the next one.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <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="sa78529951"><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.enforce_gtid_consistency</span> <span class="token operator">=</span> WARN<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Let the server run for a while with your normal workload and
          monitor the logs. If this step causes any warnings in the log,
          adjust your application so that it only uses GTID-compatible
          features and does not generate any warnings.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, execute this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10124416"><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.enforce_gtid_consistency</span> <span class="token operator">=</span> <span class="token keyword">ON</span><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="sa34830458"><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>
     <p>
      The order in which the servers execute this statement makes no
          difference, but all servers must do so before beginning the
          next step.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, execute the followng statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33810939"><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>
     <p>
      As in the previous step, it makes no difference which server
          executes the statement first, as long as each server does so
          before proceeding further.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, wait until
      <a class="link" href="server-status-variables.html#statvar_Ongoing_anonymous_transaction_count">
       <code class="literal">
        Ongoing_anonymous_transaction_count
       </code>
      </a>
      is
      <code class="literal">
       0
      </code>
      . You can check its value using a
      <a class="link" href="show-status.html" title="15.7.7.37 SHOW STATUS Statement">
       <code class="literal">
        SHOW STATUS
       </code>
      </a>
      statement, like
          this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73846659"><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 prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'Ongoing%'</span><span class="token punctuation">;</span>
<span class="token output"><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Variable_name                       <span class="token punctuation">|</span> Value <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Ongoing_anonymous_transaction_count <span class="token punctuation">|</span> 0     <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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><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><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><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><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><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><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><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
     </div>
     <p>
      On a replica, it is theoretically possible that this is
      <code class="literal">
       0
      </code>
      and then a nonzero value again. This is
          not a problem, as long as it is
      <code class="literal">
       0
      </code>
      at least
          once.
     </p>
    </li>
    <li class="listitem">
     <p>
      Wait for all transactions generated up to the previous step to
          replicate to all servers. You can do this without stopping
          updates; what matters is that all anonymous transactions are
          replicated before proceeding further.
     </p>
     <p>
      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,
          such as point-in-time backup and restore, wait until you no
          longer need the old binary logs containing transactions
          without GTIDs.
     </p>
     <p>
      For instance, after all transactions have been replicated, 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 backups. Then either explicitly
          take a backup 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 the previous step was completed, and
          for any backup taken before then to expire.
     </p>
     <p>
      Keep in mind that binary logs containing anonymous
          transactions (that is, transactions without GTIDs) cannot be
          used following the next step, after which, you must make sure
          that no transactions without GTIDs remain uncommitted on any
          server.
     </p>
    </li>
    <li class="listitem">
     <p>
      On each server, execute this statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94025864"><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">ON</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      On each server, add
      <code class="literal">
       gtid-mode=ON
      </code>
      and
      <code class="literal">
       enforce-gtid-consistency=ON
      </code>
      to
      <code class="filename">
       my.cnf
      </code>
      . This guarantees that GTIDs are
          used for all transactions which have not already been
          processed. To start using the GTID protocol so that you can
          later perform automatic failover, execute the the next set of
          statements on each replica. If you use multi-source
          replication, do this for each channel, including the
      <code class="literal">
       FOR CHANNEL
       <em class="replaceable">
        <code>
         channel
        </code>
       </em>
      </code>
      clause:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87470205"><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">1</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">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>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
