Move tables to different host and they are different sizes?

[freelancerx]

Okay, I am moving a lot of tables from one host to another. (Using phpMyAdmin and the export feature... export to .gz, then import new server... standard settings.)

I noticed an odd issue that is confusing me. The data all looks fine, but this makes me wonder...

Some tables have come through fine with matching size (data, index, row). But some differ in size... as far as I call tell row size and index are where the mismatch is occurring.

The weird thing is... sometimes the table is bigger on the new server and sometimes bigger on the old one. O_o

I realize that small differences like these are a poor way to judge if the data came through correctly, but it does have me wondering what is causing the issue. I have verified and everything else looks the same... number of rows, column types/settings, collation, index types.

Could this simply be a mis-match in MySQL versions or something else. The kicker is... some tables match exactly.

Thoughts?

Thanks,
Michael

[Dan Grossman]

Run OPTIMIZE TABLE [name] or REPAIR TABLE [name] on one of the tables with different sizes, and see if they match up afterwards. Deletes, updates and other changes to the table leave holes in the file on disk. Those holes won't appear in a newly created file that's never been deleted from even though it contains the same rows.

[freelancerx]

Well, you got it. I am pretty sure I had run Optimize, but the Repair on both got them matching. (I guess I don't understand the difference that clearly in this case.) Anyway, thanks for that!

Now all I have to do is figure out how to move my 4.4 GB table all while trying to fit it in between 2 hourly crawls and not lose any data. lol

Thanks!

[Dan Grossman]

Well, you got it. I am pretty sure I had run Optimize, but the Repair on both got them matching. (I guess I don't understand the difference that clearly in this case.) Anyway, thanks for that!

Now all I have to do is figure out how to move my 4.4 GB table all while trying to fit it in between 2 hourly crawls and not lose any data. lol

Thanks!

If your crawl only generates INSERTs and not UPDATEs, change your code to temporarily insert into both databases at the same time. Then dump and import the rows from the old to the new, and the new DB will be exactly as up-to-date as the old one. Your primary key should prevent any overlaps by throwing out duplicate INSERTs from between the time you start inserting into both DBs and when you create the dump.

4.4GB isn't that bad... I just spent 14 hours doing a 100GB+ migration without downtime or data loss

[freelancerx]

Yeah, I saw someone referring to 40MB as big... pshh. lol

This is kind of a different thread. Maybe I will start one. Let me ask you though... are you available on short notice to do some consulting? If so, PM me.

My sites has been down for 2 days. (crawl script still running on old host until I can get data moved to new host) I like your idea. PM me if you have time and I will let you know my situation. Obviously, data integrity and privacy are important. (Proprietary schtuffs!)

Thanks,
Michael