Cleaning a Production Database for use in Testing

Question

I'm building a local vm for doing web dev rather than using our on site development. I need a database locally, but I don't want to just pull down a production db and use that as it has information that, while not protected by HIPAA or anything, should not be available in the case of laptop theft. Are there any apps or recommended practices to sanitize this data so that I am able to pull down a db, clean it, and install it in my vm?

Clarification: What I'm really looking for is an app that would allow me to mark the specific columns as sensitive and whack those ones whenever I imported a new copy of the DB.

Answer 1

Sounds like what you need is a data generator, one that will populate your database with bogus data. Redgate has a good one, but I don't know if it will work with mysql. Maybe this will help you out?

Answer 2

TRUNCATE table;

or

DELETE FROM table WHERE true;

on any table that you don't want to keep the data of, and then either set dummy values for any sensitive user data, or delete all user data and just turn a few accounts into local testing accounts (user 'testadmin', password 'password', etc).

The more interesting question that you should be asking yourself is: Why does my database not already have skeleton sql migrations that I can run to create a clean database? What happens when you need to create a separate production instance on another server?