that contains the CREATE TABLE statements and a table_name.txt file that
contains the data. The data will be saved in tab-separated format (if --tab is
specified); otherwise, the data is saved as specified by the --fields-
terminated-by option. (Note that this will only work when mysqldump is run on
the same machine as the mysqld daemon.)
--fields-terminated-by='delimeter' Specifies the delimiter to use as a
separator after each field. If unspecified, the default being '\t' (tab); used with -T.
--fields-enclosed-by='delimeter' Specifies the delimiter to use to
enclose each field; used with -T.
--fields-optionally-enclosed-by='delimeter' Specifies the delimiter
to enclose each CHAR or VARCHAR type field; used with -T.
--fields-escaped-by='escape_char' Specifies the escape_char to place
before any special character, the default being '\' (amounting to one backslash);
used with -T.
--lines-terminated-by='delimeter' Specifies the line delimiter, the
default being '\n' (newline). See also LOAD DATA INFILE in Day 8, "Populating
the Database," whose specification of delimiters corresponds to these options.
-F, --flush-logs Makes MySQL flush the logs file (see "Logging Transactions"
later today, for more on logs) before starting the dump. This option can help
synchronize full and incremental backups.
-u user_name, --user=user_name Allows you to specify a MySQL username
when connecting to the server. Your UNIX login name will be used by default.
-p[your_password, -password[=your_password] Sends your password
when connecting to the server. Remember not to type a space between -p and
your_password. If you only enter -p or -password, MySQL prompts you for your
password from the terminal (thus avoiding it being visible onscreen).
-w='condition', --where='condition' Dumps only records selected by the
where condition.
-?, --help Displays the full mysqldump list of options and exits.
Note mysqldump offers a few more options, including ones for debugging and
compressing. We have listed just the most useful ones here. Run mysqldump -
-help for the full list of possibilities in your particular version.
Note If you encounter permissions problems when trying to run mysqldump with either
Errcode: 13 or Errcode: 2, this is probably due to an incorrectly set UMASK when
mysqld starts up.
The default UMASK value is usually 0660 (octal). However, you can correct this by
restarting mysqld with the correct UMASK:
shellprompt> mysqladmin shutdown
shellprompt> UMASK=384 # this is 0600 octal
shellprompt> export UMASK
shellprompt> safe_mysqld &
You should then be able to run mysqldump normally.
The following is an example of running mysqldump. Imagine that you want to make a backup of an
entire database named book. You would type:
mysqldump --opt book > /path/to/book.sql
This would produce a file containing something like the following (recalling your Customers table from
Day 11):
MySQL dump 6.4
Host: localhost Database: book
#--------------------------------------------------------