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?? :-/
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!!
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”!!!
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…)