CODEWITHSUNDEEP
mysqlsql
Rocstar
Rocstar

Reputation: 1477

Import/export manually or Dump Database

I have a rather heavy database I have to change server, so I export the database with a dump, then I retrieved it on my new server

But after importing it, the size of the database is different :

The difference is enormous, I have to worry? (Do you think there are missing things?) Is not it better to do an export then an import manually?

Upvotes: 0

Views: 65

Answers (1)

Kevin Bott
Kevin Bott

Reputation: 733

You didn't mention if these size numbers from from the filesystem (du -ch, etc), or from a query. I'm guessing they are from the filesystem. As long as you didn't get import errors, your data is probably OK.

Check for FRAGMENTATION in your SOURCE tables. That is likely why your source is larger than your target. Basically, when rows are updated, it may no longer fit in the same data block, and that data block is split into two, leaving some free space in two blocks (16KB used on disk, but 9KB used in the data file).

Check for fragmented tables: select ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables where DATA_FREE > 0;

Check for total disk used and total free space: select sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024) as TTL_MB_USED , sum(DATA_FREE)/1024/1024 as TTL_MB_FREE from information_schema.tables where table_schema='<your schema>'; That may help account for the difference in size between source and target.

I found this answer to be excellent to describe Fragmentation: https://serverfault.com/a/265885

first at all you must understand that Mysql tables get fragmented when a row is updated, so it's a normal situation. When a table is created, lets say imported using a dump with data, all rows are stored with no fragmentation in many fixed size pages. When you update a variable length row, the page containing this row is divided in two or more pages to store the changes, and these new two (or more) pages contains blank spaces filling the unused space.

Upvotes: 3

Related Questions