'mysqldump'

The mysqldump program writes the contents of database tables into text files. These files can 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 creates the table, followed by a set of INSERT statements that load 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 can be run in any of three modes:

mysqldump [options] db_name [tbl_name] ... 
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

In the first case, the named tables in the given database are dumped. If no tables are named, mysqldump dumps all tables in the database. In the second case, all arguments are taken as database names and mysqldump dumps all tables in each one. In the third case, mysqldump dumps all tables in all databases.

The most common way to use mysqldump is as follows:

% mysqldump --opt db_name > backup_file 

Note that this backup file should be imported back into MySQL with mysql rather than with mysqlimport:

% mysql db_name < backup_file 

Standard Options Supported by mysqldump

--character-sets-dir     --host                   --socket 
--compress               --password               --user
--debug                  --pipe                   --verbose
--default-character-set  --port                   --version
--help                   --set-variable

The --character-sets-dir and --default-character-set options were added in MySQL 3.23.15. As of MySQL 4, mysqldump also supports the standard SSL options.

Options Specific to mysqldump

The following options control how mysqldump operates. The next section, "Data Format Options for mysqldump," describes options that can be used in conjunction with the --tab option to indicate the format of data files.

• --add-drop-table (boolean)

  Add a DROP TABLE IF EXISTS statement before each CREATE TABLE statement. This option was introduced in MySQL 3.22.4.

• --add-locks (boolean)

  Add LOCK TABLE and UNLOCK TABLE statements around the set of INSERT statements that load the data for each table. This option was introduced in MySQL 3.22.3.

• --all, -a (boolean)

  Add additional information to the CREATE TABLE statements that mysqldump generates, such as the table type, the 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.) This option was introduced in MySQL 3.22.23.

• --all-databases, -A (boolean)

  Dump all tables in all databases. This option was introduced in MySQL 3.23.12.

• --allow-keywords (boolean)

  Allow for the creation of column names that are keywords. This option was introduced in MySQL 3.22.3.

• --complete-insert, -c (boolean)

  Use INSERT statements that name each column to be inserted.

• --databases, -B (boolean)

  Interpret all arguments as database names and dump all tables in each database. This option was introduced in MySQL 3.23.12.

• --delayed-insert (boolean)

  Write INSERT DELAYED statements. This option was introduced in MySQL 3.22.15.

• --disable-keys, -K (boolean)

  Add ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS statements to the output to disable key updating while INSERT statements are being processed. This speeds up index creation for MyISAM tables. This option was introduced in MySQL 3.23.48.

• --extended-insert, -e (boolean)

  Write multiple-row INSERT statements. These can be loaded more efficiently than single-row statements. This option was introduced in MySQL 3.22.15.

• --first-slave, -x (boolean)

  Lock all tables across all databases using FLUSH TABLES WITH READ LOCK. (Note that this type of lock is ineffective for obtaining a consistent backup of InnoDB tables; use --single-transaction instead.) This option was introduced in MySQL 3.23.22.

• --flush-logs, -F (boolean)

  Flush the server log files before starting the dump.

• --force, -f (boolean)

  Continue execution even if errors occur.

• --lock-tables, -l (boolean)

  Obtain locks for all tables being dumped before dumping them. Cannot be used with --single-transaction.

• --master-data

  This option helps make a backup that can be used with a slave server. It writes the master server filename and position at the end of the output. Use of --master-data automatically enables --first-slave. This option was introduced in MySQL 3.23.48.

• --no-autocommit (boolean)

  Write the INSERT statements for each table within a transaction. The resulting output can be loaded more efficiently than executing each statement in auto-commit mode. This option was introduced in MySQL 3.23.48.

• --no-create-db, -n (boolean)

  Do not write CREATE DATABASE statements. (Normally, these are added to the output automatically when --databases or --all-databases are used.) This option was introduced in MySQL 3.23.12.

• --no-create-info, -t (boolean)

  Do not write CREATE TABLE statements. This is useful if you want to dump just table data.

• --no-data, -d (boolean)

  Do not write table data. This is useful if you want to dump just the CREATE TABLE statements.

• --opt

  Optimize table dumping speed and write a dump file that is optimal for reloading speed. This option turns on whichever of the following options are present in your version of mysqldump--add-drop-table, --add-locks, --all, --disable-keys, --extended-insert, --lock-tables, and --quick.: This option was introduced in MySQL 3.22.3.

• --quick, -q (boolean)

  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, which is much less memory intensive. However, if you use this option, you should not suspend mysqldump; doing so causes the server to wait, which can interfere with other clients.

• --quote-names, -Q (boolean)

  Quote table and column names by enclosing them within backtick ('`') characters. This is useful if names are reserved words or contain special characters. This option was introduced in MySQL 3.23.6.

• --result-file=file_name, -r file_name

  Write output to the named file. This option is intended for Windows, where it prevents conversion of linefeeds to carriage return/linefeed pairs. This option was introduced in MySQL 3.23.28.

• --single-transaction (boolean)

  This option allows consistent dumps of InnoDB tables. The idea is that all the tables are dumped within a single transaction, which has the effect of read-locking them all at once. This option was introduced in MySQL 4.0.2. It cannot be used with --lock-tables.

• --tab=dump_dir, -T 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/tbl_name.txt is written containing the data from the table, and a file dump_dir/tbl_name.sql is written containing the CREATE TABLE statement for the table. You must have the FILE privilege to use this option.

  By default, data files are written as newline-terminated lines consisting of tab-separated column values. This format can be changed using the options described under the "Data Format Options for mysqldump" section later in this appendix.

  The effect of the --tab option can be confusing unless you understand exactly how it works:

  • 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 *.txt files and on the client host for the *.sql files. If the two hosts are different, the output files are created on different machines. To avoid any uncertainty about where files will be written, it is best to run mysqldump on the server host when you use this option.

  • The *.txt files will be owned by the account used to run the server, and the *.sql files will be owned by you. This is a consequence of the fact that the server itself writes the *.txt files, whereas the CREATE TABLE statements are sent by the server to mysqldump, which writes the *.sql files.

• --tables

  Override --databases. This option was introduced in MySQL 3.23.12.

• --where=where_clause -w where_clause

  Only dump 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.

• --xml, -X

  Generate XML output. This option can be used as of MySQL 3.23.51 (it was introduced in 3.23.48, but output was not well-formed).

Data Format Options for mysqldump

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. This option precludes the use of --fields-optionally-enclosed-by.

• --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. This option precludes the use of --fields-enclosed-by.

• --fields-terminated-by=str

  Specifies the column value separation character or characters to use for data files. By default, values are separated by tab characters.

• --lines-terminated-by=str

  Specifies the character or characters to write at the end of output lines. The default is to write newlines. This option was introduced in MySQL 3.22.4.

Variables for mysqldump

The following mysqldump variables can be set using the instructions in the "Setting Program Variables" section earlier in this appendix.

• 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 can be expanded up to max_allowed_packet bytes long.