mysqldump

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.

Usage

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
						

Standard Options Supported by mysqldump

--compress      --host          --port          --user
--debug         --password      --set-variable  --verbose
--help          --pipe          --socket        --version

Options Specific to mysqldump

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
  

  Allows for the creation of column names that are keywords.

• -c, --complete-insert
  

  Uses INSERT statements that name each column to be inserted.

• --delayed-insert
  

  Writes INSERT DELAYED statements.

• -e, --extended-insert
  

  Writes multiple-row INSERT statements.

• -F, --flush-logs
  

  Flushes the server log files before starting the dump.

• -f, --force
  

  Continues even if errors occur.

• --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

  Does not write CREATE TABLE statements.

• -d, --no-data
  

  Does not write table 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.

Data Format Options

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.

Variables for mysqldump

The following mysqldump variables can be set with the --set-variable option:

• max_allowed_packet

  The maximum size of the buffer used for communication between the server and the client.

• net_buffer_length

  The initial size of the buffer used for communication between the server and the client. This buffer may be expanded up to max_allowed_packet bytes long.