<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-importing-data">
      </a>
      25.6.9 Importing Data Into MySQL Cluster
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045091863040">
  </a>
  <a class="indexterm" name="idm46045091861552">
  </a>
  <p>
   It is common when setting up a new instance of NDB Cluster to need
      to import data from an existing NDB Cluster, instance of MySQL, or
      other source. This data is most often available in one or more of
      the following formats:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An SQL dump file such as produced by
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      . This can be imported 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, as shown later in this
          section.
     </p>
    </li>
    <li class="listitem">
     <p>
      A CSV file produced by
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      or other
          export program. Such files can be imported into
      <code class="literal">
       NDB
      </code>
      using
      <code class="literal">
       LOAD DATA
          INFILE
      </code>
      in 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, or
          with the
      <a class="link" href="mysql-cluster-programs-ndb-import.html" title="25.5.13 ndb_import — Import CSV Data Into NDB">
       <span class="command">
        <strong>
         ndb_import
        </strong>
       </span>
      </a>
      utility provided with
          the NDB Cluster distribution. For more information about the
          latter, see
      <a class="xref" href="mysql-cluster-programs-ndb-import.html" title="25.5.13 ndb_import — Import CSV Data Into NDB">
       Section 25.5.13, “ndb_import — Import CSV Data Into NDB”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      A native
      <code class="literal">
       NDB
      </code>
      backup produced using
      <a class="link" href="mysql-cluster-backup-using-management-client.html" title="25.6.8.2 Using The NDB Cluster Management Client to Create a Backup">
       <code class="literal">
        START BACKUP
       </code>
      </a>
      in the
      <code class="literal">
       NDB
      </code>
      management client. To import a native
          backup, you must use the
      <a class="link" href="mysql-cluster-programs-ndb-restore.html" title="25.5.23 ndb_restore — Restore an NDB Cluster Backup">
       <span class="command">
        <strong>
         ndb_restore
        </strong>
       </span>
      </a>
      program that comes as part of NDB Cluster. See
      <a class="xref" href="mysql-cluster-programs-ndb-restore.html" title="25.5.23 ndb_restore — Restore an NDB Cluster Backup">
       Section 25.5.23, “ndb_restore — Restore an NDB Cluster Backup”
      </a>
      , for more
          about using this program.
     </p>
    </li>
   </ul>
  </div>
  <p>
   When importing data from an SQL file, it is often not necessary to
      enforce transactions or foreign keys, and temporarily disabling
      these features can speed up the import process greatly. This can
      be done 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, either from a
      client session, or by invoking it on the command line. Within a
   <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 session, you can perform the
      import using the following SQL statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55364858"><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> ndb_use_transactions<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> foreign_key_checks<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>

<span class="token keyword">source</span> <span class="token keyword"></span><em class="replaceable"><span class="token keyword">path</span><span class="token operator">/</span><span class="token keyword">to</span><span class="token operator">/</span><span class="token keyword">dumpfile</span></em><span class="token keyword"></span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> ndb_use_transactions<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> foreign_key_checks<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When performing the import in this fashion, you
   <span class="emphasis">
    <em>
     must
    </em>
   </span>
   enable
   <code class="literal">
    ndb_use_transaction
   </code>
   and
   <code class="literal">
    foreign_key_checks
   </code>
   again following execution of
      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's
   <code class="literal">
    source
   </code>
   command. Otherwise, it is possible for
      later statements in same session may also be executed without
      enforcing transactions or foreign key constraints, and which could
      lead to data inconcsistency.
  </p>
  <p>
   From the system shell, you can import the SQL file while disabling
      enforcement of transaction and foreign keys by 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 with the
   <a class="link" href="mysql-command-options.html#option_mysql_init-command">
    <code class="option">
     --init-command
    </code>
   </a>
   option, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa99554229"><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 operator">&gt;</span> mysql <span class="token comment" spellcheck="true">--init-command='SET ndb_use_transactions=0; SET foreign_key_checks=0' &lt; <em class="replaceable">path/to/dumpfile</em></span></code></pre>
  </div>
  <p>
   It is also possible to load the data into an
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   table, and convert it to use
      the
   <code class="literal">
    NDB
   </code>
   storage engine afterwards using ALTER
      TABLE ... ENGINE NDB). You should take into account, especially
      for many tables, that this may require a number of such
      operations; in addition, if foreign keys are used, you must mind
      the order of the
   <code class="literal">
    ALTER TABLE
   </code>
   statements
      carefully, due to the fact that foreign keys do not work between
      tables using different MySQL storage engines.
  </p>
  <p>
   You should be aware that the methods described previously in this
      section are not optimized for very large data sets or large
      transactions. Should an application really need big transactions
      or many concurrent transactions as part of normal operation, you
      may wish to increase the value of the
   <a class="link" href="mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnoofconcurrentoperations">
    <code class="literal">
     MaxNoOfConcurrentOperations
    </code>
   </a>
   data node configuration parameter, which reserves more memory to
      allow a data node to take over a transaction if its transaction
      coordinator stops unexpectedly.
  </p>
  <p>
   You may also wish to do this when performing bulk
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   or
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   operations on NDB Cluster
      tables. If possible, try to have applications perform these
      operations in chunks, for example, by adding
   <code class="literal">
    LIMIT
   </code>
   to such statements.
  </p>
  <p>
   If a data import operation does not complete successfully, for
      whatever reason, you should be prepared to perform any necessary
      cleanup including possibly one or more
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP
      TABLE
    </code>
   </a>
   statements,
   <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
    <code class="literal">
     DROP
      DATABASE
    </code>
   </a>
   statements, or both. Failing to do so may leave
      the database in an inconsistent state.
  </p>
 </div>
 <br/>
</div>
