SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Move tables to different host and they are different sizes?

    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

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by freelancerx View Post
    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

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •