Comparing Databases?

Databases
1

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

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


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

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

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

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

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”!!! :eek:

Sincerely,

Debbie

7

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

Care to be a little more specific?!

Yep!! mysqldump is a very “robust” function!!!

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… :frowning:

(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