Exporting Tables as Raw Data
Problem
You want to export an entire table to a file.
Solution
Use the mysqldump program with the
--tab option.
Discussion
The mysqldump program is used to copy or back up
tables and databases. It can write table output either as a raw
datafile, or as a set of INSERT statements that
recreate the records in the table. The former capability is described
here, the latter in Recipe 10.16 and Recipe 10.17.
To dump a table as a datafile, you must specify a
--tab option that indicates the directory where
you want the MySQL server to write the file. (The directory must
already exist; the server won’t create it.) For
example, to dump the states table from the
cookbook database to a file in the
/tmp directory, use a command like this:
% mysqldump --no-create-info --tab=/tmp cookbook statesmysqldump creates a datafile using the table name
plus a .txt suffix, so this command will write a
file named /tmp/states.txt. This form of
mysqldump is in some respects the command-line
equivalent of SELECT ... INTO
OUTFILE. For example, it writes out a table as a
datafile on the server host, and you must have the
FILE
privilege to use it. See Recipe 10.14 for a list of
general properties of SELECT ...
INTO OUTFILE.
If you omit the --no-create-info option,
mysqldump also will create a file
/tmp/states.sql that contains the
CREATE TABLE statement for the
table. (The latter file will be owned by you, unlike the datafile,
which is owned by the server.)
You can name multiple tables ...