Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a 2GB MySQL DB. We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here?

Questions

  • Why is this file so large?
  • What is in this file that causes it to be so much larger than the in memory footprint?
share|improve this question
1  
+1 for this question because many have encountered this not knowing of mysqldump options. This question should serve as a gentle reminder to get to know mysql client tools. –  RolandoMySQLDBA Aug 12 '11 at 20:26

2 Answers 2

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.

share|improve this answer

The dumpfile doesn't just contain the structures and data it contains the actual sql to recreate the structures and to insert the data. That is what makes your file big. There are ways to make the dump file compressed on the fly when dumping if the size is an issue for you.

For more info read here: http://www.webcheatsheet.com/SQL/mysql_backup_restore.php#compress

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.