Database size different after export/import

Question

I had to export mysql database using mysqldump for backup reasons.

To compare the size given by phpmyadmin, I downloaded the dump on my local machine then import the dump using SQLYog into a local database.

Now, when I compare the size given by phpmyadmin on my machine and the remote machine, I end up with the imported database on my local machine to be smaller than the one on the remote machine :

remote machine database size : 112,3 Mib
local machine database size : 95,7 Mib

I would like to know what could be the reasons for such a difference ?

Cheers

AdrianBR · Accepted Answer · 2014-01-13 11:24:08Z

5

112,3 Mio what? Rows? b?

If it's storage size, there's not much to worry about, it's about how disk space usage is optimized (vacuum/optimize table). http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

On the other hand if it'S rows, you'll have to track down the tables that differ and figure out why.

use(replace schema name)

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "test"   
union all
SELECT 'total', sum(table_rows), sum(data_length), sum(index_length), 
sum(round(((data_length + index_length) / 1024 / 1024),2)) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "test"   group by 1

edited Jan 13, 2014 at 11:24

answered Jan 13, 2014 at 9:59

AdrianBR

2,5781 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

renard Over a year ago

It is the total table size, so I guess it is storage size is it ?

AdrianBR Over a year ago

yep, check the link above, storage size may vary for the same data depending on how optimized it is. Use my query to find if there are any missing rows

AdrianBR Over a year ago

No probs. Common 'scare'. Easiest way to demonstrate these inconsistencies is to just copy a database within the same server, and you will immediately see differences, even though they are identical (for example copy prod to a new one called Playground - i do that for some tests)

Cosmin · Accepted Answer · 2014-01-13 10:03:54Z

1

EDIT: Sorry about the initial answer.

Did the DB have triggers/procedures ? If you did not export the file via a user with trigger/procedures privileges they will not be included in the export.

And also by default routines are skipped and the triggers are usually exported only by the root user.

edited Jan 13, 2014 at 10:03

answered Jan 13, 2014 at 9:53

Cosmin

1,49012 silver badges26 bronze badges

2 Comments

AdrianBR Over a year ago

Database size comparison, not dump size. Dump size will often be smaller because of indices

renard Over a year ago

there's no triggers / procedures / routines on the db

Collectives™ on Stack Overflow

Database size different after export/import

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related