asked on How do I create a FAST copy of my local mysqlDB whilst I do some destructive testing?
Hi.
I'm on Windows and I'm developing using a mySQL DB. I need to do a whole load of destructive tests - by that, I am going to be deleting/summarising data, and I want to be able to get back to my start point QUICKLY.
mysqldump takes nearly 20 minutes to do a restore.
In my head, all I want to do is shutdown mysql, physically copy the data files to a safe location, restart mysql, do my tests, do the shutdown, copy back, restart, test loop a couple of times.
I just don't want to be waiting 20 minutes between each test run.
The test also include schema changes, so I'm not looking just at the data.
The whole data directory is just under 3.5GB. I can do a physical backup of this in about a minute. A much more reasonable amount of time to take.
The server is local. No other users, etc.
Also it wasn't my setup (it's part of a pre-installed WAMP setup, so it's anyone's guess as to where things are really setup!).
The problem is if I break the restore process, I don't know how to rebuild things. So actual steps that are known to work, rather than "Here is a page that more or less covers something related to some aspect of something you might have been thinking about when you were writing this question" please.
Regards,
Richard.
I'm on Windows and I'm developing using a mySQL DB. I need to do a whole load of destructive tests - by that, I am going to be deleting/summarising data, and I want to be able to get back to my start point QUICKLY.
mysqldump takes nearly 20 minutes to do a restore.
In my head, all I want to do is shutdown mysql, physically copy the data files to a safe location, restart mysql, do my tests, do the shutdown, copy back, restart, test loop a couple of times.
I just don't want to be waiting 20 minutes between each test run.
The test also include schema changes, so I'm not looking just at the data.
The whole data directory is just under 3.5GB. I can do a physical backup of this in about a minute. A much more reasonable amount of time to take.
The server is local. No other users, etc.
Also it wasn't my setup (it's part of a pre-installed WAMP setup, so it's anyone's guess as to where things are really setup!).
The problem is if I break the restore process, I don't know how to rebuild things. So actual steps that are known to work, rather than "Here is a page that more or less covers something related to some aspect of something you might have been thinking about when you were writing this question" please.
Regards,
Richard.
MySQL ServerStorage SoftwareWindows 7
Last Comment
Richard Quadling
What are you using to manage mysql? If you're using phpmyadmin, you can copy the database and test on the copy. I don't know how long it would take to copy a database of that size, though. Are you working on all tables or just one or two. You could also copy just the table(s) you're testing on. And finally, can you do your testing with temporary tables? If so, you could build into your testing routine to create a temporary copy of the table which will be destroyed automatically after testing is complete.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your feedback.
I'm not using phpMyAdmin - mysqlWorkbench and looking at the new IntelliJ's 0xDBE (finally a multi-platform DB IDE that actually seems to do what it is supposed to!!!!)
I'm not using phpMyAdmin - mysqlWorkbench and looking at the new IntelliJ's 0xDBE (finally a multi-platform DB IDE that actually seems to do what it is supposed to!!!!)