MySQL Forums
Forum List  »  InnoDB

Re: Unable to dump data from DB of 41GB - trievd several ways
Posted by: Rick James
Date: April 30, 2014 10:52AM

First, how big is the USB stick?

Since mysqldump writes out INSERT statements, the output file could be larger _or_ smaller than the storage on disk. It does not write out indexes (but does write instructions for recreating them); it does not write out padding, overhead or free space. That is, I could imagine 41GB becoming only 7.8GB.

Similarly, 7.8GB could become only 3GB after loading. This would probably imply a _lot_ of wasted space in the original ibdata1.

> but number of records were not matched before and after.

How are you counting the records?
SELECT COUNT(*) is exact.
SHOW TABLE STATUS is rarely exact, sometimes off by a factor of two.

Do the following:
Before the dump: SHOW TABLE STATUS for each database, then add up the Data_length values for all InnoDB tables.
After the reload: Do the same.
I would expect the 'after' total to be less than 'before', purely because of defragmentation. However, I would not expect more than 2x shrinkage.
Similarly the sum of Index_length is likely to go down, possibly by more than 2x. (Or it could increase.)
The "Rows" is, as I said above, likely to change. Even when you run the SHOW again, "Rows" is likely to change.

For verification, look into pt_table_cksum. It is aimed at validating Master-Slave setups, but may have a way of exposing the checksums. The checksums should match, even if disk footprint, Rows, Data_length, etc, don't match, since it is really checking the rows while ignoring how the rows are laid out on disk.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Unable to dump data from DB of 41GB - trievd several ways
884
April 30, 2014 10:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.