Something tells me you are either using a version of MySQL prior to 5.0, you called an older version of mysqldump, or you messed with the settings of the dump.
What usually blows up a mysqldump past the size of its dataset is the option --skip-extended-insert.
In older versions of MySQL, there was no extended insert. That means each and every row in a table had an INSERT command to itself. If a table had 2,000 rows, the mysqldump output will have 2,000 INSERT commands. That's a whole lot of commas, parentheses, single quotes, and "INSERT INTO" tags to place in a dump file.
In newer versions of MySQL, --extended-insert was added to group together dozens (or even hundreds) of rows in a single INSERT. SO, instead of...
INSERT INTO tbname VALUES (1);
INSERT INTO tbname VALUES (2);
INSERT INTO tbname VALUES (3);
INSERT INTO tbname VALUES (4);
INSERT INTO tbname VALUES (5);
You could have this:
INSERT INTO tbname VALUES (1),(2),(3),(4),(5);
For mysqldump, --opt includes --extended-insert. If you use --skip-opt when doing mysqldump, it disables --skip-extended-insert. Here are the options of mysqldump that affect extended insert:
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
CAVEAT
Run these at the Linux command line
mysql --version
mysqldump --version
which mysqldump
If these do not match the version of mysql you are running on the server, or multiple versions of MySQL exist on the same machine, get that straightened out. Until then, make sure you call the correct version of mysqldump and don't use --skip-opt.
Best Answer
Based on the bounty message
Looking for an answer drawing from credible and/or official sources.
, the only thing I could do is just mention that mysqldump is already buffering as little as possible. Why can I say that ???By default, the option --opt is enabled. The MySQL Documentation says this about --opt:
Please note that --quick is already enabled because --opt is enabled.
What does --quick do ???
Since --quick is already enabled, each row is being dumped directly to stdout without buffering. You may have to just bite the bullet and disable --quick by using
--skip-quick
. What that will do is buffer a whole table into RAM (which you didn't want to do in the first place) and then spill it out to stdout.In your question, you said something interesting
Since you said that, you have reached a critical moment
I can say that mysqldump is not really the problem. The problem is the OS and/or hardware.
Evidently, the computer you are using for Windows is not performant for screen I/O.
Since MacOS runs like Linux, I would tend to blame Windows OS. My guess is that even with a far better computer (faster CPU, more RAM), mysqldump may still not perform better if Windows cannot buffer screen I/O effectively.
Your fix for this would be to tune Windows. Godspeed, Spiderman !!!