Add a DROP TABLE IF EXISTS statement before each CREATE TABLE statement.
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 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.
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.
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:
Compatible with DB2
Compatible with MaxDB
Compatible with MS SQL Server
Compatible with MySQL 3.23
Compatible with MySQL 4.0
Compatible with Oracle
Compatible with PostgreSQL
Suppress MySQL-specific column-related options
Suppress MySQL-specific index-related 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.
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.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Generate output in XML format rather than as a set of SQL statements.