mysqldump has a "quick" option. Why isn't this enabled by default?
--quick
is on by default, as it is one of the options included in --opt
, which is itself on by default.
From the man page:
- Use of
--opt
is the same as specifying--add-drop-table
,--add-locks
,--create-options
,--disable-keys
,--extended-insert
,--lock-tables
,--quick
, and--set-charset
. All of the options that--opt
stands for also are on by default because--opt
is on by default.
The page you linked to contains similar information.
--opt
This option, enabled by default, is shorthand for the combination of
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset
. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.
While --quick
is on by default, I can only think of one specific time where I had to disable a part of the --quick
options.
A client of mine had a database that was to be installed on a smaller Linux server with almost no memory (I think it was 2GB). A mysqldump made with --quick
had extended INSERTs. That mysqldump could not be loaded into this 2GB Linux because a bulk insert buffer could not be allocated large enough to accommodate a single extended INSERT. It was driving me nuts. That is, until I decided to add --skip-extended-insert
.
What this did what make each INSERT command insert one row. While this blew up the size of the mysqldump, and made the load of the mysqldump take hours, that did the trick. The entire dataset was loaded into a rinky-dink Linux server.
CAVEAT : Please don't ask me why a client would want MySQL running on a 2GB Linux server. This was over 5 years ago, and I still scratch my head when I think about.
As for --disable-keys
, you could use --skip-disable-keys
to let a MyISAM table get reloaded and let the INSERTs fill the all non-unique indexes on-the-fly. This would produce somewhat lopsided indexes if the data were loaded already sorted. Nevertheless, this would be necessary when reloading a mysqldump in a low-memory environment (as I already sadly reminisced).