Exported databases have different sizes

If I export a database with phpmyadmin his size is 18MB

If I expoert it from terminal using this command is size is only 11MB.

/usr/bin/mysqldump --opt -u root -ppassword ${DB} | gzip > ${DB}.sql.gz

Could you explain me why ? Is because of --otp parameter ?

How can I be sure the database has been succesfully exported ? Should I inspect it.. still it is not a reliable evaluation. thanks

Answers


With the details you've given, there are a number of possibilties as to why the sizes may differ. Assuming the output from phpMyAdmin is also gzipped (otherwise the obvious reason for the difference would be that one is compressed, the other isn't), the following could affect size to some degree:

  • Different ordering of INSERT statements causing differences in the compressibility of the data
  • One using extended inserts, the other using only standard inserts (this seems most likely given the difference in sizes).
  • More comments added by the phpMyAdmin export tool
  • etc...

I'd suggest looking at the export to determine completeness (perhaps restore it to a test database and verifying that the row-counts on all tables are the


I don't have enough points to comment so I'm adding my comments in this answer...

If you look at the uncompressed contents of the export files from a phpmyadmin export and a mysqldump they will be quite different.

You could use diff to compare the two sql files:

diff file1.sql file2.sql

However, in my experience that will NOT be helpful in this case.

You can simply open the files in your favorite editor and compare them to see for yourself.

As mentioned by Iridium in the previous answer, the use of inserts can be different. I created two new empty databases and imported into each (via phpmyadmin) - one of the two exports mentioned above (one from phpmyadmin and the other via mysqldump).

The import using the mysqldump export file recreated the database containing 151 tables with 1484 queries.

The import using the phpmyadmin export file recreated the database containing 151 tables with 329 queries.

Of course these numbers apply only to my example, but it seems to be in line what Iridium was talking about earlier.


Need Your Help

ACE Connection String From Excel 2010 to Itself

sql excel-vba excel-2010 vba excel

I am trying to use SQL in VBA to update data in an existing table in Excel (same file as vba code). This is being written with SQL queries and updating with the intent of moving the source data to...

Make instance of Singleton Class object eligible for GC

java enums jaxb garbage-collection singleton

I have a class JAXBReader which hold unmarshalled xml file using the jaxb generated classes. I have used the singleton design so that I need not unmarshall the file again and again. The object of t...