<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysqldump-delimited-text">
      </a>
      9.4.3 Dumping Data in Delimited-Text 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 delimited-text dump files. For information about
        reloading such dump files, see
   <a class="xref" href="reloading-delimited-text-dumps.html" title="9.4.4 Reloading Delimited-Text Format Backups">
    Section 9.4.4, “Reloading Delimited-Text Format Backups”
   </a>
   .
  </p>
  <p>
   If you 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_tab">
    <code class="option">
     --tab=
     <em class="replaceable">
      <code>
       dir_name
      </code>
     </em>
    </code>
   </a>
   option, it uses
   <em class="replaceable">
    <code>
     dir_name
    </code>
   </em>
   as the
        output directory and dumps tables individually in that directory
        using two files for each table. The table name is the base name
        for these files. For a table named
   <code class="literal">
    t1
   </code>
   , the
        files are named
   <code class="filename">
    t1.sql
   </code>
   and
   <code class="filename">
    t1.txt
   </code>
   . The
   <code class="filename">
    .sql
   </code>
   file
        contains a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement
        for the table. The
   <code class="filename">
    .txt
   </code>
   file contains the
        table data, one line per table row.
  </p>
  <p>
   The following command dumps the contents of the
   <code class="literal">
    db1
   </code>
   database to files in the
   <code class="filename">
    /tmp
   </code>
   database:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa6071930"><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> mysqldump <span class="token comment" spellcheck="true">--tab=/tmp db1</span></code></pre>
  </div>
  <p>
   The
   <code class="filename">
    .txt
   </code>
   files containing table data are
        written by the server, so they are owned by the system account
        used for running the server. The server uses
   <a class="link" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
    <code class="literal">
     SELECT ... INTO
        OUTFILE
    </code>
   </a>
   to write the files, so you must have the
   <a class="link" href="privileges-provided.html#priv_file">
    <code class="literal">
     FILE
    </code>
   </a>
   privilege to perform this
        operation, and an error occurs if a given
   <code class="filename">
    .txt
   </code>
   file already exists.
  </p>
  <p>
   The server sends the
   <code class="literal">
    CREATE
   </code>
   definitions for
        dumped tables to
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   , which writes them
        to
   <code class="filename">
    .sql
   </code>
   files. These files therefore are
        owned by the user who executes
   <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>
   It is best that
   <a class="link" href="mysqldump.html#option_mysqldump_tab">
    <code class="option">
     --tab
    </code>
   </a>
   be used
        only for dumping a local server. If you use it with a remote
        server, the
   <a class="link" href="mysqldump.html#option_mysqldump_tab">
    <code class="option">
     --tab
    </code>
   </a>
   directory
        must exist on both the local and remote hosts, and the
   <code class="filename">
    .txt
   </code>
   files are written by the server in the
        remote directory (on the server host), whereas the
   <code class="filename">
    .sql
   </code>
   files are written by
   <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 local directory (on the
        client host).
  </p>
  <p>
   For
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump --tab
     </strong>
    </span>
   </a>
   , the server by default
        writes table data to
   <code class="filename">
    .txt
   </code>
   files one line
        per row with tabs between column values, no quotation marks
        around column values, and newline as the line terminator. (These
        are the same defaults as for
   <a class="link" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
    <code class="literal">
     SELECT ... INTO
        OUTFILE
    </code>
   </a>
   .)
  </p>
  <p>
   To enable data files to be written using a different format,
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   supports these options:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html#option_mysqldump_fields">
       <code class="option">
        --fields-terminated-by=
        <em class="replaceable">
         <code>
          str
         </code>
        </em>
       </code>
      </a>
     </p>
     <p>
      The string for separating column values (default: tab).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html#option_mysqldump_fields">
       <code class="option">
        --fields-enclosed-by=
        <em class="replaceable">
         <code>
          char
         </code>
        </em>
       </code>
      </a>
     </p>
     <p>
      The character within which to enclose column values
            (default: no character).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html#option_mysqldump_fields">
       <code class="option">
        --fields-optionally-enclosed-by=
        <em class="replaceable">
         <code>
          char
         </code>
        </em>
       </code>
      </a>
     </p>
     <p>
      The character within which to enclose non-numeric column
            values (default: no character).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html#option_mysqldump_fields">
       <code class="option">
        --fields-escaped-by=
        <em class="replaceable">
         <code>
          char
         </code>
        </em>
       </code>
      </a>
     </p>
     <p>
      The character for escaping special characters (default: no
            escaping).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html#option_mysqldump_lines-terminated-by">
       <code class="option">
        --lines-terminated-by=
        <em class="replaceable">
         <code>
          str
         </code>
        </em>
       </code>
      </a>
     </p>
     <p>
      The line-termination string (default: newline).
     </p>
    </li>
   </ul>
  </div>
  <p>
   Depending on the value you specify for any of these options, it
        might be necessary on the command line to quote or escape the
        value appropriately for your command interpreter. Alternatively,
        specify the value using hex notation. Suppose that you want
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to quote column values within
        double quotation marks. To do so, specify double quote as the
        value for the
   <a class="link" href="mysqldump.html#option_mysqldump_fields">
    <code class="option">
     --fields-enclosed-by
    </code>
   </a>
   option. But this character is often special to command
        interpreters and must be treated specially. For example, on
        Unix, you can quote the double quote like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa33972565"><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"><span class="token operator">--</span>fields<span class="token operator">-</span>enclosed<span class="token operator">-</span>by<span class="token operator">=</span>'"'</code></pre>
  </div>
  <p>
   On any platform, you can specify the value in hex:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa15297491"><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"><span class="token operator">--</span>fields<span class="token operator">-</span>enclosed<span class="token operator">-</span>by<span class="token operator">=</span>0x22</code></pre>
  </div>
  <p>
   It is common to use several of the data-formatting options
        together. For example, to dump tables in comma-separated values
        format with lines terminated by carriage-return/newline pairs
        (
   <code class="literal">
    \r\n
   </code>
   ), use this command (enter it on a
        single line):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa4059118"><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 constant">--tab</span><span class="token attr-value"><span class="token punctuation">=</span>/tmp</span> <span class="token constant">--fields-terminated-by</span><span class="token attr-value"><span class="token punctuation">=</span>,</span>
         <span class="token constant">--fields-enclosed-by</span>=<span class="token atrule">'"'</span> <span class="token constant">--lines-terminated-by</span><span class="token attr-value"><span class="token punctuation">=</span>0x0d0a</span> db1</code></pre>
  </div>
  <p>
   Should you use any of the data-formatting options to dump table
        data, you need to specify the same format when you reload data
        files later, to ensure proper interpretation of the file
        contents.
  </p>
 </div>
 <br/>
</div>
