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, the table contents are written to a data file as tab-separated values, one line per row, 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, mysqldump dumps the named tables in the given database. 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. If --databases or --all-databases is used, the output contains CREATE DATABASE IF EXISTS and USE statements preceding the statements for the tables in each database.

The most common way to use mysqldump is as follows:

% mysqldump --opt db_name > backup_file

As of MySQL 4.1, --opt is enabled by default and can be omitted. To obtain pre-4.1 output instead, use --skip-opt.

The 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      --password                --socket
--compress                --pipe                    --user
--debug                   --port                    --verbose
--default-character-set   --protocol                --version
--help                    --set-variable
--host                    --shared-memory-base-name

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 may 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.

• --add-locks (boolean)

  Add LOCK TABLE and UNLOCK TABLE statements around the set of INSERT statements for each table.

• --all, -a (boolean)

  See the description for --create-options. The --all option is deprecated in favor of --create-options as of MySQL 4.1.2.

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

  Dump all tables in all databases. This option also causes the dump output to include CREATE DATABASE IF NOT EXISTS and USE statements for each database.

• --allow-keywords (boolean)

  Allow for the creation of column names that are keywords.

• --comments, -i (boolean)

  Include additional informational comments in the output, such as the mysqldump version, which tables each set of INSERT statements applies to, and so forth. This open is enabled by default; disable it with --skip-comments.

• --compact (boolean)

  Generate more concise output that does not include comments, including version-specific comments that set system variables. This option also enables the --skip-add-drop-table, --skip-set-charset, --skip-disable-keys, and --skip-add-locks options. The --compact option was introduced in MySQL 4.1.2.

• --compatible=mode

  This option causes mysqldump to modify its output to be compatible with standard SQL, other database servers, or older versions of MySQL server. The mode value specifies a compatibility mode. It can be given using one or more of the following values as a comma-separated list:

  Option	Compatibility Meaning
  ANSI	ANSI-compatible
  DB2	Compatible with DB2
  MAXDB	Compatible with MaxDB
  MSSQL	Compatible with MS SQL Server
  MYSQL323	Compatible with MySQL 3.23
  MYSQL40	Compatible with MySQL 4.0
  ORACLE	Compatible with Oracle
  POSTRESQL	Compatible with PostgreSQL
  NO_FIELD_OPTIONS	Suppress MySQL-specific column-related options
  NO_KEY_OPTIONS	Suppress MySQL-specific index-related options
  NO_TABLE_OPTIONS	Suppress MySQL-specific table-related options

  
  

  This option was introduced in MySQL 4.1.0 and has no effect if you connect with mysqldump to a server that is older than 4.1.0.

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

  Use INSERT statements that name each column to be inserted.

• --create-options (boolean)

  Add additional information to the CREATE TABLE statements that mysqldump generates, such as the storage engine, 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 E.)

  This option was introduced in MySQL 4.1.2. It is enabled by default; use --skip-create-options to disable it.

• --databases, -B (boolean)

  Interpret all arguments as database names and dump all tables in each database. This option also causes the dump output to include CREATE DATABASE IF NOT EXISTS and USE statements for each database.

• --delayed-insert (boolean)

  This option causes mysqldump to write INSERT DELAYED statements rather than INSERT statements. If you are loading a dump file for MyISAM tables into another database and you want to minimize the impact of the operation on other statements that may be taking place in that database, --delayed-insert is helpful for achieving that end.

• --delete-master-logs

  Delete the binary log files on the server and begin a new one by issuing a FLUSH MASTER statement after generating the dump output. Don't use this option unless you're sure you want the existing binary logs to be wiped out. This option enables --first-slave before MySQL 4.1.8 and --master-data from 4.1.8 on.

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

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

  Write multiple-row INSERT statements. These can be loaded more efficiently than single-row statements.

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

  This option is the old form of the --lock-all-tables option before MySQL 4.1.8.

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

  Flush the server log files before dumping tables. By default, the logs are flushed for each database to create a checkpoint. This makes it easier to perform restore operations because you know that binary logs after the checkpoint time were made after the backup for a given database. In conjunction with --lock-all-tables or --master-data, the logs are flushed only after all tables have been locked. This option requires the RELOAD privilege.

• --force, -f (boolean)

  Continue execution even if errors occur.

• --hex-blob (boolean)

  Dump BINARY, VARBINARY, and BLOB columns as hexadecimal constants. For example, with this option, mysqldump writes "MySQL" as 0x4D7953514C. This option was introduced in MySQL 4.1.7.

• --lock-all-tables, -x (boolean)

  Use FLUSH TABLES WITH READ LOCK to lock all tables across all databases. This option disables --single-transaction and --lock-tables. Before MySQL 4.1.8, --lock-all-tables was called --first-slave.

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

  Use LOCK TABLES … READ LOCAL obtain locks for all tables being dumped before dumping them. This option is good for MyISAM tables because a READ LOCAL lock allows concurrent inserts to proceed while the dump is in progress. For InnoDB tables, --single-transaction is preferable.

• --master-data= value

  This option helps make a backup that can be used with a slave server. With this option, mysqldump sends a SHOW MASTER STATUS statement to the server to get its current binary log filename and position, and uses the results to write a CHANGE MASTER statement to the output that contains the same filename and position. The effect is that when you load the dump file into a slave server, it synchronizes the slave to the proper replication coordinates to begin replicating at the point when the dump was made. Note: This option has no effect unless the server has binary logging enabled.

  By default, the CHANGE MASTER statement is written in non-commented form. As of MySQL 4.1.8, --master-data takes an optional value to explicitly control commenting of the statement. A value of 1 produces a non-commented statement, and a value of 2 produces a commented statement.

  --master-data requires the RELOAD privilege. This option automatically enables --lock-all-tables if --single-transaction is not given.

• --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 autocommit mode.

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

• --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, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. This option was disabled by default before MySQL 4.1. It is now enabled by default; use --skip-opt to disable it.

• --order-by-primary (boolean)

  Dump table rows in order of the primary key or the first unique index if there is one. This produces sorted dump output for each table at a cost in performance. This option was introduced in MySQL 4.1.8.

• --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. --quote-names is enabled by default as of MySQL 4.1.2; use --skip-quote-names to disable it.

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

• --set-charset (boolean)

  Write a SET NAMES charset statement to the output, where charset is utf8 by default. The character set can be changed using the --default-character-set option. The --set-charset option is enabled by default; use --skip-set-charset to disable it. This option was introduced in MySQL 4.1.2.

• --single-transaction (boolean)

  This option allows consistent dumps of InnoDB tables. The idea is that all the tables are dumped within a single transaction. For InnoDB, mysqldump uses the REPEATABLE READ transaction isolation level to produce a consistent dump without causing other clients to block. (For non-transactional tables, changes might still occur during the dump operation.) This option disables --lock-tables.

• --skip-opt

  This option has the opposite effect of --opt, which is enabled by default as of MySQL 4.1. --skip-opt can be used to obtain the pre-4.1 behavior of mysqldump.

• --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 may be changed using the options described under "Data Format Options for mysqldump."

  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 host and some are written on the client host. 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 so that all files are created on the same machine.

  • 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 to cause any following arguments to be interpreted as table names.

• --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 your command interpreter from treating it as multiple command-line arguments.

• --xml, -X

  Generate output in XML format rather than as a set of SQL statements.

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 might 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 E.

• --fields-enclosed-by=char

  Specifies that column values should be enclosed within the given character, usually a quote character. The default is to not 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 to not 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.

Variables for mysqldump

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

• max_allowed_packet

  The maximum size of the buffer used for communication between the server and the client. The default value is 24MB and the maximum is 1GB.

• 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. The default value is slightly less than 1MB.