mysql dump reliable?

sneakyimp

i just made a backup of my production database using mysqldump:

mysqldump dbase_live > live_dbase_backup_2006_03_02.sql

i then restored the resulting SQL to an empty database to make sure it would work:

mysql restore_test < live_dbase_backup_2006_03_02.sql

both commands took some time but ran to completion without errors. HOWEVER, phpMyAdmin reports different database sizes for various database tables between the original databse 'live_dbase' and the restored one, 'restore_test'.

Is this ok? Or might my data in the restoration test database be corrupted? What causes these differences? Where do these size numbers come from? In one case, a table contains only 142 records with 10 fields and the database sizes are 17.1K in the original database and 13.3 K in the restored one! Furthermore, one of the indexes in that table has a cardinality of 10 in the old Dbase and 0 in the new one!

I'm pretty concerned here. I was under the impression that mysqldumps were reliable. Is this not the case?

Drakla

Have you done deletes on the original database? Have you optimised that database, defragmenting it? When you delete records basically you add a 'hole' to the data that'll get filled in when another record comes along. So optimise it and then see if the size changes.

e.g.

SHOW TABLE STATUS LIKE 'foo';
OPTIMIZE TABLE foo;
SHOW TABLE STATUS LIKE 'foo';

And see if the data_length changes before and after

sneakyimp

thanks drakla.

I optimized the old table and now the sizes are the same, but the index size still differs...cardinality 7 in the old DB and zero in the new one.

when i optimize the new table, it updated the index cardinality to 7 though. does this mean i need to optimize all tables??

Drakla

You should only worry about optimising tables often if they're really big and have loads of insert / delete jiggery pokery going on, then do it on a cron or something every week.

If you look at the manual about cardinality it says it's all guesswork and can change all the time with OPTIMIZE so don't worry about it.

"Note that as the Cardinality is counted based on statistics stored as integers, it's not necessarily accurate for small tables."