Difference in mysql dump file size and database size

Question

Hi folks I've a doubt in mysql file dump size and db size varies.

My DB size is 238MB and same DB's dump size is 297MB. Why this large variations?. Can any one explain this?.

Answer 1

The dump file is a text file. When you turn the internal data of a DB into text, there are many expansions:

• Numbers that might be 4 bytes internally are written out as digits like 123456
• Strings have quotes around them, and special characters in the strings may be escaped
• There are commas between each column, and parentheses around the rows

Answer 2

When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Answer 3

For a good normalized db, db size is significantly smaller than dump file.

Because the db uses a lot of numbers, I have a db with 200 mb dump, but only 80 kb in db.

As said by Barmar, during dump, numbers are converted into characters, 1234567891 uses 1 integer for storage, or 3 bytes, but 10 bytes for the characters.