<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysqldump-sql-format">
      </a>
      9.4.1 Dumping Data in SQL Format with mysqldump
     </h3>
    </div>
   </div>
  </div>
  <p>
   This section describes how 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 SQL-format dump files. For information about reloading
        such dump files, see
   <a class="xref" href="reloading-sql-format-dumps.html" title="9.4.2 Reloading SQL-Format Backups">
    Section 9.4.2, “Reloading SQL-Format Backups”
   </a>
   .
  </p>
  <p>
   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>
   writes information as
        SQL statements to the standard output. You can save the output
        in a file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa41097010"><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">$&gt; </span><span class="token command">mysqldump</span> <span class="token punctuation">[</span><em class="replaceable">arguments</em><span class="token punctuation">]</span> &gt; <em class="replaceable">file_name</em></code></pre>
  </div>
  <p>
   To dump all databases, invoke
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   with
        the
   <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
    <code class="option">
     --all-databases
    </code>
   </a>
   option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa26742420"><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">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--all-databases</span> &gt; dump<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   To dump only specific databases, name them on the command line
        and use the
   <a class="link" href="mysqldump.html#option_mysqldump_databases">
    <code class="option">
     --databases
    </code>
   </a>
   option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa52780911"><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">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--databases</span> db1 db2 db3 &gt; dump<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   The
   <a class="link" href="mysqldump.html#option_mysqldump_databases">
    <code class="option">
     --databases
    </code>
   </a>
   option causes
        all names on the command line to be treated as database names.
        Without this option,
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   treats the
        first name as a database name and those following as table
        names.
  </p>
  <p>
   With
   <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
    <code class="option">
     --all-databases
    </code>
   </a>
   or
   <a class="link" href="mysqldump.html#option_mysqldump_databases">
    <code class="option">
     --databases
    </code>
   </a>
   ,
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   writes
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE
        DATABASE
    </code>
   </a>
   and
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   statements prior to the dump output for each database. This
        ensures that when the dump file is reloaded, it creates each
        database if it does not exist and makes it the default database
        so database contents are loaded into the same database from
        which they came. If you want to cause the dump file to force a
        drop of each database before recreating it, use the
   <a class="link" href="mysqldump.html#option_mysqldump_add-drop-database">
    <code class="option">
     --add-drop-database
    </code>
   </a>
   option as
        well. In this case,
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   writes a
   <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
    <code class="literal">
     DROP DATABASE
    </code>
   </a>
   statement preceding
        each
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE DATABASE
    </code>
   </a>
   statement.
  </p>
  <p>
   To dump a single database, name it on the command line:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa93387988"><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">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--databases</span> test &gt; dump<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   In the single-database case, it is permissible to omit the
   <a class="link" href="mysqldump.html#option_mysqldump_databases">
    <code class="option">
     --databases
    </code>
   </a>
   option:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa48286046"><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">$&gt; </span><span class="token command">mysqldump</span> test &gt; dump<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   The difference between the two preceding commands is that
        without
   <a class="link" href="mysqldump.html#option_mysqldump_databases">
    <code class="option">
     --databases
    </code>
   </a>
   , the dump
        output contains no
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE
        DATABASE
    </code>
   </a>
   or
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   statements. This has several implications:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When you reload the dump file, you must specify a default
            database name so that the server knows which database to
            reload.
     </p>
    </li>
    <li class="listitem">
     <p>
      For reloading, you can specify a database name different
            from the original name, which enables you to reload the data
            into a different database.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the database to be reloaded does not exist, you must
            create it first.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because the output contains no
      <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
       <code class="literal">
        CREATE
            DATABASE
       </code>
      </a>
      statement, the
      <a class="link" href="mysqldump.html#option_mysqldump_add-drop-database">
       <code class="option">
        --add-drop-database
       </code>
      </a>
      option
            has no effect. If you use it, it produces no
      <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
       <code class="literal">
        DROP DATABASE
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To dump only specific tables from a database, name them on the
        command line following the database name:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa64181760"><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">$&gt; </span><span class="token command">mysqldump</span> test t1 t3 t7 &gt; dump<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   By default, if GTIDs are in use on the server where you create
        the dump file (
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
    <code class="literal">
     gtid_mode=ON
    </code>
   </a>
   ),
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   includes a
   <code class="literal">
    SET
        @@GLOBAL.gtid_purged
   </code>
   statement in the output to add
        the GTIDs from the
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
    <code class="literal">
     gtid_executed
    </code>
   </a>
   set on the source
        server to the
   <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
    <code class="literal">
     gtid_purged
    </code>
   </a>
   set
        on the target server. If you are dumping only specific databases
        or tables, 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>
   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
        server, even those that changed suppressed parts of the
        database, or other databases on the server that were not
        included in the partial dump. If you only replay one partial
        dump file on the target server, the extra GTIDs do not cause any
        problems with the future operation of that server. However, if
        you replay a second dump file on the target server that contains
        the same GTIDs (for example, another partial dump from the same
        source server), any
   <code class="literal">
    SET @@GLOBAL.gtid_purged
   </code>
   statement in the second dump file fails. To avoid this issue,
        either set the
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   option
   <code class="option">
    --set-gtid-purged
   </code>
   to
   <code class="literal">
    OFF
   </code>
   or
   <code class="literal">
    COMMENTED
   </code>
   to output the second dump file
        without an active
   <code class="literal">
    SET @@GLOBAL.gtid_purged
   </code>
   statement, or remove the statement manually before replaying the
        dump file.
  </p>
 </div>
 <br/>
</div>
