Yep. For x.com I wrote a simple cron job that sterilizes the automated database dump and sends it to the dev server. Roughly, it's like this:
-cp the dump to a new working copy
-sed out cache and tmp tables
-Replace all personal user data with placeholders. This part can be tricky, because you have to find everywhere this lives (are form submissions stored and do they have PII?)
-Some more sed to deal with actions/triggers that are linked to production's db user specifically.
-Finally, scp the sanitized dump to the dev server, where it awaits a Jenkins job to import the new dump.
The cron job happens on the production DB server itself overnight (keeping the PII exposure at the same level it is already), so we don't even have to think about it. We've got a working, sanitized database dump ready and waiting every morning, and a fresh prod-like environment built for us when we log on. It's a beautiful thing.
-cp the dump to a new working copy
-sed out cache and tmp tables
-Replace all personal user data with placeholders. This part can be tricky, because you have to find everywhere this lives (are form submissions stored and do they have PII?)
-Some more sed to deal with actions/triggers that are linked to production's db user specifically.
-Finally, scp the sanitized dump to the dev server, where it awaits a Jenkins job to import the new dump.
The cron job happens on the production DB server itself overnight (keeping the PII exposure at the same level it is already), so we don't even have to think about it. We've got a working, sanitized database dump ready and waiting every morning, and a fresh prod-like environment built for us when we log on. It's a beautiful thing.