SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Comparing Databases??

    I just learned - I think - how to use mysqldump to "backup" my database (i.e. "export") and then "restore" it (i.e. "import") on my new MacBook Pro.

    My concern is this...

    How do I *truly* know that my "Backup & Restore" was successful??

    Off Topic:


    When I have been migrating all of my files over from my old MacBook to my new MacBook Pro, there is less concern because 1.) They are whole files, and 2.) I can use something like "DeltaWalker to do a "byte-by-byte" comparison between my hard-drives (in cases where I did a "Copy & Paste" versus a "Cut & Paste").



    Because this is just my "Dev" database, I guess it is less crucial, but I am hoping there is a way to make sure every "1 and 0" is in its proper place, so that when I use mysqldump to backup my "Production" database, I can rest assured that it is successfully backed up!!

    Sincerely,


    Debbie

  2. #2
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    You can do something along these lines ...

    load the backed up data into a new table (in my code its called bup_table)
    `id` is your primary key column

    Code:
                           SELECT count(*)
    			FROM original_table t1
    			LEFT JOIN bup_table t2 ON 
    				`t1`.`id` = `t2`.`id`
    			WHERE 
    				`t2`.`id` IS NULL

  3. #3
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,226
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    I think doing before and after CHECKSUM TABLE queries would be ideal, but
    A live checksum is enabled by specifying the CHECKSUM=1 table option when you create the table; currently, this is supported only for MyISAM tables.
    And CHECK TABLE queries could help determine if tables got corrupted, but might not mean that they're identical in every way.

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    I think doing before and after CHECKSUM TABLE queries would be ideal, but


    And CHECK TABLE queries could help determine if tables got corrupted, but might not mean that they're identical in every way.
    But what I care about is if my two DATABASES (and Contents) match...

    I skimmed CHECKTABLE, and fail to see how that would "equate" two different Databases - or related DB Objects?

    Sincerely,


    Debbie

  5. #5
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,226
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    Depending on how you EXPORT/IMPORT the dump unless you use different charset/collation I don't see how content would differ unless something got corrupted in the process. CHECKTABLE would detect corruption

    That's why I think CHECKSUM TABLE would be what to use if it's critical that they be identical in all aspects.

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Depending on how you EXPORT/IMPORT the dump unless you use different charset/collation I don't see how content would differ unless something got corrupted in the process. CHECKTABLE would detect corruption

    That's why I think CHECKSUM TABLE would be what to use if it's critical that they be identical in all aspects.
    Well, my entire DB uses InnoDB, so that kills using CHECKSUMTABLE.


    Back to my OP...

    How likely would it be for mysqldump to...

    a.) Corrupt a Table Definition (i.e. DDL)??

    b.) Corrupt a given record (i.e. INSERT)??

    c.) Drop records (i.e. INSERTs)??

    d.) Otherwise screw up the SQL that rebuilds a Database from scratch??


    Here is what I ended up doing in my current situation...

    1.) On my old MacBook, ran mysqldump.

    2.) Placed the resulting .sql file onto a thumbdrive.

    3.) Copied the resulting .sql file onto my new MacBook Pro.

    4.) Ran the command to import the .sql file into my shell "doubledee" database.

    5.) On my new MacBook Pro, ran mysqldump on the new database

    6.) Used the app DeltaWalker to do a "File-to-File" (byte-by-byte) comparison of the to .sql files.

    This entailed scrolling through line by line of the two .sql files in side-by-side panes and looking for where differences were highlighted.


    Fortunately, according to DeltaWalker, my two .sql files were identical, except for some minor things like the SQL keyword being "default" in the original database and getting changed to "DEFAULT" in the new database.


    Bottom Line: Using the above approach worked fine for my Dev database, but on a Production database with tens or hundreds of thousands of records, or even millions of records, that approach isn't realistic...


    And since I am a big advocate of Open-Source Software, I'm not overly inclined to go buy software to do what I want...


    I think one of my fundamental questions is...

    How reliable is mysqldump - especially with large databases???


    If mysqldump "just works", then I guess trusting it is enough.

    Then again, I *thought* that I could trust phpMyAdmin after lots of luck with it in the past as a tool to at least manage my database.

    Boy was I wrong about phpMyAdmin being "reliable"!!!

    Sincerely,


    Debbie

  7. #7
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,226
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    Though originally written by an individual, now that they are both from the same place I would think that mysqldump would be at least as reliable as MySQL is.

    The only reason I can think of that might cause problems is if you used the wrong options. And there's a lot of them http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

    I think what most sites that have massive databases do is keep frequent backups just in case.

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,914
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    The only reason I can think of that might cause problems is if you used the wrong options.
    Care to be a little more specific?!


    Quote Originally Posted by Mittineague View Post
    Yep!! mysqldump is a very "robust" function!!!


    Quote Originally Posted by Mittineague View Post
    I think what most sites that have massive databases do is keep frequent backups just in case.
    From what I read a few weeks ago when I was on my old MacBook, doing Restores and doing Backup/Restores on Production Databases is MUCH more involved.

    What I just did yesterday was easy, because it was a small database, and one where no one was in it when I ran mysqldump.

    But for my Production environment, it will be much trickier to do Backups when people are using it it, and then to successfully do a Restore when people are using it.


    I have read a little bit that seems to imply that maybe MySQL WorkBench offers some capabilities on these topics, but from the little I've read, it seems like this might be a time I have to break down and *buy* something...

    (If you are willing to pay $5,000 per year per license, I think MySQLEnterprise Edition offers a very robust Enterprise Backup/Recovery/Change Mgt Tool...)

    Sincerely,


    Debbie


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
  •