Default flags of mysqldump

By default, mysqldump uses the --opt flag which is a combination of multiple flags. If you don't want to use it, you can add --skip-opt.

Let's see which flags are included with the default --opt:

--add-drop-table

Adds a DROP TABLE statement before each CREATE TABLE statement. This means that before restoring a dump, all tables will be deleted and created again from the dump.

--disable-keys

In order to import a dump faster, indexes are created after all rows are inserted. This flag is effective only for MyISAM tables.

--extended-insert

Write INSERT statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the dump is imported. This option should not be used for tables with large columns (for example blobs) because can cause queries going over the configured max_allowed_packet. However, this is not a common case and we suggest to keep this flag because single-query per insert slows down the imports considerably.

--quick

mysqldump is forced to retrieve a row at a time rather than retrieving the entire row set. This is required when the MySQL server does not have enough RAM memory to fit an entire table.

--add-locks

It locks the tables when importing a dump in order to have faster inserts. It only applies when importing a dump, not when exporting one.

--create-options

Include all MySQL-specific table options in the CREATE TABLE statements

--lock-table

It locks all the tables when creating a dump because MyISAM does not have support for transactions. Since InnoDB is the default MySQL engine and has support for transactions, using --single-transaction is a much better solution. We recommend to disable this option. We have a section dedicated to this aspect which can be found here.

--set-charset

Write SET NAMES default_character_set to the output. No character set conversion is performed, it's just saying that you want the character set info added in so it will be set when re-importing the dump file.