<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="copying-databases">
      </a>
      3.15 Copying MySQL Databases to Another Machine
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045325794192">
  </a>
  <a class="indexterm" name="idm46045325792736">
  </a>
  <a class="indexterm" name="idm46045325791664">
  </a>
  <p>
   In cases where you need to transfer databases between different
      architectures, you can 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
      a file containing SQL statements. You can then transfer the file
      to the other machine and feed it as input to 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.
  </p>
  <p>
   Use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump --help
     </strong>
    </span>
   </a>
   to see what options are
      available.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    If GTIDs are in use on the server where you create the dump
        (
    <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
     <code class="literal">
      gtid_mode=ON
     </code>
    </a>
    ), by default,
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    includes the contents of the
    <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
     <code class="literal">
      gtid_executed
     </code>
    </a>
    set in the dump
        to transfer these to the new machine. The results of this can
        vary depending on the MySQL Server versions involved. Check the
        description for the
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    <code class="option">
     --set-gtid-purged
    </code>
    option to find what happens
        with the versions you are using, and how to change the behavior
        if the outcome of the default behavior is not suitable for your
        situation.
   </p>
  </div>
  <p>
   The easiest (although not the fastest) way to move a database
      between two machines is to run the following commands on the
      machine on which the database is located:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa62613781"><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">mysqladmin <span class="token property">-h</span> <span class="token atrule">'<em class="replaceable">other_hostname</em>'</span> create <em class="replaceable">db_name</em>
mysqldump <em class="replaceable">db_name</em> | mysql <span class="token property">-h</span> <span class="token atrule">'<em class="replaceable">other_hostname</em>'</span> <em class="replaceable">db_name</em></code></pre>
  </div>
  <p>
   If you want to copy a database from a remote machine over a slow
      network, you can use these commands:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa84968606"><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">mysqladmin create <em class="replaceable">db_name</em>
mysqldump <span class="token property">-h</span> <span class="token atrule">'<em class="replaceable">other_hostname</em>'</span> <span class="token property">--compress</span> <em class="replaceable">db_name</em> | mysql <em class="replaceable">db_name</em></code></pre>
  </div>
  <p>
   You can also store the dump in a file, transfer the file to the
      target machine, and then load the file into the database there.
      For example, you can dump a database to a compressed file on the
      source machine like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa8773654"><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">--quick</span> <em class="replaceable">db_name</em> | gzip &gt; <em class="replaceable">db_name</em><span class="token punctuation">.</span>gz</code></pre>
  </div>
  <p>
   Transfer the file containing the database contents to the target
      machine and run these commands there:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa19568384"><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">mysqladmin create <em class="replaceable">db_name</em>
gunzip &lt; <em class="replaceable">db_name</em><span class="token punctuation">.</span>gz | mysql <em class="replaceable">db_name</em></code></pre>
  </div>
  <a class="indexterm" name="idm46045325765552">
  </a>
  <a class="indexterm" name="idm46045325764480">
  </a>
  <p>
   You can also use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   and
   <a class="link" href="mysqlimport.html" title="6.5.5 mysqlimport — A Data Import Program">
    <span class="command">
     <strong>
      mysqlimport
     </strong>
    </span>
   </a>
   to transfer the database. For large
      tables, this is much faster than simply using
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   . In the following commands,
   <em class="replaceable">
    <code>
     DUMPDIR
    </code>
   </em>
   represents the full path name
      of the directory you use to store the output from
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   First, create the directory for the output files and dump the
      database:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa82224584"><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">mkdir <em class="replaceable">DUMPDIR</em>
mysqldump <span class="token constant">--tab</span><span class="token attr-value"><span class="token punctuation">=</span><em class="replaceable">DUMPDIR</em></span>
   <em class="replaceable">db_name</em></code></pre>
  </div>
  <p>
   Then transfer the files in the
   <em class="replaceable">
    <code>
     DUMPDIR
    </code>
   </em>
   directory to some corresponding directory on the target machine
      and load the files into MySQL there:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa51643401"><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">mysqladmin create <em class="replaceable">db_name</em>           <span class="token comment" spellcheck="true"># create database</span>
cat <em class="replaceable">DUMPDIR</em>/*<span class="token punctuation">.</span>sql | mysql <em class="replaceable">db_name</em>   <span class="token comment" spellcheck="true"># create tables in database</span>
mysqlimport <em class="replaceable">db_name</em>
   <em class="replaceable">DUMPDIR</em>/*<span class="token punctuation">.</span>txt   <span class="token comment" spellcheck="true"># load data into tables</span></code></pre>
  </div>
  <p>
   Do not forget to copy the
   <code class="literal">
    mysql
   </code>
   database
      because that is where the grant tables are stored. You might have
      to run commands as the MySQL
   <code class="literal">
    root
   </code>
   user on the
      new machine until you have the
   <code class="literal">
    mysql
   </code>
   database
      in place.
  </p>
  <p>
   After you import the
   <code class="literal">
    mysql
   </code>
   database on the new
      machine, execute
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin flush-privileges
     </strong>
    </span>
   </a>
   so
      that the server reloads the grant table information.
  </p>
 </div>
 <br/>
</div>
