MySQL database sync for workstation developement/testing

Question

I need a local copy of our production database, and I need to refresh it every few days so testing and development is not working with terribly stale data. A few days old is just fine. Here is the pseudo plan:

1. Write a script on the Production server that mysqldump's + gzip the database.
2. Add a cron process to run the script every other day during non-peak hours.
3. Write a script on the workstation that rsync's that gzipped dump and loads it up.

Is there any better, cleaner, or safer way of doing this?

EDIT: Just to add clarity. We still have in place Test Data that is known, along with our test library (test driven development). Once THOSE tests pass, its on to the (more) real stuff.

Answer 1

You may wish to consider MySQL replication. It isn't a thing to be trifled with but may be what you are looking for. More information here... http://dev.mysql.com/doc/refman/5.0/en/replication-features.html (I don't personally know anything about it other than that it can be done).

Answer 2

Testing should be working with "known" data; not production data. You should have scripts to load "Test" data into the system to achieve this. Test/Dev shouldn't have to deal with a moving target of data. Besides, if you have any sensitive data in production (doesn't everyone"); your dev/test teams shouldn't have access to it.

Some suggestions for creating test data: 1) Excel spreadsheets with VBA behind them to create sql to run against the DB 2) Raw sql scripts 3) Data creation programs that generate data in a known pattern.