7.4.3 Dumping Data in Delimited-Text Format with mysqldump
This section describes how to use mysqldump to create delimited-text dump files. For information about reloading such dump files, see Section 7.4.4, “Reloading Delimited-Text Format Backups”.
If you invoke mysqldump with the
--tab= option, it uses
dir_name 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
t1, the files are named
.sql file contains a
CREATE TABLE statement for the table. The
.txt file contains the table data, one line per table row.
The following command dumps the contents of the
db1 database to files in the
shell> mysqldump --tab=/tmp db1
.txt 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
SELECT ... INTO OUTFILE to write the files, so you must have the
FILE privilege to perform this operation, and an error occurs if a given
.txt file already exists.
It is best that
--tab be used only for dumping a local server. If you use it with a remote server, the
--tab directory must exist on both the local and remote hosts, and the
.txt files will be written by the server in the remote directory (on the server host), whereas the
.sql files will be written by mysqldump in the local directory (on the client host).
For mysqldump --tab, the server by default writes table data to
.txt 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
SELECT ... INTO OUTFILE.)
To enable data files to be written using a different format, mysqldump supports these options:
The string for separating column values (default: tab).
The character within which to enclose column values (default: no character).
The character within which to enclose non-numeric column values (default: no character).
The character for escaping special characters (default: no escaping).
The line-termination string (default: newline).
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 mysqldump to quote column values within double quotation marks. To do so, specify double quote as the value for the
--fields-enclosed-by 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:
On any platform, you can specify the value in hex:
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 (
\r\n), use this command (enter it on a single line):
shell> mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1
Should you use any of the data-formatting options to dump table data, you will need to specify the same format when you reload data files later, to ensure proper interpretation of the file contents.