<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-replication-preparation">
      </a>
      25.7.5 Preparing the NDB Cluster for Replication
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045086182336">
  </a>
  <a class="indexterm" name="idm46045086180848">
  </a>
  <p>
   Preparing the NDB Cluster for replication consists of the
      following steps:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Check all MySQL servers for version compatibility (see
      <a class="xref" href="mysql-cluster-replication-general.html" title="25.7.2 General Requirements for NDB Cluster Replication">
       Section 25.7.2, “General Requirements for NDB Cluster Replication”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Create a replication account on the source Cluster with the
          appropriate privileges, using the following two SQL
          statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52016849"><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<em class="replaceable">S</em>&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">replica_user</em>'</span>@<span class="token string">'<em class="replaceable">replica_host</em>'</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'<em class="replaceable">replica_password</em>'</span><span class="token punctuation">;</span>

<span class="token prompt">mysql<em class="replaceable">S</em>&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SLAVE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">TO</span> <span class="token string">'<em class="replaceable">replica_user</em>'</span>@<span class="token string">'<em class="replaceable">replica_host</em>'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In the previous statement,
      <em class="replaceable">
       <code>
        replica_user
       </code>
      </em>
      is the replication
          account user name,
      <em class="replaceable">
       <code>
        replica_host
       </code>
      </em>
      is
          the host name or IP address of the replica, and
      <em class="replaceable">
       <code>
        replica_password
       </code>
      </em>
      is the password to
          assign to this account.
     </p>
     <p>
      For example, to create a replica user account with the name
      <code class="literal">
       myreplica
      </code>
      , logging in from the host named
      <code class="literal">
       replica-host
      </code>
      , and using the password
      <code class="literal">
       53cr37
      </code>
      , use the following
      <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       <code class="literal">
        CREATE USER
       </code>
      </a>
      and
      <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
       <code class="literal">
        GRANT
       </code>
      </a>
      statements:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64065804"><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<em class="replaceable">S</em>&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'myreplica'</span>@<span class="token string">'replica-host'</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'53cr37'</span><span class="token punctuation">;</span>

<span class="token prompt">mysql<em class="replaceable">S</em>&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">SLAVE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">TO</span> <span class="token string">'myreplica'</span>@<span class="token string">'replica-host'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For security reasons, it is preferable to use a unique user
          account—not employed for any other purpose—for the
          replication account.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045086155856">
      </a>
      Set up the replica to use the source. Using 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, this can be accomplished with
          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="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13711370"><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">mysql<em class="replaceable">R</em><span class="token operator">&gt;</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 prompt">     -&gt;</span> <span class="token keyword">SOURCE_HOST</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">source_host</em>'</span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_PORT</span><span class="token operator">=</span><span class="token keyword"><em class="replaceable">source_port</em></span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_USER</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">replica_user</em>'</span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_PASSWORD</span><span class="token operator">=</span><span class="token string">'<em class="replaceable">replica_password</em>'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      In the previous statement,
      <em class="replaceable">
       <code>
        source_host
       </code>
      </em>
      is the host name or IP
          address of the replication source,
      <em class="replaceable">
       <code>
        source_port
       </code>
      </em>
      is the port for the
          replica to use when connecting to the source,
      <em class="replaceable">
       <code>
        replica_user
       </code>
      </em>
      is the user name set
          up for the replica on the source, and
      <em class="replaceable">
       <code>
        replica_password
       </code>
      </em>
      is the password
          set for that user account in the previous step.
     </p>
     <p>
      For example, to tell the replica to use the MySQL server whose
          host name is
      <code class="literal">
       rep-source
      </code>
      with the
          replication account created in the previous step, use the
          following statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63901856"><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">mysql<em class="replaceable">R</em><span class="token operator">&gt;</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 prompt">     -&gt;</span> <span class="token keyword">SOURCE_HOST</span><span class="token operator">=</span><span class="token string">'rep-source'</span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_PORT</span><span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_USER</span><span class="token operator">=</span><span class="token string">'myreplica'</span><span class="token punctuation">,</span>
<span class="token prompt">     -&gt;</span> <span class="token keyword">SOURCE_PASSWORD</span><span class="token operator">=</span><span class="token string">'53cr37'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For a complete list of options that can be used with this
          statement, see
      <a class="xref" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
       Section 15.4.2.2, “CHANGE REPLICATION SOURCE TO Statement”
      </a>
      .
     </p>
     <p>
      To provide replication backup capability, you also need to add
          an
      <a class="link" href="mysql-cluster-options-variables.html#option_mysqld_ndb-connectstring">
       <code class="option">
        --ndb-connectstring
       </code>
      </a>
      option
          to the replica's
      <code class="filename">
       my.cnf
      </code>
      file prior
          to starting the replication process. See
      <a class="xref" href="mysql-cluster-replication-backups.html" title="25.7.9 NDB Cluster Backups With NDB Cluster Replication">
       Section 25.7.9, “NDB Cluster Backups With NDB Cluster Replication”
      </a>
      , for
          details.
     </p>
     <p>
      For additional options that can be set in
      <code class="filename">
       my.cnf
      </code>
      for replicas, see
      <a class="xref" href="replication-options.html" title="19.1.6 Replication and Binary Logging Options and Variables">
       Section 19.1.6, “Replication and Binary Logging Options and Variables”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If the source cluster is already in use, you can create a
          backup of the source and load this onto the replica to cut
          down on the amount of time required for the replica to
          synchronize itself with the source. If the replica is also
          running NDB Cluster, this can be accomplished using the backup
          and restore procedure described in
      <a class="xref" href="mysql-cluster-replication-backups.html" title="25.7.9 NDB Cluster Backups With NDB Cluster Replication">
       Section 25.7.9, “NDB Cluster Backups With NDB Cluster Replication”
      </a>
      .
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa57996251"><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">ndb<span class="token operator">-</span>connectstring<span class="token operator">=</span><em class="replaceable">management_host</em><span class="token punctuation">[</span><span class="token operator">:</span><em class="replaceable">port</em><span class="token punctuation">]</span></code></pre>
     </div>
     <p>
      In the event that you are
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      using NDB
          Cluster on the replica, you can create a backup with this
          command on the source:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa52961227"><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"><span class="token prompt">shell<em class="replaceable">S</em>&gt;</span><span class="token command"> mysqldump</span> <span class="token constant">--source-data</span><span class="token attr-value"><span class="token punctuation">=</span>1</span></code></pre>
     </div>
     <p>
      Then import the resulting data dump onto the replica by
          copying the dump file over to it. After this, you can 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 data from the
          dumpfile into the replica database as shown here, where
      <em class="replaceable">
       <code>
        dump_file
       </code>
      </em>
      is the name of the file
          that was generated using
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      on the
          source, and
      <em class="replaceable">
       <code>
        db_name
       </code>
      </em>
      is the name of
          the database to be replicated:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa33360950"><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">shell<em class="replaceable">R</em>&gt; mysql <span class="token property">-u</span> root <span class="token property">-p</span> <em class="replaceable">db_name</em> &lt; <em class="replaceable">dump_file</em></code></pre>
     </div>
     <p>
      For a complete list of options to use with
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      , see
      <a class="xref" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       Section 6.5.4, “mysqldump — A Database Backup Program”
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       If you copy the data to the replica in this fashion, make
            sure that you stop the replica from trying to connect to the
            source to begin replicating before all the data has been
            loaded. You can do this by starting the replica with
       <a class="link" href="replication-options-replica.html#option_mysqld_skip-replica-start">
        <code class="option">
         --skip-replica-start
        </code>
       </a>
       . Once
            the data loading has completed, follow the additional steps
            outlined in the next two sections.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Ensure that each MySQL server acting as a replication source
          is assigned a unique server ID, and has binary logging
          enabled, using the row-based format. (See
      <a class="xref" href="replication-formats.html" title="19.2.1 Replication Formats">
       Section 19.2.1, “Replication Formats”
      </a>
      .) In addition, we
          strongly recommend enabling the
      <a class="link" href="mysql-cluster-options-variables.html#sysvar_replica_allow_batching">
       <code class="literal">
        replica_allow_batching
       </code>
      </a>
      system
          variable (the default).
     </p>
     <p>
      Use
      <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_replica_batch_size">
       <code class="option">
        --ndb-replica-batch-size
       </code>
      </a>
      to
          set the batch size used for writes on the replica instead of
      <code class="option">
       --ndb-batch-size
      </code>
      , and
      <a class="link" href="mysql-cluster-options-variables.html#sysvar_ndb_replica_blob_write_batch_bytes">
       <code class="option">
        --ndb-replica-blob-write-batch-bytes
       </code>
      </a>
      rather than
      <code class="option">
       --ndb-blob-write-batch-bytes
      </code>
      to
          determine the batch size used by the replication applier for
          writing blob data. All of these options can be set either in
          the source server's
      <code class="filename">
       my.cnf
      </code>
      file, or
          on the command line when starting the source
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      process. See
      <a class="xref" href="mysql-cluster-replication-starting.html" title="25.7.6 Starting NDB Cluster Replication (Single Replication Channel)">
       Section 25.7.6, “Starting NDB Cluster Replication (Single Replication Channel)”
      </a>
      , for more
          information.
     </p>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
