Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process is taking longer, there becomes a risk where as the database is still being scrubbed, developers can connect to the test database still and look at sensitive data. Is there a way to scrub and then load without adding too many steps? It already takes about half hour to reload the test database. The objective here is the make sure the sensitive data is safe at all times and somehow increase the speed of reloading the test environment's database.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
As a specific answer to "speed of reloading the test environment's database": you probably do not need to rebuild the test database (copy producion then randomise) every time it needs to be reset. Instead at key times take a copy, randomise it, and store that copy - then each time you reset the test environment all you have to do is restore a new copy of that database. You could speed things up further by having more than one restored at any given time: to reset drop the current DB, rename one of the other copies to take its place (testing work can now start immediately), restore another copy to replace the one you are now actively using ready for the next reset.
Wanting to use production data as it is a good representation of the real world but not wanting to give people access to real data are, if the data is truely sensitive, completeley mutually exclusive. If you randomise the data enough that there is absolutely no way a sufficiently determined data miner couldn't infer useful real information from it then you've randomised it to the point that it no longer represents reality any more than made up test data would anyway.
An alternative is to create your own test data in a scripted manner which has a number of beneifts:
For "people" in the above substitude what-ever units your system's size is dependent on. There may be more than one key unit of course: for a training and competence tracking system with its own test managment and training material the key size determinants are the size of the question bank and the number of people, so the request to the sample data generating scripts would be something like:
Of course there is work involved here, which is why the "take real data and try to disguise it" approach is a common short-cut (I do it myself often enough!):
There are products out there that are designed to help create and maintain this sort of test data, though for small systems or particularly complex ones hand-rolling by a good local dev may produce better results cheaper.