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.