dumping process and create a faster reload file.-q or -quick (^) This option prevents MySQL from reading the entire dump into
memory and then executing the dump. Instead, it will write to
the file as soon as it reads it.
-T path or -tab =
path
This option will create two files. One file will contain the DDL or
table creation statements and the other will contain the data.
The DDL file is named table_name.sql and the data file is
named table_name.txt. The path argument is the directory
where you want these files created. This directory must already
exist. To use this command, you must have FILE privileges.
-w "WHERE Clause" or
-where = "Where
clause"
As mentioned before, you can use this option to filter the data
that will be in the export file.
mysqlimport, like its counterpart, also has file formatting options. You have seen them before, but
they are listed again in Table 8.3:
Table 8.3 mysqlimport Options
Option Action Performed
--fields-enclosed-
by=char
This specifies what character encloses the data in your file.
For example, a lot of products enclose data with quotation
marks. You could specify that to the import utility with this
option. By default, the mysqlimport utility assumes that
there are no enclosing characters.
--fields-escaped-
by=char
This option tells mysqlimport what character is the escape
character for special characters. Characters that may need
to be escaped are the back slash () and the newline
character (\n). The default is no escape character.
--fields-optionally-
terminated-by=char This option tells that the data may be enclosed by a special
character. Otherwise it works just like the command of the
same name.
--fields-terminated-
by=char
This option specifies what character separates the values
from one another. In a comma-delimited file, it would be the
comma. The mysqlimport utility defaults to the tab
character.
--lines-terminated-
by=string
This option specifies what ends a line of data. By default,
mysqlimport assumes the newline character. A string
could be used instead of a single character, a new line and
a carriage return, for example.
Suppose that you need to create a file for the accounts to use in a spreadsheet. Management wants to
see all the orders for this year. They are bean counters and are not really interested in the DDL. They
also need the file comma-delimited because it's easy to import into Excel that way. To complete this,
task you could use the following statement:
bin/mysqldump –p
–where "Order_Date >='2000-01-01'"
–tab = /home/mark –no-create-info –fields-terminated-by=, Meet_A_Geek Orders
This will produce the wanted results.
SELECT INTO OUTFILE
If the mysqldump utility isn't cool enough, MySQL also has a counterpart to the LOAD DATA INFILE
command. It is the SELECT INTO OUTFILE command. These commands have a lot in common. For
starters, they pretty much share all the same options. The only major difference is that one command imports
the data and one command exports the data.
To demonstrate how to use this command, you are going to perform the same operation you did
previously with the mysqldump utility. To do that, perform the following steps:
- Make sure the mysqld daemon is running and you are in the mysql directory.
- cd /usr/local/mysql