Mysql database question

[frank]

On making a backup of a forum mysql dbase (data & structure), in the Cpanel the usage is listed as 24 mb. When I select backup it downloads with the size of just over 12 mb, ok I guess that is compressed.
If I go into Manage sql dbase and click to Export with compression set to NONE it downloads a filesize of just over 14 mb.

As the Cpanel is showing 24mb it appears the actual download is 10 mb short of something. Am I having a senior moment? I'm no expert on SQL and I want to be sure I'm getting the right size of DB in case I ever need to restore the clubs forum.

Thanks

 

[unplugged]

If my memory serves the Size on Disk will be the size of the Data files on the disk. This will include the indexes, and other information "generated" but not part of the data and will vary depending on database type and structure itself (Different Character Sets for example may store data differently and affect the size on disk)

When you generate a Database Dump from phpmyadmin (which im assuming is the tool cpanel is using) it just generates an sql file which has all the commands needed to recreate the tables and data in them. The size of this file however is not necessarily a indicator to the size of the data.

A table such as

NAME E-MAIL
ME my@mail.com

will turn into

CREATE TABLE `my_table`...... etc.
INSERT INTO `my_table` (name, email) VALUES ('me', my@email.com)

Which is probably bigger than the data itself due to query overhead. The safest way would be to restore the backup into another database (create new and import) and make sure all the tables and data appear to be their (row counts etc)

 

[unplugged]

I just did a dump of one of my local databases.

A 2MB database turned into a 1.43mb SQL file.
A 45MB database turned into a 34.9mb SQL file.

So the "loss" is not unusual.

 

[frank]

Thanks guy's, makes me a bit more confident now that the backups should be ok.