MySQL backup databases: Vastly different sizes based on method used

[GWestphal]

So I'm backing up about 20 smallish databases and I'm noticing something funny. Based on how I back it up, it is vastly different in size.

MySQL Administrator - 40 MB
myPHPAdmin - default - 83 MB
myPHPAdmin -bzipped - 8 MB
MySQL Workbench - sql - 280 KB
MySQL Workbench - dump folder - 42 MB

Why so much variance? Could there be a bug in Workbench for the 280 KB one? Because that seems wicked small. Which one do I trust?

 

[Cerb]

Check them out and see what's inside. There could be unneeded duplication going on, with the larger ones, FI, or individual inserts. I definitely wouldn't trust the 280KB one. I suspect the code style and formatting options are what's different between the others.

Your bz2 backup should be identical to the non-bz2 one. It's compressed, so the apparent size means nothing.

Compare:

INSERT INTO yertable (uid, uname), VALUES(123, 'Hannibal Lector');
INSERT INTO yertable (uid, uname), VALUES(246, 'Mr. Potatohead');
INSERT INTO yertable (uid, uname), VALUES(999, 'Pat');

to

INSERT INTO yertable (uid, uname), VALUES
(123, 'Hannibal Lector'),
(246, 'Mr. Potatohead'),
(999, 'Pat');

Both will do the same thing, and both will require any sex field to have at least four values 🙂. The latter will typically be faster, though, in addition to being shorter.