The mysqldump program writes the contents of database tables into text files. These files may be used for a variety of purposes, such as database backups, moving databases to another server, or setting up a test database based on the contents of an existing database.
By default, output for each dumped table consists of a CREATE TABLE statement that re-creates the table, followed by a set of INSERT statements that reload the contents of the table. If the --tab option is given, table contents are written to a data file in raw format, and the table creation SQL statement is written to a separate file.
mysqldump [options] db_name [tbl_name] …
If no tables are named following the database name db_name, all tables in the database are dumped. Otherwise, only the named tables are dumped.
The most common way to use mysqldump is as follows:
% mysqldump --opt db_name > backup_file
--compress --host --port --user --debug --password --set-variable --verbose --help --pipe --socket --version
The following options control how mysqldump operates. The next section, "Data Format Options," describes options that may be used in conjunction with the --tab option to indicate the format of data files.
--add-drop-table
Adds a DROP TABLE IF EXISTS statement before each CREATE TABLE statement.
--add-locks
Adds LOCK TABLE and UNLOCK TABLE around the set of INSERT statements that load the data for each table.
--allow-keywords
-c, --complete-insert
Uses INSERT statements that name each column to be inserted.
--delayed-insert
-e, --extended-insert
-F, --flush-logs
-f, --force
--full
Adds additional information to the CREATE TABLE statements that mysqldump generates, such as the table type, beginning AUTO_INCREMENT value, and so forth. This is the information that you can specify in the table_options part of the CREATE TABLE syntax. (see Appendix D.)
-l, --lock-tables
Obtains lock for all tables being dumped before dumping them.
-t, --no-create-info
-d, --no-data
--opt
Optimizes table dumping speed and writes a dump file that is optimal for reloading speed. This option turns on --add-drop-table, --add-locks, --all, --extended-insert, --quick, and --lock-tables.
-q, --quick
By default, mysqldump reads the entire contents of a table into memory and then writes it out. This option causes each row to be written to the output as soon as it has been read from the server.
If you use this option, you should not suspend mysqldump; that causes the server to wait, which can interfere with other clients.
-T dump_dir, --tab=dump_dir
This option causes mysqldump to write two files per table, using dump_dir as the location for the files. The directory must already exist. For each table tbl_name, a file dump_dir/table_name.txt is written containing the data from the table, and a file dump_dir/table_name.sql is written containing the CREATE TABLE statement for the table. You must have the FILE privilege to use this option.
To avoid confusion, it is best to run mysqldump on the server host when you use this option. Otherwise, some of the files are written on the server and some are written on the client. dump_dir is used on the server host for the *.sql files and on the client host for the *.txt files. The *.sql files will be owned by the account used to run the server, and the *.txt files will be owned by you.
By default, the data file contains newline-terminated lines consisting of tab-separated column values. This format may be changed using the options described under "Data Format Options."
-w where_clause, --where=where_clause
Only dumps records selected by the WHERE clause given by where_clause. You should enclose the clause in quotes to prevent the shell from interpreting it as multiple command-line arguments. This option was introduced in MySQL 3.22.7.
If you specify the --tab or -T option to generate a separate data file for each table, several additional options apply. You may need to enclose the option value in appropriate quoting characters. These options are analogous to the data format options for the LOAD DATA statement. See the entry for LOAD DATA in Appendix D.
--fields-enclosed-by=char
Specifies that column values should be enclosed within the given character, usually a quote character. The default is not to enclose column values within anything.
--fields-escaped-by=char
Specifies the escape character for escaping special characters. The default is no escape character.
--fields-optionally-enclosed-by=char
Specifies that column values should be enclosed within the given character, usually a quote character. The character is used for non-numeric columns. The default is not to enclose column values within anything.
--fields-terminated-by=char
Specifies the column value separation character to use for data files. By default, values are separated by tabs.
--lines-terminated-by=str
Specifies the string (it may be multiple characters) to write at the end of output lines. The default is to write newlines.
The following mysqldump variables can be set with the --set-variable option: