different database size after export and import

BoBBers's profile image BoBBers posted 8 years ago in General Permalink

Hello, I hope it does not bother. But I have a question:

I have an export from my database made with creating the database and tables as an option.

How can it be that I have the same .sql file (12.9 MB) in xampp and the test server does not have the same size as on the source server

1 attachment(s):
  • different-database-size
ansgar's profile image ansgar posted 8 years ago Permalink

HeidiSQL calculates the database size by adding the reported Data_length + Index_length from all contained tables. These values are reported by SHOW TABLE STATUS (at least in MySQL). I think especially InnoDB tables report these sizes with a slight variation, or even as an estimation, not sure. I have seen the same effect here often on my InnoDB tables, but when I compared them as SQL dumps using BeyondCompare, they were exactly the same. By the way, I can really recommend BeyondCompare for diffs, uploading things to (S)FTP servers and so on.

kalvaro's profile image kalvaro posted 8 years ago Permalink

Those values mean (reference here):

Data_length

For MyISAM, Data_length is the length of the data file, in bytes.

For InnoDB, Data_length is the approximate amount of memory allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

Index_length

For MyISAM, Index_length is the length of the index file, in bytes.

For InnoDB, Index_length is the approximate amount of memory allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

MySQL will not rearrange a complete data file as soon as you e.g. remove a row, for obvious performance reasons. InnoDB page size is clearly a value that can change between servers. MySQL can implement different internal structures or strategies in new releases. And of course, the sizes of a binary data structure and the plain text source code used to generate it are only loosely related.

BoBBers's profile image BoBBers posted 8 years ago Permalink

but a different filezise over 10 mb ?

the reason is, i use "Server A" on this Server is my Shop installed. Ubuntu 14.04, MariaDB php7 etc.

Now i rent a secound Server "Server B" for testing a other Serverconfiguration. Debian 8, Maria DB php5.6

At the first test the Serverconf. from Server B runs faster and is much stabler as Server A.

So, now i have to backup my SQL from Server A. But, can i trust the Backup? At the testsystem the Backup runs very well but im confused about this big differenz at the filesize so that i think: is that correct? Can this be right?

But if you say, thats okay i look ahead to my Serverbackup.

Today i try the new Configuration. I hope it works. I take full Filebackup (so problem) and i take 4 sql Backups:

  1. phpmyadmin
  2. over ssh
  3. HeidiSQL
  4. a full exported Snapshot from System to reinstall at the worst case 4 days ago

i hope you can understand my english :/

ansgar's profile image ansgar posted 8 years ago Permalink

If you don't trust the size calculated by HeidiSQL, then dump both databases and compare the results if both contain the same rows.

Please login to leave a reply, or register at first.