<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-multi-source-provision-replica">
      </a>
      19.1.5.2 Provisioning a Multi-Source Replica for GTID-Based Replication
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045145289248">
  </a>
  <p>
   If the sources in the multi-source replication topology have
      existing data, it can save time to provision the replica with the
      relevant data before starting replication. In a multi-source
      replication topology, cloning or copying of the data directory
      cannot be used to provision the replica with data from all of the
      sources, and you might also want to replicate only specific
      databases from each source. The best strategy for provisioning
      such a replica is therefore to use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to
      create an appropriate dump file on each source, then use the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client to import the dump file on the
      replica.
  </p>
  <p>
   If you are using GTID-based replication, you need to pay attention
      to the
   <code class="literal">
    SET @@GLOBAL.gtid_purged
   </code>
   statement that
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   places in the dump output. This
      statement transfers the GTIDs for the transactions executed on the
      source to the replica, and the replica requires this information.
      However, for any case more complex than provisioning one new,
      empty replica from one source, you need to check what effect the
      statement has in the version of MySQL used by the replica, and
      handle the statement accordingly. The following guidance
      summarizes suitable actions, but for more details, see the
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   documentation.
  </p>
  <p>
   <code class="literal">
    SET @@GLOBAL.gtid_purged
   </code>
   adds the GTID set from
      the dump file to the existing
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
    <code class="literal">
     gtid_purged
    </code>
   </a>
   set on the replica.
      The statement can therefore potentially be left in the dump output
      when you replay the dump files on the replica, and the dump files
      can be replayed at different times. However, it is important to
      note that the value that is included by
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   for the
   <code class="literal">
    SET
      @@GLOBAL.gtid_purged
   </code>
   statement includes the GTIDs of all
      transactions in the
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
    <code class="literal">
     gtid_executed
    </code>
   </a>
   set on the source, even those that changed suppressed parts of the
      database, or other databases on the server that were not included
      in a partial dump. If you replay a second or subsequent dump file
      on the replica that contains any of the same GTIDs (for example,
      another partial dump from the same source, or a dump from another
      source that has overlapping transactions), any
   <code class="literal">
    SET
      @@GLOBAL.gtid_purged
   </code>
   statement in the second dump file
      fails, and must therefore be removed from the dump output.
  </p>
  <p>
   As an alternative to removing the
   <code class="literal">
    SET
      @@GLOBAL.gtid_purged
   </code>
   statement, you caninoke
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   with
   <a class="link" href="mysqldump.html#option_mysqldump_set-gtid-purged">
    <code class="option">
     --set-gtid-purged=COMMENTED
    </code>
   </a>
   to
      include the statement encased in SQL comments, so that it is not
      performed when you load the dump file. If you are provisioning the
      replica with two partial dumps from the same source, and the GTID
      set in the second dump is the same as the first (so no new
      transactions have been executed on the source in between the
      dumps), you can set
   <code class="option">
    --set-gtid-purged=OFF
   </code>
   instead
      when you export the second dump file, to omit the statement.
  </p>
  <p>
   In the following provisioning example, we assume that the
   <code class="literal">
    SET @@GLOBAL.gtid_purged
   </code>
   statement cannot be
      left in the dump output, and must be removed from the files and
      handled manually. We also assume that there are no wanted
      transactions with GTIDs on the replica before provisioning starts.
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      To create dump files for a database named
      <code class="literal">
       db1
      </code>
      on
      <code class="literal">
       source1
      </code>
      and a
          database named
      <code class="literal">
       db2
      </code>
      on
      <code class="literal">
       source2
      </code>
      , run
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      for
      <code class="literal">
       source1
      </code>
      as follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa63011546"><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-terminal">mysqldump <span class="token property">-u&lt;<em class="replaceable">user</em>&gt;</span> <span class="token property">-p&lt;<em class="replaceable">password</em>&gt;</span> <span class="token property">--single-transaction</span> <span class="token property">--triggers</span> <span class="token property">--routines</span> <span class="token constant">--set-gtid-purged</span><span class="token attr-value"><span class="token punctuation">=</span>ON</span> <span class="token property">--databases</span> db1 &gt; dumpM1<span class="token punctuation">.</span>sql</code></pre>
     </div>
     <p>
      Then run
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      for
      <code class="literal">
       source2
      </code>
      as follows:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa39049457"><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-terminal">mysqldump <span class="token property">-u&lt;<em class="replaceable">user</em>&gt;</span> <span class="token property">-p&lt;<em class="replaceable">password</em>&gt;</span> <span class="token property">--single-transaction</span> <span class="token property">--triggers</span> <span class="token property">--routines</span> <span class="token constant">--set-gtid-purged</span><span class="token attr-value"><span class="token punctuation">=</span>ON</span> <span class="token property">--databases</span> db2 &gt; dumpM2<span class="token punctuation">.</span>sql</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Record the
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
       <code class="literal">
        gtid_purged
       </code>
      </a>
      value
          that
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      added to each of the dump
          files. You can extract the value like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa32814308"><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-none">cat dumpM1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''
cat dumpM2.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''</code></pre>
     </div>
     <p>
      The result in each case should be a GTID set, for example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa24941290"><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-none">source1:   2174B383-5441-11E8-B90A-C80AA9429562:1-1029
source2:   224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Remove the line from each dump file that contains the
      <code class="literal">
       SET @@GLOBAL.gtid_purged
      </code>
      statement. For
          example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa37740470"><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-simple">sed '<span class="token operator">/</span>GTID_PURGED<span class="token operator">/</span>d' dumpM1<span class="token punctuation">.</span>sql <span class="token operator">&gt;</span> dumpM1_nopurge<span class="token punctuation">.</span>sql
sed '<span class="token operator">/</span>GTID_PURGED<span class="token operator">/</span>d' dumpM2<span class="token punctuation">.</span>sql <span class="token operator">&gt;</span> dumpM2_nopurge<span class="token punctuation">.</span>sql</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Use the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client to import each edited
          dump file into the replica. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa26763012"><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-simple">mysql <span class="token operator">-</span>u<span class="token operator">&lt;</span><em class="replaceable">user</em><span class="token operator">&gt;</span> <span class="token operator">-</span>p<span class="token operator">&lt;</span><em class="replaceable">password</em><span class="token operator">&gt;</span> <span class="token operator">&lt;</span> dumpM1_nopurge<span class="token punctuation">.</span>sql
mysql <span class="token operator">-</span>u<span class="token operator">&lt;</span><em class="replaceable">user</em><span class="token operator">&gt;</span> <span class="token operator">-</span>p<span class="token operator">&lt;</span><em class="replaceable">password</em><span class="token operator">&gt;</span> <span class="token operator">&lt;</span> dumpM2_nopurge<span class="token punctuation">.</span>sql</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      On the replica, issue
      <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>
      to clear the GTID execution history
          (assuming, as explained above, that all the dump files have
          been imported and that there are no wanted transactions with
          GTIDs on the replica). Then issue a
      <code class="literal">
       SET
          @@GLOBAL.gtid_purged
      </code>
      statement to set the
      <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
       <code class="literal">
        gtid_purged
       </code>
      </a>
      value to the
          union of all the GTID sets from all the dump files, as you
          recorded in Step 2. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95261942"><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">RESET</span> <span class="token datatype">BINARY</span> <span class="token keyword">LOGS</span> <span class="token operator">AND</span> GTIDS<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@@GLOBAL.gtid_purged</span> <span class="token operator">=</span> <span class="token string">"2174B383-5441-11E8-B90A-C80AA9429562:1-1029, 224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695"</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If there are, or might be, overlapping transactions between
          the GTID sets in the dump files, you can use the stored
          functions described in
      <a class="xref" href="replication-gtids-functions.html" title="19.1.3.8 Stored Function Examples to Manipulate GTIDs">
       Section 19.1.3.8, “Stored Function Examples to Manipulate GTIDs”
      </a>
      to check this
          beforehand and to calculate the union of all the GTID sets.
     </p>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
